Stumped on how to retrieve data from a specific cell using structured reference selected from outside of the table

bstreiss

New Member
Joined
Jul 12, 2015
Messages
8
Here's a simplified example of what I'm trying to do:
Table: Data has columns based by month name
Cell B1 contains a list of the the columns in table Data.

I'm trying to build a formula that basically says: take the value from the nth row of table Data, in the column listed in cell B1 (which isn't contained within the table).

I'm using the drop down list in other places and would like to automate it so that I can just select the month and everything will in.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Not quite sure I understand you, but take a look at using INDEX/MATCH/MATCH for this
 
Upvote 0
Code:
=INDIRECT("Data[@"&B1&"]")
is as close as I've been able to come, except that what I want isn't the current row, but whatever row I choose.

I'll take a look at index/match/match and see if I can get it to work. I'd post a screenshot but I can't seem to get Google Drive to give me a file format this board can handle.
 
Upvote 0
I get a #REF ! error, and I'm not sure that what I want to do anyways. As I understand the formula I added it is looking in the current row of the table "Data", in the column that contains the same name as the value of B1. Incase it wasn't clear, B1 isn't part of the table. Rather than getting the value of the cell of the row we are in I want to be able to specify the row, let's call it 7 to make things easier. What I'm trying to do is something like =indirect("Data[10"&b1&"]") but as I understand it one can't reference rows in structured data.

My big problem is that my table will be growing in column over time, so I'm trying to build flexible formula that will expand along with the tables. I don't think I can use a pivot table because sometimes I need values from single cells, other times I need subtotals, etc.
 
Upvote 0
I get a #REF ! error, and I'm not sure that what I want to do anyways. As I understand the formula I added it is looking in the current row of the table "Data", in the column that contains the same name as the value of B1. Incase it wasn't clear, B1 isn't part of the table. Rather than getting the value of the cell of the row we are in I want to be able to specify the row, let's call it 7 to make things easier. What I'm trying to do is something like =indirect("Data[10"&b1&"]") but as I understand it one can't reference rows in structured data.
Not sure why you said "let's call it 7" for the row number and then use 10 (I think) for the row number in your attempted formula. Anyway, see if this works for you (assuming Row 7)...

=INDEX(INDIRECT("Data["&B$1&"]"),7)
 
Upvote 0
Thanks for that catch. :) I was getting confused between the row number of the sheet and the row number of the table. In any case, that's exactly what I needed!.
 
Upvote 0
Hi everyone,

I have similar problem and don't know how to solve it.
I'll try to describe.

I have 2 sheets, in 1st sheet I get data from our ERP system with SQL statement modified in table structure. In 2nd sheet I want to reference on a specific cell from the 1st sheet and just paste it.
I tried with =Table_name[col_name], also with =index(Table_name[col_name],row_num) but it doesn't work!

Can you help me?
If you need more details please let me know.

Best regards
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

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