Excel help

PixieLandGirl · 16 Jan 2010 at 23:56

Just wondered if anyone can help, I want a formula or function that would allow for the following:

if(A1="yes", B2 ..... background = purple

I looked in conditional formatting but that seems to edit the cell with the correct data "yes" and not an alternative cell, any help would be great.

Poolybit · 17 Jan 2010 at 00:01

Hey,

What you will need to do is put the conditional formatting on the cell that you wish to change background and then specify a formula to check for the other cell's value.

In the example you've given, you would need to click on B1, go to Format > Conditional Formatting. Select the drop down where it says "Cell Value Is" and select "Formula Is". Into the formula box put in =$A$1="Yes" (or whatever you want the formula to check against) and then set the conditional formatting options (background colour etc).

That should (hopefully) have the result you are looking for.

PixieLandGirl · 17 Jan 2010 at 00:04

Ah ty, I had done similar but i'd missed the $ parts out.
Ty muchly

Poolybit · 17 Jan 2010 at 00:05

No problem

PixieLandGirl · 17 Jan 2010 at 01:02

Ok next brain picking question

.

I have a drop-down list that gets its info from Data!a1:a10
Is there a way to get the selected item from the drop-down list to pull information from cells Data!B1:E1 & display the info on the current worksheet?

Eg: Select an item of clothing & then it fills in the Size, Colour etc next to it

PMKeates · 17 Jan 2010 at 19:51

In the fields for size, colour etc. use a VLOOKUP formula to the table. Something like this: http://www.mkeates.com/vlookup_example.xls.

You'll notice that if you set the item to nothing, you get nasty "#N/A" errors in the size and colour fields. You can sort that by changing the formula from, say, =VLOOKUP(I9,A1:C10,2,FALSE) to =IF(ISERROR(VLOOKUP(I9,A1:C10,2,FALSE)),"-",VLOOKUP(I9,A1:C10,2,FALSE)). This will return a "-" when it can't find the item.