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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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:
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,211,685
Messages
6,103,294
Members
447,853
Latest member
olddutch7

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
Back
Top