Hello,
First, I want to explain the context, maybe to help to understand my problem.
I have a xlsx file, updated automatically by a script, or something like that. (it's not my work to take care of this file).
But I need to import theses data into another xlsx file, and use this data.
So, my idea was to use Power Query, instead of a old macro.
Everything is working fine, expect when the file is updated and new line are added.
For example, with the data source file of yesterday, I have 1475 Entry, saved into the sheet 2.
In the sheet 1, I simply use formula to import data, for example :
A1475 = =IF(sheet2!A1475<>"";sheet2!A1475;"")
A1476 = =IF(sheet2!A1476<>"";sheet2!A1476;"")
A1477 = =IF(sheet2!A1477<>"";sheet2!A1477;"")
(1476 and 1477 doesnt exist in the source file, but I prepare the file in advance for 10'000 lines)
...
but when I do a refresh all, the behavior then escapes me completely!
I have 11 new entry in the source file, and my sheet 2 contains now 11 new lines (1486 lines)
BUT
A1475 = =IF(sheet2!A1475<>"";sheet2!A1475;"")
A1476 = =IF(sheet2!A1486<>"";sheet2!A1486;"")
A1477 = =IF(sheet2!A1487<>"";sheet2!A1487;"")
Why excel do that ?
that's not at all what's expected. Or yes ?
Entry 1477, 1478, etc. are are therefore ignored
I haven't pushed the tests to the end yet, but I think that if a line is removed from my source file, it will set everything off.
What's the best approach to protect against that?
I have Data from my source file, to which I simply have to add columns to complete the data manually or with formulas.
On paper it looks so simple, but in practice, really painful
Thanks and regards,
H.
