Auto populate cell based on value in corresponding cell

[swannylfc]

Hi there

I have a spreadsheet which has a column called 'Country'. Depending on the value of each cell in this column, another column on the row may need to be populated with another cell value (including the colour format) which is on the side of the page in a table format as below.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]CODE[/TD]
[/TR]
[TR]
[TD]Ireland[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Scotland[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Wales[/TD]
[TD]Yellow[/TD]
[/TR]
</tbody>[/TABLE]


So in the Country column, which is F3:F200, if cell F3 = 'Ireland' I want the Code column (cell H3) to automatically show 'Green' (including the red text as shown in the above table). If F4 = 'America' - which is not on the table - then leave H4 empty. If cell F5 = 'Wales' then show 'Yellow' in H5 and so on.

Could somebody please point me in the right direction for going about accomplishing something like this, without making a giant 'IF' chain (as there are more entries in the table than in the above example). Thank-you.

 

[jarjarbingie]

use the table above as a reference table instead.. then use the following:

H3 = IFERROR(VLOOKUP(F3, reference table, 2, 0), "")

for the formatting, just go to cell formats and change font color, all of them should be in RED anyway..

Hi there

I have a spreadsheet which has a column called 'Country'. Depending on the value of each cell in this column, another column on the row may need to be populated with another cell value (including the colour format) which is on the side of the page in a table format as below.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]CODE[/TD]
[/TR]
[TR]
[TD]Ireland[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Scotland[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Wales[/TD]
[TD]Yellow[/TD]
[/TR]
</tbody>[/TABLE]


So in the Country column, which is F3:F200, if cell F3 = 'Ireland' I want the Code column (cell H3) to automatically show 'Green' (including the red text as shown in the above table). If F4 = 'America' - which is not on the table - then leave H4 empty. If cell F5 = 'Wales' then show 'Yellow' in H5 and so on.

Could somebody please point me in the right direction for going about accomplishing something like this, without making a giant 'IF' chain (as there are more entries in the table than in the above example). Thank-you.

 

[swannylfc]

use the table above as a reference table instead.. then use the following:

H3 = IFERROR(VLOOKUP(F3, reference table, 2, 0), "")

for the formatting, just go to cell formats and change font color, all of them should be in RED anyway..

Hi jarjar, thanks for your answer. It is working nicey, except it is not bringing across the colour formatting. I should have mentioned that the red colour was a (bad) example, what I actually need is the text on a green background with highlighted border. That is how it is in the reference table, but it is just returning the reference table cell text and not the background nor the highlighted border. Any suggestions on how to approach this? I should point out that formatting all the cells to have green backgrounds etc wouldn't work if there is no match in the reference table I want the cell to remain white and empty (much like me, haha). I should also point out that there is another section in the reference table which has a blue background and white text.

 

[jarjarbingie]

hmmm.. you could set the conditional formatting rules on column H based on the value, like if it contains "Green", fill it with green background and white font color, if it contains "Blue", fill it with blue background and white font color..

Hi jarjar, thanks for your answer. It is working nicey, except it is not bringing across the colour formatting. I should have mentioned that the red colour was a (bad) example, what I actually need is the text on a green background with highlighted border. That is how it is in the reference table, but it is just returning the reference table cell text and not the background nor the highlighted border. Any suggestions on how to approach this? I should point out that formatting all the cells to have green backgrounds etc wouldn't work if there is no match in the reference table I want the cell to remain white and empty (much like me, haha). I should also point out that there is another section in the reference table which has a blue background and white text.

 

[swannylfc]

hmmm.. you could set the conditional formatting rules on column H based on the value, like if it contains "Green", fill it with green background and white font color, if it contains "Blue", fill it with blue background and white font color..

Thanks jarjar, I have it now. You're the man.