Dynamically color fill from one worksheet to another

[Dugzilla]

First, you all are very helpful, kind, and most of all patient.

In. the following table (TABLE A), I would like to fill the appropriate cell color (in the next table (TABLE B), when a particular team is chosen.. For example, if I select ARI in cell T36, I would like to dynamically pull the correct color for ARI into TABLE B cell U35, or an cell for that matter, located in TABLE A, I2 (below). I am a MAC user and notice Excel for MAC is limited. I'm assuming this can be done through conditional formatting, but haven't been able to figure out a formula... if it can be done at all. Once I get this working I would do the same for font color.

Thank you so much!

TABLE B

TABLE A

 

[Eric W]

OK, I don't have a mac, so I can't vouch for my comments on a mac, but as far as I know they're valid.

First, no formula, not even Conditional Formatting, can "pull" a color from another cell. CF can change font and background colors, but these colors are hard coded in the CF rules. But you still have options.

1) Use CF. You'd have to have a different rule for each team, so 32 rules. Kind of cumbersome, but possible, and might be a one-time setup thing, depending on your workbook.

2) On your table A, you could have a cell on each line with the name in the right colors. Then use this trick to display that cell in your table B.

Vlookup that copies formatting?

Hey there, I tried googling around and couldn't find a good answer for what I need. I am making a small information lookup tool in excel where I select a company name and it displays the information. One of the cells is for comments and I want the comment cell to retain formatting when it gets...

www.mrexcel.com

3) I'm not sure how you get the logo in T35 in table B. Is that pulled from your table A somehow? Here's another trick that might work, although the #2 trick should also work:

Picture Lookup in Excel using Named Ranges

Learn how to use Named Ranges to do a Picture Lookup in Excel. This is a cool excel dashboard trick where pictures update dynamically based on selections

trumpexcel.com

4) Finally, you could use VBA. Probably the easiest and closest to your original request, if you're ok with a macro-enabled workbook. In this scenario, we'd add an event trigger on the T36 cell. Whenever that cell is changed, it triggers a macro to run. It would look at the value of T36, then it would find the colors from your table A, then set U35 to those colors.

Anyway, let me know if any of these seem like they'd work for you.

 

[Dugzilla]

OK, I don't have a mac, so I can't vouch for my comments on a mac, but as far as I know they're valid.

First, no formula, not even Conditional Formatting, can "pull" a color from another cell. CF can change font and background colors, but these colors are hard coded in the CF rules. But you still have options.

1) Use CF. You'd have to have a different rule for each team, so 32 rules. Kind of cumbersome, but possible, and might be a one-time setup thing, depending on your workbook.

2) On your table A, you could have a cell on each line with the name in the right colors. Then use this trick to display that cell in your table B.

Vlookup that copies formatting?

Hey there, I tried googling around and couldn't find a good answer for what I need. I am making a small information lookup tool in excel where I select a company name and it displays the information. One of the cells is for comments and I want the comment cell to retain formatting when it gets...

www.mrexcel.com

3) I'm not sure how you get the logo in T35 in table B. Is that pulled from your table A somehow? Here's another trick that might work, although the #2 trick should also work:

Picture Lookup in Excel using Named Ranges

Learn how to use Named Ranges to do a Picture Lookup in Excel. This is a cool excel dashboard trick where pictures update dynamically based on selections

trumpexcel.com

4) Finally, you could use VBA. Probably the easiest and closest to your original request, if you're ok with a macro-enabled workbook. In this scenario, we'd add an event trigger on the T36 cell. Whenever that cell is changed, it triggers a macro to run. It would look at the value of T36, then it would find the colors from your table A, then set U35 to those colors.

Anyway, let me know if any of these seem like they'd work for you.

Hello Eric and thank you for your response. I will check out your ideas.

Regarding pulling the images/icons dynamically, in Table A (using Arizona as an example): Insert > Picture > Place in cell > Picture from File. In Table B I did an Index/Match function.

Now I'll get to work on the rest.