take a cells value and then use the value to identify ?

[unityjon]

i'm not sure if this is possible but would be very gratefull of some help.

I have 3 spreadsheets A,B,C,D

In sheet A cell 'B8' i would like a drop down selection of sheet 'D' column A (A2:A50, as Row 1 has column headings) (no problem i can do this) but

depending on the row selected (A2:A50) i would like each selection to have a value and take that 'value' and apply it to various cell formulas in sheet A

i.e b8 = 'sheetA!a2' the value of b8 would be 1

then apply that value to other cells like this:

value =1 then 'sheet A!B15:B16' = 'sheetB!a3:a4', 'sheetA!B14'='sheetC!A4' and so on...

is there a 'sensible' formula for this operation, other than applying 50 'if' statements to each cell, (65 cells in total ?)

much thanks in advance.

novice.

 

[SusaninHouston]

Are you familiar with using Lookup tables? Sounds like another column in your D worksheet specifying the values you want to use in the A sheet would solve your problem.

I'm not 100% clear from your description, but use Excel's help to look up VLOOKUP formulas to see if this meets your needs -

 

[unityjon]

ok looked a LOOKUP VLOOKUP and HLOOKUP

it sounded promising then the mud just got thicker , i'll have to have a another look next week.

thanks for the pointer.

 

[SusaninHouston]

I've seen Excel's explanations for how to use this, and I doubt I could understand them either. But once you get the hang of it, it's not really that hard.

Assuming your data is in a table, and that the first row is titles and the first column is an 'index' value, the syntax is as follows:

=LOOKUP(MyValue,Table,ColNum,true/false)

MyValue = the value in sheet A you're trying to match
Table = the table I've just described, including row 1 and column 1
ColNum = a number representing the column in the table you want back
(1 would be the index, 2 would be the first 'data' column, etc.)
true or false = False if you want an EXACT match or an error,
True if your table is sorted in order and you want the first matching value

 

[KenWright]

It's easy to get lost in a formula if you don't really understand what the formula is actually
doing:-

The VLOOKUP function essentially takes a value that you specify, whether it be a hardcoded
number/letter/text or a value within a cell reference, and then goes and looks it up in a table.
It will look for that value in the leftmost column of the table, and either find it or the closest
match, and will then return the corresponding value on the same row, in whatever column of that
table that you tell it to:-

Example - With the following table

D E
1 1 0.20
2 10 0.25
3 20 0.30
4 30 0.35
5 40 0.40

and with your value that you are looking up in say cell A1 (and let's assume it is 25 for
example).

You can put a formula in pretty much any other cell, that says, take the value in cell A1 (25), go
and look for it in the lefthand column of the table (D1:D5), and then when you have found it (or
the lowest closest number to it), go to the second column (or whichever one you specify if there
are more than 2) and give me the number that it is on the same row in that column.

So, with the formula being =VLOOKUP(A1,D1:E5,2) it will first take the value in A1 which is 25,
then go look for it in the leftmost column (D) of your specified table (D1:E5), and it will try to
find that number. Now it isn't there, so what it will do is look for the next lower closest
number, which in this case will be 20, and the 2 in the formula says to go and get the value in
the 2nd column (E) in your table, that is on the same row as the 20. That value in this case is
0.3

If you put the value 30 or 31 or 32 etc into A1 now, you will see the result of the formula
change, because now it will either find those numbers or the lower closest number (and in each
case there it is 30), and will subsequently return 0.35 as the corresponding value.

Just to show you how the 2 really works in that formula, if you added one more column to your
table so that it looked like this:-

D E F
1 1 0.20 0.15
2 10 0.25 0.25
3 20 0.30 0.35
4 30 0.35 0.45
5 40 0.40 0.55

and you actually wanted the value from Col F, then you would simpl;y change the 2 in the formula
to a 3 to signify the third column, eg:-

=VLOOKUP(A1,D1:E5,3)

With the examples already given, 25 in A1 would return 0.35, and 30/31/32 would return 0.45

The one caveat to all of this (When getting the nearest number is OK) is that the data in your
leftmost column must be sorted in ascending order.


There are times when you would only want it to give you a value if you had an exact match on the
number, and in this instance you would simply add a 4th argument of 0 or FALSE to the formula,
eg:-

=VLOOKUP(A1,D1:E5,3,0)

or

=VLOOKUP(A1,D1:E5,3,FALSE)

In these cases you do not need to have the data in your leftmost column sorted.

Ken...............