EXCEL : Automatic data entry in a cell depending on list selection

Lil306

OK.....

I'm building my spreadsheet to contain a gas log .....

COLUMN A               COLUMN B

01/01/01                 0008
02/01/01                 1234
03/01/01                 5678
04/01/01                 9876
05/01/01                 10101

Now, I want to have a drop down list but I want to limit it so then when I pick say 01/01/01 it will put the entry for 0008 into that cell wich will then be used as the value to calculate a gas bill.

How's it done, if I simply choose the date it's converted and gives the wrong value.

I'm sure it's something to do with a list / data validation but I've got numb brain tonight

HoofeHearted

Not sure if this is what you want, but would something like this work?

Columns A and B as you showed.

Column D is where you enter the date for the calculation.

Column E is where the calculation is made.

You enter the required date in column D and the formula in Column E is
=IF(D1="","",VLOOKUP(D1,$A$1:$B$2000,2,)*12.345)

This means the following:-
o If there is no date in D1, then make E1 "blank", otherwise
o Lookup the date entered in D1, anywhere in Col A to the end of the sheet (2000 being the no of entries). When the date is found, use the value found in the corresponding cell in Col B (B=col 2).
o Then multiply the value by 12.345 (Replace this with whatever calculation you want)

Does that do what you want?

Lil306

Hi Hoof,

Not sure if that's what I'm looking for but thanks for replying....

Try to think of it like this..

(Date)                  (Gas Units Used)      (Select date)         (Actual value)
COLUMN A             COLUMN B              COLUMN C             COLUMN D
01/01/01               999                       drop down list        loads a value depending on date chosen from c1

Basically I have a list of dates (Column A), which will have a list of gas readings for those dates (Column . I then have a drop down list (data validation or w/e, Column C.... i.e please choose a date to load readings), then when you choose the date you want the readings for Column D displays that value

Hope that explains it better. I think a vlookup would be similar, but I don't think it would work for choosing a specific date

TheMiddle

Hi,

The VLookup is a good way to achieve this, you would want the drop down list in D1 (using the example below)

Try Tools>Validation for drop down list options.

Hope this helps!

TheMiddle

Think i've got it - Do you want to PM me your email address?

TheMiddle

I've done it for you:

download it here: www!sk6computers!uk!net/mse.xls

(Replace ! with . )

Cheers

TM

Lil306

Thanks, I'll play around with that and adjust accordingly, wouldn't have thought you could use vlookup with a list but meh, learn summat new everyday

TheMiddle

It is using the Vlookup, and, one of the key parts of the Vlookup is the cell that specifies what it is you want to look up - one your spreadsheet you have a drop down list... which is fine!