Structured references: Keep names through copy-past

1
votes

So I'm using structured references for user data. Eventually we'll have a proper import procedure, but I was hoping to push that back to later and instead use copy-past of their raw data for now.

So I have a table, say tData, that contains a a copy of raw data output. That output already comes with headers, which are actually used in the structured references to the data throughout the workbook.

My problem is that the raw output isn't 100% stable - e.g. some columns may appear & go. Those aren't used in calculations, but it does affect slightly the table structure (position of columns). I can't control the raw output.

I was hoping to instruct them for now to copy-past directly. As I am working with the headers & not position, I thought the formulas would still work.

Summarize in the images below: =tTest[Column2] is the formula set for the box on the right. It should refer to the content of column2 (so 2). If I copy-past different headers, with "Column0" that shifts everything to the right, you can see that a positional reference is actually used by excel to refer to your data. It now returns "1" and Excel even changed the formula to "=tTest[Column1].

enter image description here

enter image description here

Seems wrong to me - e.g. if you reference something by name you don't expect it to actually be referenced by position.

I already tried tTest[[header]:[header]] and it doesn work either.

1
excelexcel-formula
You could use INDIRECT I guess, =INDIRECT("tTest[@Column2]") - Bob Phillips
Interesting proposal - I was actually trying to make the reference a text but hadn't thought of INDIRECT. I'll just check first to make sure I wouldn't loose flexibility - as this is more of a hack while waiting to have a proper import procedure. I'll get back to see what I do. - logicOnAbstractions

1 Answers

0
votes

So, in the end, better write a proper import procedure I think. I won't post here because it's somewhat involved and there are various checks to perform. But overall steps are:

  • File picker to select the data to input
  • Copy everything into ThisWorkbook, close input file
  • Match the column headers from input data with mytable.HeaderRowRange
  • Copy the raw data into the appropriate column if match if found

So basically write a script to do a match on column headers, which is was I thought Excel would be doing. And still think it should do. However thinking about it, it is probably a lot simpler to code it this way than to have it actually dynamically adapt named references and not positions....

As mentioned by Bob Phillips, you can use INDIRECT. However that does involve adding an extra INDIRECT(reference_to_table_here) to, well, all references. Excel worksheet formulas are clunky enough I think without have to make them any clunkier.

Even though you write the formulas using column names, in the background Excel actually uses position of the column within your table for its calculations. Whenever you change the layout of the workbook, it updates the position for you.