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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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!
 

Forum statistics

Threads
1,171,790
Messages
5,877,506
Members
433,264
Latest member
Donnybrook

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