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

[bstreiss]

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 n^th 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.

 

[FDibbins]

Not quite sure I understand you, but take a look at using INDEX/MATCH/MATCH for this

 

[bstreiss]

=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.

 

[kvsrinivasamurthy]

Try this

=INDIRECT("Data[@B"&ROW()&"]")

 

[bstreiss]

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.

 

[kvsrinivasamurthy]

Post the image of table and what is the value in B1.

 

[bstreiss]

excel_grab.jpg - Google Drive

I can't figure out how to get the image to post.

 

[Rick Rothstein]

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)

 

[bstreiss]

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!.

 

[djuksi13]

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