Tricky error with updating an external data source, and 'data - sort - copy output to'

[newbie-02]

hello all,

i have built me a sheet to download and evaluate the corona data from JHU,

- part of the file with the error - click to download / open -

one special thing goes wrong:

if i update the data in the sheet (by allowing ‘update’ after opening the file or with ‘edit - links to external files - update’) - note the red marked cells JF1:JG2 before, the references run ‘smoothly through’ to column JZ and more columns are downloaded for the sheet ‘cases_raw’, then the columns on the sheet ‘cases_sort’ change from JG on, and the formulas reference other cells, e.g. ‘cases_sort.JG1’ changes from ‘=cases_raw.JG1’ to ‘=cases_raw.JK1’, this is not intended and messes up all downstream work …

the two sheets are connected in two different ways, in row 1 and 2 of ‘cases_sort’ there are hard coded references to cells in the first two rows of ‘cases_raw’, below that there is an area which was filled with data by selecting ‘data_1’ on sheet cases_raw and then doing ‘data - sort - criteria col_b ascending, col_a ascending - options - copy output to cases_sort.A3’,

somehow the new download now has an effect on ‘cases_sort’ that i did not intend and do not understand … may this be due to some cryptic options? or is it a bug?

tia fah ‘thanks in advance’ - ‘for any help’

reg.

b.

[erAck]

My assumption is that at some point in time 5 columns were inserted on sheet cases_raw before column JG so the references on sheet cases_sort were adjusted. If you want the references to always point to some fixed but relative position without being adjusted upon insert/delete/move then use a named range expression, i.e. on cell cases_sort.A1 define a name headercell as $cases_raw.A1 (note relative column and row reference pointing to the same position on the other sheet) and use the name as cell formula. You can simply select cells A1:JZ2 and type in =headercell and close with Alt+Enter, which writes the input into all selected cells.

[newbie-02]

@erAck:
it works,

1. don’t expect me to understand how and why,
2. ‘at some point in time 5 columns were inserted on sheet cases_raw before column JG’ - you mean by the download process? that could match as - perhaps - the download routine tries to not to damage work done beside the old download area, and as a precaution inserts new space, then updating or not the references is wrong in the one or other case (they are used to access space beside the download or prepared to be used with later download … can’t say if i’d call it a bug,
3. how the heck should a ‘normal user’ get the idea to make special use of named cells, up to now i only understood assigning a name to a range … mysteries over mysteries …
   thanks for your help and profound knowledge …

[erAck]

'1. don’t expect me to understand how and why

Relative cell references in named expressions are not and can not be adjusted because they are expected to always be relative to the cell where they are used; it’s a property of named expressions.

'2. […]

As I don’t know what you did there I can’t tell. If columns were inserted then adjusting cell references is correct and not a bug.

'3. how the heck should a ‘normal user’ get the idea

By gaining expertise. Spreadsheets are not easy, only everyone is assuming that (and hence failing).

[ddcpi]

Hi and thanks to newbie-02 for having redirecting me to his thread. I have the same issue, columns changing row by theirselves after refreshing the htlm external link sheets. I tried erAck suggestion but didn’t worked for me, some columns letters have still changed row from the inital lettter i gave them. Don’t know how to solve this issue as it’s really annoying…

Note : When define a name for cells i can’t add any character like $, it says it’s not allowed. So i added a name without the absolute symbol, could this have an effect ?

[newbie-02]

with @erAck’s solution you shouldn’t have column-letters or row-numbers anymore, but a ‘named range expression’ - in his sample ‘headercell’ …
it was … not easy to understand … i just followed his script and it worked …
either try that, preferably first for another sheet, e.g. the example from this thread, and then transfer it to your task,
or provide your sheet, preferably remove private data first, perhaps someone will fix it for you … or get’s a better understanding of your problem …

[ddcpi]

Hello, yes i did like erAck suggest, for example i have =NB(Sheet2_D) for second sheet column D90:D150.

I saw the change when ctrl F3, you can see all the named cells and their assigned range and from there i notice some have changed again.

[erAck]

What (defined to what) named range changed how, and when doing what?

[ddcpi]

Hello erAck, to explain it a little better i have many sheets with html external data. Those sheet contains only html data, no cells with formula or whatever and they all have various refresh time set.I have one main sheet that contains all the formulas and collect all the data from the html data sheets.

So for example in one of my html data sheet ( the 2nd sheet ) and for the range $Sheet2.$D$90:$D$150 i defined a name Sheet2_D

In my main sheet i then have a formula like this : =COUNT(Sheet2_D)

Now the data i import contain some tables, sometimes the site doesn’t have the table available and the cells are empty and sometimes later the tables are available again and data reappear on the cells if refreshed.

What i noticed is when it’s refreshing and tables aren’t available anymore it’s when the letter i assigned “D” change to “I”. So when i CTRL F3 and check the cell name list it goes from $Sheet2.$D$90:$D$150 to $Sheet2.$I$90:$I$150

[erAck]

All reference points in $Sheet2.$D$90:$D$150 are absolute references by having a $ prefix. Absolute references are adjusted upon move/insert/delete in all formula expressions. In a named expression use relative references by not specifying the $ in that dimension, but note that defining (and displaying) the expression in the Name Manager is always relative to the current cell cursor position where the Name Manager was invoked.

For a relative reference define $Sheet2.D90:D150 for both column-relative and row-relative, so when used in a name in a formula expression in the same cell where the Name Manager was invoked will always address the range D90:D150 on Sheet2. Using it in other cells will shift accordingly.

If the rows should be adjusted when inserting rows then define absolute row references but keep the column references relative, so $Sheet2.D$90:D$150. That name then can be used in a formula on any row, in column D for D$90:D$150, in E for E$90:E$150, …

[ddcpi]

Thanks for the clarifications erAck, i’ll try without the absolute symbol and report.