locked
Power Query import and formulas RRS feed

  • Question

  • 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.


    • Edited by hurikan Wednesday, May 6, 2020 9:13 AM
    Wednesday, May 6, 2020 9:11 AM

Answers