Way to dynamically address a structured reference

[Carl Massa]

Is there a way to tie a structured reference to a cell?


Example I have a table with 20 columns.

1=SID

2=DG1_PLAN

3=DG1_ACT

4=DG2_PLAN

5=DG2_ACT

6

7

etc


I know this address works:

=Table[[#DATA],[DG1_PLN]]

meaning the data table ="TABLE", column="DG1_PLN"


BUT I want to "tie" [DG1_PLN] to a cell (A1) this would allow me to write the code 1 time, copy to another cell and then chage the value


I tried =Table[[#DATA],[$D$5]]

Where Cell d5 contained "DG1_PLN".


Any thoughts?

 

[SirJB7]

Hi, Carl Massa!

I don't finish to understand your issue. Where does that address work? What is #DATA?

Can you please explain with more detail or upload a sample file? Thanks.

Regards!

 

[Carl Massa]

I have one, but I am not sure how to upload it...

 

[dan_l]

http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[Carl Massa]

Download link:


http://speedy.sh/gfRRM/ChandooEx1.xlsx


What I am trying to do is in the file. THe "TABLE" is from a SQLSVR download.


Yellow columns are entered by user. Database to be refreshed upon entry of yellow fields, Then I want them to be able to fill in CELL "C22" with any fieldname [all choices are in green] Then the spreadsheet would return the correct date

 

[SirJB7]

Hi, Carl Massa!

I defined a named range called TableHeaders just for A5:L5, and I replaced the field name by the column number. In cell E22 I wrote:

=BDEXTRAER(Table_Query_from_SQLS20084[#Todo];COINCIDIR(C22;TableHeaders);$B$1:$C$2) -----> in english: =DGET(Table_Query_from_SQLS20084[#Todo],MATCH(C22,TableHeaders),$B$1:$C$2)

It retrieved the same value as D22, 05/02/2010, but... I copied down to check with all the other green cells, and I got several #¡VALUE!... Check them, please. Here's the link:

http://www.2shared.com/file/rvwWj7dY/ChandooEx1-_Way_to_dynamically.html

Just advise if any error.

Regards!

 

[Carl Massa]

I was playing with this too and I came up with the same answer, as long as you use the column number rather than the [FIELDNAME] this does work. Now I can put the FIELDNAME in dropbox and let the user define the "field" they want to see


thanks

 

[NARAYANK991]

Hi Carl ,


Is your problem resolved ?


Narayan