Getting Data from Sheet1 knowing only the Coordinates

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
I feel embarrassed but I can't seem to figure this one out.


This is my Sheet 2:
Book1.xls
ABCD
1Column No.Row No.Result
2Product C35
3Product D58
Sheet2



I want to get the result from Sheet 1, knowing the coordinates are as shown in the table.

I can't figure out how to best combine the fact that the data is in sheet1 and the address(c2,b2) function.
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
I could have sworn that I tried that one (it must have been some version of it that obviously didn't work). Sometimes you just get those brain freezes.:unsure:

Thanks a lot, Kristy :biggrin:
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
If your data in Sheet1 is set up in a table, you could also just use Vlookup to find the name in the list then return the info from the column you want.

If you want to keep the same setup on Sheet2, you could use something like:

=VLOOKUP($A2,Sheet1!$A$1:$E$10,$B2,0)

A1:E10 would be the range containing all of the data in the table, and B2 tells it what column to return the data from, using the number in your Table on Sheet2.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
I think I figured out why it didn't work before I posted.


My actual Sheet1 was named "A sheet", but the formula =INDIRECT("A data!"&ADDRESS($C2,$B2)) returns a #Ref! error. As soon as I change the name to eliminate the space (i.e Asheet), the formula works!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,092
Members
412,310
Latest member
mark884
Top