Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


Reference single value in multiple tabs with a dynamic TOC

  • Matthew Reeves
  • Topic Author
  • Offline
  • New Member
  • Nový člen
4 months 2 weeks ago #1396 by Matthew Reeves
Hello everyone,

I have a staff list of ~150. Each staff has a single tab within a workbook for tracking certain information. A macro creates a TOC for me that makes referencing individual tables straightforward.

What I need to add is a way to pull the value that populates in cell A3 of each worksheet (~150) into a single list. I would like to do this in a way that when staff are removed or added to the list, that the tracking list automatically updates.

I created a separate worksheet that I can automatically pull current staff and sequence from the TOC to keep my tracking list updated ( ='Table of Contents'!C5 ) - but is there a formula that will move with staff when they move up and down on the list that always will reference their tab?

I was hoping I could pull staff name from the TOC to cell A1 of the tracking sheet, then in Cell B1 reference the name that pulled into A1 as the tab name. ( ='Staff Example'!A1 ) That way as staff data changed the formula would still pull the correct information. Obviously this didn't get me very far. I hope this makes sense. :)

Please Log in or Create an account to join the conversation.

  • Not Allowed: to create new topic.
  • Not Allowed: to reply.
  • Not Allowed: to edit your message.
Moderators: jaychivoQiu hongkunJay Chivo