trying to write a formula ivolving data validation

Is it possible to write a formula for my example below


1 cell has a list in it e.g. Beverage, food, Accomodation.

If you select beverage, cells b15,c15,d15 all change colour
If you selected food, cells b16,c16,d16 all change colour.

In another worksheet I want to be able to click on a cell and depending
on whether i have beverage or food selected from my list the answer
appears in my selected cell.

Does this make sense and is it possible

Cheers


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

In the same file and a different sheet, or altogether different file.
Alos, you want the data to be displayed on merely selecting a cell..?

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

another sheet in the same workbook.

I will have a link setup so that the information is displayed in that
cell.

Its like a summary sheet and that why I need to be able to write the
formula which can differentiate btween a drop down list.


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

you could simple write a formula like:
=Sheet1!A1

where A1 contains the value you want to carry forward to the other
sheet.

Or am I missing something...?

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

I didnt explain it properly,

When I select from my list lets say Food which makes cell b16 change
colour, and when I select beverage from my list which makes b17 change
colour.

But on my other worksheet I only have 1 space for food and beverage, so
I need the formula to be able to differentiate between when food is
selected and give me the text from cell b16 and when beverage is
selected and give me the text from cell b17,

Is that a bit better ??


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

Yes much better.
Enter in sheet2, whatever cell
=IF(Sheet1!A1="foods",b16,b17)

where A1 in sheet 1 contains the foods/beverages list

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

but i need to get the beverage text aswell, so can it be written with
beverage in the formula as well??


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

The above formula will give the result for foods, when food is selected,
and when beverage is selected, it will give the beverage results.

If you don't need the above scenario, then can you explain your
position what it is right now in sheet1.

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

You can use conditional formatting to colour the cells based on what is
selected. There are instructions in Excel's help, and he

http://www.contextures.com/xlCondFormat02.html

To create the dropdown list on the other worksheet, you can use a
dependent list. There are instructions he

http://www.contextures.com/xlDataVal02.html

melben wrote:
Is it possible to write a formula for my example below


1 cell has a list in it e.g. Beverage, food, Accomodation.

If you select beverage, cells b15,c15,d15 all change colour
If you selected food, cells b16,c16,d16 all change colour.

In another worksheet I want to be able to click on a cell and depending
on whether i have beverage or food selected from my list the answer
appears in my selected cell.

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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 ??


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