Dynamic Conditional Formatting based on List Box?

[BigRods]

Hi,

I wondered if there was a way to set conditional formatting on a sheet where the worksheet the formula references changes based upon what the user selects in a list box?

i.e. I want a cell on my front sheet to colour green or red based upon a vlookup result - if the user selects an entry from the list box, I'd like the vlookup formula to reference the worksheet selected in the list box.

I could do it via VBA, but wondered if there was a non-coding way to do it?

Thanks!

 

[BenGee]

Hey

If I've understood right, you can select the cell then under the home tab, select conditional formatting, select new rule, select format only cells that contain and then adjust the rule description to meet the 1st result (i.e. "Specific Test" and "Containing" and "Yes"). Then select format and adjust the conditioning to whatever you want. Then, repeat these steps for your 2nd condition.

Hope it helps but forgive me if I've misunderstood your question.

 

[baitmaster]

you probably want to investigate INDIRECT

So you might have a formula that says
=Sheet1!A1

You could replace this with
=INDIRECT([sheet name] &"!A1")
where [sheet name] is the piece of text selected using your drop down and must exactly match any sheet name

You'll need to consider all other syntax, so if your formula needs apostrophe (where sheet name includes a space) you'll need to include it, I omit from above as it's difficult to read it amongst the quotes below:
=INDIRECT("'" & [sheet name] &"'!A1")