Excel help

[cowasaki]

I've used Excel for years and done some rather complicated spreadsheets but I keep forgetting certain things. The help system leave a lot to be desired so if any Excel users out there can help me please.....

1.

I need to create a drop down containing words such as Case, Battery, LCD, Button, Resistor with these words stored in another sheet called DATA in the column A

Once I do this I can then look up the value in the next column referenced from the first using VLOOKUP.

2.

Can I reference a location in another sheet using a formula where part of the formula is a cell value i.e.

Cell E1 = numerical value 7

Cell F4 I want to use the formula =DATA!B7 but the 7 needs to come from the value in E1 i.e. =DATA!B{E1}

3.

Can I set:

D1 to be the first character of the sheet's name
D2 to be the characters 3 to 5 of the sheet's name
D3 to be the characters 6 to 9 of the sheet's name


Thanks in advance, I am writing a complete stock, production and ordering system for my triggers!!

 

[Phil V]

I've used Excel for years and done some rather complicated spreadsheets but I keep forgetting certain things. The help system leave a lot to be desired so if any Excel users out there can help me please.....

1.

I need to create a drop down containing words such as Case, Battery, LCD, Button, Resistor with these words stored in another sheet called DATA in the column A

Once I do this I can then look up the value in the next column referenced from the first using VLOOKUP.

2.

Can I reference a location in another sheet using a formula where part of the formula is a cell value i.e.

Cell E1 = numerical value 7

Cell F4 I want to use the formula =DATA!B7 but the 7 needs to come from the value in E1 i.e. =DATA!B{E1}

3.

Can I set:

D1 to be the first character of the sheet's name
D2 to be the characters 3 to 5 of the sheet's name
D3 to be the characters 6 to 9 of the sheet's name

Thanks in advance, I am writing a complete stock, production and ordering system for my triggers!!

1 yes and yes

2 I don't understand. What would be in cell Data!b7 why couldn't it just be the data that's in e1?

3. Yes but I'm not sure it wouldn't be easier to reference it through another cell value (which could be either a copy of the sheet name or a direct reference to it.

.

 

[-RV-]

The INDIRECT function might help

2.
Set E2 (or any other cell) to be =CONCATENATE("DATA!B",E1)
Then in F4 do =INDIRECT(E2,TRUE)

Hope you understand

 

[beeje25]

can you give an example of what you want the output of the drop down to be I might be able to work out a way

 

[TopBanana]

1. Click on the cell where you want the drop down list. Go to Data>Validation. Change 'Allow:' to 'list' then change the source to where you have the data (e.g. battery) for the list.
The way I last implemented that was I had a list of options in column A, with their associated values in column B, and then another cell on the sheet for the 'currently used value'. This 'currently used value' cell had an IF statement in which detected which option had been chosen, then took the corresponding value. This way all my equations just referenced this 'current values' cell to minimise the size of the formula in the main equation cell. VLOOKUP may work better for you in your instance of course.
I hope that that all made sense.

 

[cowasaki]

Thanks everyone, I have managed to sort it finally