links to table corrupting when new data pasted in

[ajm]

I download a report from our ERP which is then pasted into a file. The destination worksheet ("T1 Download") is set up as a table (Table1) so that the range expands appropriately as the records increase each week. On another tab in this workbook ("CalcSheet"), there is a second table (Table2) that links to the original. Formulas used in Table 2 are very simply copying the contents of the same cell in Table1. so, the first cell in Table 2 (range A6) contains the formula "= 'T1 Download'!A6". The worksheet was set up this way to reorder the downloaded data by column. so, each row in Table2 corresponds to the same row in Table1.

Problem i am having lately is that when the new data is pasted into Table1, the formulas on the second tab ("CalcSheet") seem to drop a few rows so that i end up with fewer rows of data on this second tab.

Has anyone else experienced anything like this? Has anyone got a suggestion(s) on how to do the column reorder task that "CalcSheet" performs?

 

[Peter_SSs]

Instead of a formal table on CalcSheet, how would it be if you just had a formula like this to bring in the whole of Table1 but with the column order you want (assuming that you have the CHOOSECOLS function)

=CHOOSECOLS(Table1[#All],1,3,2,4)

 

[ajm]

Instead of a formal table on CalcSheet, how would it be if you just had a formula like this to bring in the whole of Table1 but with the column order you want (assuming that you have the CHOOSECOLS function)

=CHOOSECOLS(Table1[#All],1,3,2,4)

g'day Peter, i don't know that i have choosecols. will look it up. cheers.

 

[ajm]

yes. do have it. another question. If i needed to insert a new column, a blank column which doesn't exist in Table1, into Table 2 for future use, would i use ChooseCols for the first few columns, say 5 (A- E),

=CHOOSECOLS(Table1[#All],3,7,1,9,5)

then have the blank column, col F, then use choosecols again, but this time for the other columns required from Table 1?

in col G

=CHOOSECOLS(Table1[#All],6,12,4,8)

or something like that? i tried using a space ,, in the sequence of column numbers but that didn't work.

 

[Alex Blakenburg]

I am not sure how well doing it this way will work for you but do you have HSTACK yet ?
If you do then this might do what you are asking

=IFERROR(HSTACK(CHOOSECOLS(Table1[#All],3,7,1,9,5),"",CHOOSECOLS(Table1[#All],6,12,4,8)),"")

I suspect you might be better off using a macro to populate or extend Table2,

 

[ajm]

I am not sure how well doing it this way will work for you but do you have HSTACK yet ?
If you do then this might do what you are asking

=IFERROR(HSTACK(CHOOSECOLS(Table1[#All],3,7,1,9,5),"",CHOOSECOLS(Table1[#All],6,12,4,8)),"")

I suspect you might be better off using a macro to populate or extend Table2,

****! two new functions in one day. Just discovered that I do indeed have HSTACK. Thanks Alex. have popped it in and it looks good to go. while it won't (or, more likely, i can't figure out how to) wrap a table around the output of this new beast, i can, for what i need after this, achieve the same result using a named range.

thank you both.

 

[Alex Blakenburg]

You are welcome. Glad we could help.

 

[Peter_SSs]

it looks good to go.

If i needed to insert a new column, .... into Table 2 for future use,

Just checking that it is actually what you want? That is, why do you want an empty column? If it is so that you can later enter data into it, as suggested by your "for future use" words, then the suggestion will not work since as soon as you later enter any data into that blank column, the original formula will return a #SPILL! error.

 

[Alex Blakenburg]

Just checking that it is actually what you want? That is, why do you want an empty column? If it is so that you can later enter data into it, as suggested by your "for future use" words, then the suggestion will not work since as soon as you later enter any data into that blank column, the original formula will return a #SPILL! error.

I am trying to find out whether this thread relates to the one below and whether they are mutually exclusive or not.
copy first three columns of table then rest of it

 

[ajm]

Just checking that it is actually what you want? That is, why do you want an empty column? If it is so that you can later enter data into it, as suggested by your "for future use" words, then the suggestion will not work since as soon as you later enter any data into that blank column, the original formula will return a #SPILL! error.

Peter, that is what i was working towards. this second sheet to which i am copying the data, is one of four, each with a different layout that the user can't change, that gets copied to a final combined sheet. this particular data set has all but one of the necessary columns already which is why i am inserting the blank column.