trying to write a formula ivolving data validation

Thanks Debra, but i already have set up the drop down lists and the
condition formatting for the colour change, what im looking for now is
a formula to link everything I havedone together.

Thanks for those references though, will be sure to check them out

Cheers


--
melben
------------------------------------------------------------------------
melben's Profile: http://www.excelforum.com/member.php...o&userid=23619
View this thread: http://www.excelforum.com/showthread...hreadid=373824

Hi,
Ok. Consider the result of your first drop-down is in cell b1 (sheet1),
in that case, on sheet2 C20 you can enter the formula:
=IF(Sheet1!B1="foods","foods","beverages")

Instead, if you want to check the contents of cells b16 and b17 for
C20, then you could try entering the following in cell C20:
=IF(Sheet1!B16="",Sheet1!B17,Sheet1!B16)

Mangesh








I have 3 drop down lists on sheet 1
- 1 for food, beverage
- 1 for dinner food
- 1 for dinner drinks

In the first drop down list if I select food the cell ( b16 ) changes
colour and that is where the next drop down list for Dinner food is.

But alternatively if I select beverage then the cell ( b17 ) changes
colour and that is where the dinner drinks drop down list is

On Sheet 2 I have 1 cell ( c20 ) which will be for either food or
drinks

so i need a formula for cell c20 so that it can show the result of my
earlier choices ( being that if I first selected food or if I had
selected beverage )

Is that a bit clearer ??


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=373824

O.K I think i have a better way to explain it now. Mangesh your last
formula was vry close to what I need.

The formula needs to be able to distinguish between what is selected in
B1. For instance if food is entered into B1 then the formula must read
from cell B16, but if Beverage is selected in Cell B1 then the formula
needs to know to read the information from cell B17

Can that all be written into 1 formula, so that is knows to refer to
different cells if you have a certain selection from a drop down box
selected ??


--
melben
------------------------------------------------------------------------
melben's Profile: http://www.excelforum.com/member.php...o&userid=23619
View this thread: http://www.excelforum.com/showthread...hreadid=373824

So it would be a combination of the 2 formulae i sent earlier.

Enter in C20:
=IF(Sheet1!B1="foods",Sheet1!B16,Sheet1!B17)

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=373824

thanks mate, I'll give that a try and let you know how I went


--
melben
------------------------------------------------------------------------
melben's Profile: http://www.excelforum.com/member.php...o&userid=23619
View this thread: http://www.excelforum.com/showthread...hreadid=373824

Will wait for your reply. Do let me know if that worked for you.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=373824

That was exactly what I needed,
Another thing if you dont mind, now that I have the formula in place
when nothing is selected an 0 or even $0.00 appears in the cell
conatining the formula. Is there anyway for me to be able to have the
cell blank if there is nothing selected.

Cheers


--
melben
------------------------------------------------------------------------
melben's Profile: http://www.excelforum.com/member.php...o&userid=23619
View this thread: http://www.excelforum.com/showthread...hreadid=373824

=IF(Sheet1!B1="","",IF(Sheet1!B1="foods",Sheet1!B1 6,Sheet1!B17))

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=373824

Cheers for that


--
melben
------------------------------------------------------------------------
melben's Profile: http://www.excelforum.com/member.php...o&userid=23619
View this thread: http://www.excelforum.com/showthread...hreadid=373824

Thanks for the feedback.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=373824