Use of INDIRECT (I think!)

[gromitnz]

Hi

I have a workbook with multiple sheets, one for each employee, then a summary sheet - for regular staff catch-ups.

On my summary sheet, it references cells in each of the staff member's pages.

The problem I have is the summary sheet doesn't contain row for row all of the same topics - (hence summary) - so when I try to drag to fill down more rows (or copy/paste) for future catchups, the references are all bung.
I have attached a sample sheet, as it's a lot easier to see than explain.
The actual doc has a lot more employee sheets, and there is no way I can re-reference each cell manually - will be very prone to error over time.

I know it's along the lines of using INDIRECT, and the row number on the employee sheet somehow, but can't sort it.


To try my best to describe it, with just one as an example (I can figure out the rest - have highlighted relevant cells yellow in the doc), here's the problem in some words:


Sheet 'Tom' has the date of the 1st round of catchups on cell B15, and the 2nd catchup on B26, and the 3rd in B37.

Sheet 'Summary' has a cell with the refence to that 1st date on C8, then the 2nd date on cell C14.
Currently in cell C8 I just have =Tom!B15. (good enough)
My attempt in cell C14 is =INDIRECT("'Tom'!B" & ROW(Tom!B15)+11)
This works for the 2nd catchup slot fine.
When dragging down to populate a 3rd catchup slot however, instead of correctly pointing to cell B37 - it points to cell B32.

Hope this properly describes the issue.

Any help much appreciated!
11 for test.xlsx

[TMS]

You could do it this way:

Please Login or Register  to view this content.

[TMS]

Or you can use INDIRECT for the sheet (employee) name:

Please Login or Register  to view this content.

[TMS]

This, for all of Tom's Summary data:

Please Login or Register  to view this content.

[gromitnz]

This, for all of Tom's Summary data:

Please Login or Register  to view this content.

Holy cow you guys are wizards!!!

I'm going to use the second one you gave, purely because maintenance for a novice like me will be easier long term:

Please Login or Register  to view this content.

Works brilliantly, thanks! I love the versatility of grabbing the sheet name using INDIRECT, for future employees.

One minor thing - one of my employees names (and therefore the sheet name) has a ' in it, which I think is messing up the formula (e.g O'Donnell).
How would I go about fixing that?

[HansDouwe]

Another option, empty the whole table and try in C4 and copy to right:

Please Login or Register  to view this content.

[gromitnz]

Thank you - also looks great - although I see a problem with this when more and more 'Rounds' (future catch-ups) are added - we are going to be doing them once a month, so will start to add up!

[TMS]

You're welcome.



If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[gromitnz]

Sorry TMS - are you able to help with this?

One minor thing - one of my employees names (and therefore the sheet name) has a ' in it, which I think is messing up the formula (e.g O'Donnell).
How would I go about fixing that?

This is for:

Please Login or Register  to view this content.

Will def mark as solved if you have any thoughts around this, and happy to hand out stars!!

[TMS]

Not off the top of my head. I tried replacing the "'" with a space ( "'" ). But the problem with that is the cell value and the sheet name all have to tie up.

[TMS]

Thanks for the rep.

[gromitnz]

While playing between replies, the unmentionable (shhh) came up with this modification which seems to work:

Please Login or Register  to view this content.

Will mark solved, thanks so very much!!!

[HansDouwe]

Thanks for the feedback and rep . Glad to have helped.