trying to write a formula ivolving data validation

  • Thread starter Thread starter melben
  • Start date Start date
M

melben

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 dependin
on whether i have beverage or food selected from my list the answe
appears in my selected cell.

Does this make sense and is it possible

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

Manges
 
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.
 
you could simple write a formula like:
=Sheet1!A1

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

Or am I missing something...?

Manges
 
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 ??
 
Yes much better.
Enter in sheet2, whatever cell
=IF(Sheet1!A1="foods",b16,b17)

where A1 in sheet 1 contains the foods/beverages list

Manges
 
but i need to get the beverage text aswell, so can it be written with
beverage in the formula as well??
 
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 you
position what it is right now in sheet1.

Manges
 
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 ) change
colour and that is where the next drop down list for Dinner food is.

But alternatively if I select beverage then the cell ( b17 ) change
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 o
drinks

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

Is that a bit clearer ?
 
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
 
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 fo
C20, then you could try entering the following in cell C20:
=IF(Sheet1!B16="",Sheet1!B17,Sheet1!B16)

Mangesh
 
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 ??
 
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
 
That was exactly what I needed,
Another thing if you dont mind, now that I have the formula in plac
when nothing is selected an 0 or even $0.00 appears in the cel
conatining the formula. Is there anyway for me to be able to have th
cell blank if there is nothing selected.

Cheer
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Payment calculation 1
Copying a formula containing the INDIRECT function 7
Using AND or OR in a IF formula 3
Format linked workbook cells (updated) 2
Problem of having more than 7 IF statement 1
avoiding circular references 2
copy data from 1 sheet to other sheet. 1
using a cell's reference to return a value. 3

Back
Top