Excell Lookups 1

[dezian]

Can anyone help, I have 3 sheets in one docement, 1 is a form and the others are data to be used as inputs for the form. What I would like to do is enter a number in the form sheet, say enter a value of 1 in A1 that tells the syntax which data sheet to use, then again in the form enter a value of say 20 in cell A2, and display its corisponding value from "sheet 1" in A3 I.e.

If the form is called "form"
Sheet 1 is "sheet 1" and sheet 2 is "sheet 2"

Entering 1 in A1 tells the code to look at sheet 1.
Then in cell A2 enter the number 20, which is a level in a tank. So in sheet 1, A1 is 10, A2 is 20 etc, in B1 and C1 are two more values, the Value of C1 is the data I need to input into the form in cell B3.

In english then, B2 should have code that says if A1 is 1, use sheet 1 to get info, then using the entered value in B2 display the value from "sheet 1" C3

Any help would be fantastic, my brains begining to throb.

Cheers Andy Hickson

 

[togatown]

dezian,

A little unclear about the 'corresponding value'. If you enter 20, where on the sheet is the 20 contain and where is the corresponding value you want returned? Do you what to do a vlookup? An example sheet or sample table would help.

From there it sounds very simple.

Toga

 

[dezian]

Toga, I have 3 sheets in excel, the 1st is used to get data from the other 2. The data is in the form of 3 columns A, B, & C all numbers. As below

Data Sheet 1 Data Sheet 2 1st Sheet
A B C A B C A1= 1 (data sheet 1)
1 10 50 10 50 1000 A2= 2 (val to lookup)
2 20 100 20 500 2000 A3= 100 (val C2 d/s 1)
3 30 150 30 600 3000

By entering a value of 1 or 2 in A1 on the first sheet will decide where the data is coming from, i.e select 1 for data sheet 1 or a 2 for data sheet 2.

Then enter a value of say 2 in A2 in the 1st sheet, the value will relate to an equivelant value in data sheet 1 or 2.

Finaly for the code bit, I need Cell A3 in the first sheet to display a value from either data sheet. So If I enter 1 in A1 and 2 in A2, A3 should display the value from C2, this is the value 2 cells to the right of the matching number entered in A2.

Hope this is a bit clearer, thanks for your time.

Cheers Andy

 

[SkipVought]

=if(a1=1,vlookup,a2,'data sheet1'!$a$1:$c$999,2,false),vlookup,a2,'data sheet2'!$a$1:$c$999,2,false))

?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[dezian]

VLookup, ive used this code but keep getting an error message highlighting $c$999. I need A3 in the first sheet to display a value from one of the 2 data sheets. The value entered in A1 shows which data sheet to use, A2 in the first sheet shows what value to look for in the data sheet and A3 of the first sheet should display the value 2 cells to the right from the corrasponding value in the data sheet. I'm not doing very well am I !!

Thanks for your time.

Andy

 

[PHV]

Works for me. Formula in cell A3 of Sheet1:
=IF(A1=1,VLOOKUP(A2,Sheet2!$A$1:$C$999,3,FALSE),VLOOKUP(A2,Sheet3!$A$1:$C$999,3,FALSE))

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884