Need help with If Function between sheets

[pearl11]

I am trying to simplify having to input fixed information.

I have information on two excel sheets/tabs

One sheet is where i want to input data

the other sheet is where i want it to grab the info.

So if I put in 3 in A1, it will look on the second sheet and grab a number from B3 and place this number in A2 (on the first sheet).

If I put in 2 in A1, it will look on the second sheet and grab a number from B2 and place this number in A2 (on the first sheet).

I was able to figure out how to it for one number, but if you ask other numbers it doesn't work.

=IF(A1=3,Sheet2!B3)

so that formula works for the number 3. what if I put in the number 2 or 9 or 211?

Any suggestions?

 

[MrKowz]

Welcome to the boards!

Do you have any more conditions? Do you want it so if you put in 4, it will return Sheet2!B4, if you put in 5 it will return Sheet2!B5, etc?

 

[texasalynn]

Welcome to MrExcel board...

try this

=IF(A1=3,Sheet2!B3,IF(A1=2,Sheet2!B2,IF(A1=9,Sheet2!B9)))

 

[MrKowz]

You could try:

=INDIRECT("Sheet2!B" & A1)

 

[pearl11]

that works great, thanks

is there a limit on how long the formula can go, like if I am if AI=1000 etc
this would be a large formula

 

[texasalynn]

look at the suggestion by MrKowz

 

[pearl11]

that suggestion works for lots of entries, thanks

yet when i label the columns with titles, this throws off the count

how do i get around this?

 

[MrKowz]

What do you mean? Columns are A, B, C, D, etc... What does your Sheet2 look like? What is your overall goal with this formula?

 

[texasalynn]

that suggestion works for lots of entries, thanks

yet when i label the columns with titles, this throws off the count

how do i get around this?

What are you referring to - "COUNTS"?

 

[pearl11]

the way the formula is written, it is drawing information from sheet 2 starting from B1 while I want it to start at B3. currently when it draws info from sheet 2 cell B1, on sheet one I am seeing the title.