CODEWITHSUNDEEP
excelexcel-formulaexcel-2013excel-2016
MmVv
MmVv

Reputation: 553

Proper referencing from one sheet to another, without #ref

So, I got 2 sheets:

1st Sheet is Main sheet one with the data where I will periodically update (edit, add or delete) data, got like 800 rows.

2nd Sheet is actually lookup sheet where I have same data as are on Sheet1 but with some additinally calculations, like broaden sheet.

I wanna have it properly updated, in a sense of:

I tried:

=OFFSET(SummaryAll!A2;0;0)

referncing: =SummaryAll!A2

INDEX/MATCH combo

or something like this: =INDIRECT(ADDRESS(Row(Helper!C2),Column(Helper!C2),,,"txt"))

Well, I dont know actually, referencing itself is not a problem, but how I am avoiding error #ref and is there a way to dynamically move my rows when one is deleted from the main sheet?!

Thank you!

Upvotes: 0

Views: 48

Answers (1)

MmVv
MmVv

Reputation: 553

Thanks for all comments, lets sum it:

THX to @JvdV and @JustynaMK for actually simple as a day suggest and solution:

So it is possible to use (at least two approaches):

=INDEX(SummaryAll!A:A;ROW())

or one of my ways but requires a bit more refreshes with functions 

=@SummaryAll!A3:INDEX(SummaryAll!A:A;COUNTA(SummaryAll!A:A))

And lastly (in my case works also) using Power Query, after adding new data just hit refresh.

Upvotes: 1

Related Questions