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

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

Sorry mangesh, I still have a couple of questions if thats o.k

Firstly I'm using a simple formula to copy the contents of 1 cell into
another cell on a different worksheet, this is in regards to the time.
Is there anyway that I can write that ormula so that it only shows the
contents if there is an actual time written into the cell ?

Also, with the last formula that you gave me in regards to the food and
beverage, I'm using that over 3 cells now but when i select something
from my lists in 2 cells the 3rd cell which has nothing selected is
showing a value of $0.00, is there any other way around this ??


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

Hi Melben,

no problem.

Firstly I'm using a simple formula to copy the contents of 1 cell into
another cell on a different worksheet, this is in regards to the time.
Is there anyway that I can write that ormula so that it only shows the
contents if there is an actual time written into the cell ?

I don't think there is, because time is basically a number and excel
cannot differentiate between two numbers whether it is a time or a
simple number.


Also, with the last formula that you gave me in regards to the food and
beverage, I'm using that over 3 cells now but when i select something
from my lists in 2 cells the 3rd cell which has nothing selected is
showing a value of $0.00, is there any other way around this ??

Can you give your sample data with expected answer and cases.

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