Need help with If Function between sheets

pearl11

New Member
Joined
Dec 16, 2010
Messages
30
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?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
Welcome to MrExcel board...

try this
Code:
=IF(A1=3,Sheet2!B3,IF(A1=2,Sheet2!B2,IF(A1=9,Sheet2!B9)))
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You could try:

=INDIRECT("Sheet2!B" & A1)
 

pearl11

New Member
Joined
Dec 16, 2010
Messages
30

ADVERTISEMENT

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
 

pearl11

New Member
Joined
Dec 16, 2010
Messages
30

ADVERTISEMENT

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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 

pearl11

New Member
Joined
Dec 16, 2010
Messages
30
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,753
Messages
5,524,654
Members
409,597
Latest member
Dannydev

This Week's Hot Topics

Top