REF problem

[ceruin]

Hi, I have a summary sheet which contains summary of sheet1 and sheet2.

Is there a way if say certain rows deleted / added / modified in either sheet1 or sheet2 then this get automatically reflected in the summary sheet?

Thanks!

 

[GlennUK]

How about using INDIRECT to link to locations in those sheets? The links won't be affected by insertion and deletion in that case.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[macropod]

Hi ceruin,

In A2 of your Summary sheet, insert:
=IF(OFFSET(DVD!A$2,ROW()-2,)="","",OFFSET(DVD!A$2,ROW()-2,))
and copy across & down to B10.
In A11 of your Summary sheet, insert:
=IF(OFFSET(VCD!A$2,ROW()-11,)="","",OFFSET(VCD!A$2,ROW()-11,))
and copy across & down to B20.

Cheers

[MS MVP - Word]

 

[ceruin]

Hi GlennUK, thanks for the idea, I looked at some indirect samples for using indirect, but I do not know how to use it to solve my problem.

Hi macropod, thanks for your solution, this is awesome! close to what I need. Do you know how to make it so when you insert/delete row the summary sheet also insert/delete row?

 

[macropod]

Hi ceruin,

You can't add/delete rows with formulae. Even so, with a bit of effort it should be possible to come up with a formula that automatically starts the VCD rows after the last DVD row. Conditional formatting could be used to adjust the cell formats in concert with this.

Give it a try!

Cheers

[MS MVP - Word]

 

[GlennUK]

Hi GlennUK, thanks for the idea, I looked at some indirect samples for using indirect, but I do not know how to use it to solve my problem.

Unfortunately I can't view your link here at work, but if I get a chance tonight I'll have a look and see if I can do an example for you.

Cheers, Glenn.

Beauty is in the eye of the beerholder.