INDIRECT with Relative Cell References

I hope the title kind of explains what i'm trying to accomplish here, but basically I have a workbook with worksheet names Item 1, Item 2 -- all the way to 50.

I used indirect to link a summary page to the sheet names, so if we have to add more sheets (items) we can just drag and drop down, without having to manually type 'Item 51', etc.

=INDIRECT("'Item "&ROW()-9&"'!A5")

Is the command I'm using.

That part works, but the problem I'm having is that if someone adds a row above A5 on Item 1 (Worksheet) the A5 part doesn't dynamically update to A6 on the summary page. I'm assuming because it's in the Indirect(). So it essentially breaks the reference on the summary sheet.

So my question is, is there a way to fix this within Indirect? Or am I SOL on using indirect for this purpose?

Originally Posted by mcmahobt

Which row does your INDIRECT formula start being used?

The indirect begins on Line 10 on the summary page. So the first indirect ends up being evaluated as =Indirect("'Item 1'!A5") which correctly links to A5 on the Item 1 worksheet. The problem is, if someone adds a row to the Item 1 worksheet above A5, the indirect stays the same(at A5), instead of changing to A6.

Originally Posted by mcmahobt

Try this:

Please Login or Register  to view this content.

On the summary page that works for the first item. It correctly displays A5 on worksheet Item 1. But if I drag that down to Item 2's summary, it adds the row of the summary page, making the next entry Item 2, A6. It needs to be A5 still. The only time I need it to change to A6, is if someone adds a row above A5 on the Item 'X' worksheets.

https://drive.google.com/folderview?...E0&usp=sharing

The picture called "Indirect Location" is on a worksheet called Item 1. The picture called Indirect Reference is on the summary Page. So basically I want the summary page to show A5 from the Item 1 worksheet. But if someone adds a row above A5, I want it to update itself to A6. I'm probably explaining this very confusingly, because I don't really know the excel terminology -- if you want I can post the workbook.

Example Worksheet.xlsx

OK, I attached a sample.

So basically at the bottom I have 3, Item Worksheets and a Summary page. On the summary page, I want to be able to drag those down as pages get added, without having to manually enter each worksheet name. I did this by using indirect and &row. So as I drag down, it automatically increments the Item 'X' part. That works fine. You can test it by just dragging the Item 2 row in the summary page down and Item 3 worksheet gets populated.

The problem I have though is the reference to the cell. In the description for example, it's referencing cell A5 on the various worksheets. But if you go into Worksheet 1 and add a row at say A3. Then The "Testing" part gets moved down to A6. But on the summary page, it still points to A5 and doesn't automatically change to A6.