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.
 

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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,119
Messages
5,857,487
Members
431,882
Latest member
saaaaaaaaaaaaaaaaaaaaaa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top