Hi all, I am a new member and a beginner with excel. First i want to say... I had the hardest time registering on this forum, the confirmation images were way to hard to see (must have tried a dozen times)
Anyways, my question is about referencing a sourced worksheet.
I have a large workbook with over a million cells that is constantly updating (source). I have a second workbook with two sheets, Sheet"A" and sheet "B". In sheet A i reference 'Source" with ='[source.xlsx]1'!$A$1000:$BCD$1000, so sheet A and book "source" is exactly the same. In sheetB, i do calculations with A, for example =sum('A'!a10:'A'!a12).
Everything works fine, except when the source file is changed. If I were to insert a column before column A from sheet A, Sheet A would shift to the right (column A becomes column B) which makes perfect sense. But my calculation in sheet B is still referencing column A =sum('A'!a10:'A'!a12). which is not what i want.
Is there anyway to make it so that sheet B is relative to sheet A which is relative to the source? i tried defining a name for column A in sheet A but even then, the name stays in the first column while column A shifts to the right.
Thanks in advance,
Anyways, my question is about referencing a sourced worksheet.
I have a large workbook with over a million cells that is constantly updating (source). I have a second workbook with two sheets, Sheet"A" and sheet "B". In sheet A i reference 'Source" with ='[source.xlsx]1'!$A$1000:$BCD$1000, so sheet A and book "source" is exactly the same. In sheetB, i do calculations with A, for example =sum('A'!a10:'A'!a12).
Everything works fine, except when the source file is changed. If I were to insert a column before column A from sheet A, Sheet A would shift to the right (column A becomes column B) which makes perfect sense. But my calculation in sheet B is still referencing column A =sum('A'!a10:'A'!a12). which is not what i want.
Is there anyway to make it so that sheet B is relative to sheet A which is relative to the source? i tried defining a name for column A in sheet A but even then, the name stays in the first column while column A shifts to the right.
Thanks in advance,
Last edited:
