Inserting Cells loses Formula

[Jennsy]

Hi,

I have a problem when I insert cells into a worksheet, in that there is another sheet that looks up data from this sheet, that then ignores (skips) the inserted data.

I have attached two xlsx documents to show this.

Excel-Before-Insert.xlsx is the original before I insert any rows. The Summary sheet calls data from the Raw Data sheet and displays it. I have used a simple formula in the Summary sheet and if you click down the cells from A2 you will see the formula gets the data from RawDataA2, RawDataA3, RawDataA4 etc.

Excel-Aftere-Insert.xlsx is the same sheet, but I have inserted a blank row in RawData row 3. I want this blank row to show in the Summary but it doesn't. If you click down the cells from A2 in the Summary you will see the formula gets the data from RawDataA2, RawDataA4, RawDataA5, so it misses RawDataA3 - as that is the inserted row.

Is there anyway to stop this happening, so my Summary in Excel-Aftere-Insert.xlsx would look like this with a blank row for the inserted cell.

Client Revenue
Coke £10,000.00

Pepsi £2,000.00
7 Up £4,000.00

NB - this is just a very small set of example data, the real xlsx I have the problem is has the Summary sheet as it ignores some columns and also has graphs.

Thanks
Jennsy

[Ace_XL]

Before inserting the row, group the two sheets together then insert the row

[Jennsy]

Hi Ace_XL.

Thanks for the reply. I have grouped the sheets together and inserted a row in RawData, and that now does also insert a row in Summary. The only problem is row 3 in Summary doesn't have the formula in the cells, the cells are just completely blank. I have attached my xlsx so you can see. Is there anyway to force the inserted rows in Summary to have the formula in the cells as well?

Thanks,
Jennsy

[Ace_XL]

Perhaps, convert your data into a table on the summary sheet

http://office.microsoft.com/en-us/ex...010359200.aspx

[TMS]

In cell A2, use the formula:

=IF(INDEX('Raw Data'!A:A,ROW())="","",INDEX('Raw Data'!A:A,ROW()))

Not this (original formula) =IF(ISBLANK('Raw Data'!A2),"",'Raw Data'!A2)

Copy down and across.


Regards, TMS

Edit: change the formula to what I thought I had posted ...

[Jennsy]

Thanks, that is working now.

I have another question though, as I will ultimately have a number of summary sheets and raw data sheets that are linked together on a 1 to 1 basis. Is it possible to group (link) sheets together and not just do it as a whole?

For e.g. in the attached xlsx, I would want a new inserted row in RawData 2 to only show in Summary 2, and a new instered row in RawData to only show in Summary?

Thanks,
Jennsy

[Jennsy]

I should also add that the data I am putting in the RawData sheets is being added dynamically, so I don't want to have to add each data set manually or set the sheet groups manually each time. Hope that makes sense!

[TMS]

I'm sorry, just looked back at my post and it should have been this in A2:

=IF(INDEX('Raw Data'!A:A,ROW())="","",INDEX('Raw Data'!A:A,ROW()))

Looks like I just copied your formula back ... dohhh!

Hope that makes sense!

No, not really. I don't understand what it is you are trying to do. If you are linking one sheet to another, row by row and cell by cell, that doesn't look much like a summary to me. And then having several different versions of it ...

I'm sure that what you want could be achieved using INDIRECT and picking up the sheet index from somewhere. But, in all honesty, I think you'd end up with a very complex and unwieldy formula.

I don't think I can offer a solution on this.

Regards, TMS

[Jennsy]

Hi TMS,

The =IF(INDEX('Raw Data'!A:A,ROW())="","",INDEX('Raw Data'!A:A,ROW())) formula looks to be working for what I need!

Thanks very much
Jennsy

[TMS]

You're welcome. Thanks for the rep.