How can add custom text to a cell based on a specific entry in a different cell?

ChemistV700

New Member
Joined
Dec 20, 2020
Messages
3
Platform
  1. Windows
My laboratory is capable of running 20+ different analyses, and we get contracts from about the same 15 companies to do a combination of these analyses. I created an Excel spread sheet to keep track of the work as it comes in, where columns are the 20 different analyses we can run, and rows are the companies. I type in either a checkmark or "NA", depending on whether that company requests that specific analysis. (Each company requests its own combination of analyses).
I need some help with the following:
If I enter "Company 1" in cell A100, I want cell B100 to display "NA". If I enter "Company 2" instead, I want cell D100 to display "NA". And if I enter "Company 3", do nothing, for example. I am OK with adding the check marks manually, as there are other variables that need not be mentioned.
Now, I have been able to develop some toy solution in VBA to some extent (please see code below). However, I have two issues:
  1. In order to run the code, I have to switch to the VBA editor and press F5 after every entry. Instead, I would like it to work like when using formulas for the cells. In other words, if I type in "Company 1" in any cell of column A and hit "Enter", I would like the "NA" to display automatically in the appropriate cells on the row. I guess I could record a macro for this, but the file is shared with many people and I would prefer to avoid that.
  2. In the future I will need to add more companies and analyses, so I need a code I can quickly go in and update. Or maybe have a list of companies that I add to and link it somehow to my code.
    VBA Code:
    Sub writeNA()
    For i = 1 To 20 Step 1
    
    x = Cells(i, 1).Value
    
    If x = "Company 1" Then
    Cells(i, 2).Value = "NA"
    End If
    
    If x = "Company 2" Then
    Cells(i, 3).Value = "NA"
    End If
    
    If x = "Company 3" Then
    Cells(i, 4).Value = "NA"
    End If
    
    Next
    End Sub
Thank you!
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
So you would like a script that would automatically run when you enter a value in column A
Is that correct.
That can be done automatically when you enter a value in column A

But when you say Company1 and company 2 and company 3

The script would have to know each company name
How can the script know that?

Like if "Apple" is entered in A3 how would a script know what column to put NA in?
Since the company names are entered in first column of each row
There is nothing in Range(G1) for example to tell the script to put NA here.
 

ChemistV700

New Member
Joined
Dec 20, 2020
Messages
3
Platform
  1. Windows
So you would like a script that would automatically run when you enter a value in column A
Is that correct.
That can be done automatically when you enter a value in column A

But when you say Company1 and company 2 and company 3

The script would have to know each company name
How can the script know that?

Like if "Apple" is entered in A3 how would a script know what column to put NA in?
Since the company names are entered in first column of each row
There is nothing in Range(G1) for example to tell the script to put NA here.
Thank you for your response. Sorry if I wasn't clear before, but the individual companies always request the same analyses. So I am thinking of having a list of what analyses Companies 1, 2, 3... request. Then if Company 1 requests analyses A, B, C, I want nothing there (I will put checkmarks manually) but I want the script to put NA's for all the other analyses.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
Thank you for your response. Sorry if I wasn't clear before, but the individual companies always request the same analyses. So I am thinking of having a list of what analyses Companies 1, 2, 3... request. Then if Company 1 requests analyses A, B, C, I want nothing there (I will put checkmarks manually) but I want the script to put NA's for all the other analyses.

Well if you plan to manually enter a check mark in the column if the company wants that analyses.
I could just have the script automatically enter NA in any cell that is empty in that row. Correct
But tell me how do you plan to put a checkmark in the cell. What font and what character do you use to enter a check mark I do not have a checkmark on my keyboard? Or I just tell the script to enter NA in all empty cells in that row as long as there is text in Row1 which is where the headers are.
 

ChemistV700

New Member
Joined
Dec 20, 2020
Messages
3
Platform
  1. Windows
That would mean putting in the check marks first, which would not work, because I end up adding them after the analysis is complete. The spreadsheet is more of a running "live" list of projects and companies we work with.
But I was able to find a solution by having a separate table (on another sheet) that establishes a pattern, then pulling that up when entering a company.

Thank you very much for taking a look at my question.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,722
Members
414,013
Latest member
tnobbs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top