Dynamic Indirect Cell Reference with Drag Fill

[naridox]

Good morning!

I'm pulling my hair out. For the life of me, I can't find anything online that clearly delineates how I can make a dynamic indirect cell reference that I can fill drag over an entire section.

CONTEXT
On a dashboard, I have a table that has member names in the first column and in subsequent columns refer to weekly stats. I'm attempting to indirectly refer to the member-named worksheets and click+drag across columns to fill an entire table without manually changing every cell reference.

(I'd prefer to know how to do across columns and rows, but I'll happily take columns for now!)

ATTACHED View attachment 170418 Tracker Education v3.xlsx
I attached the document in question. As I have only two member sheets complete, I'm focusing on Klein and Range. See "DASH" cell D28 and D29 for the problems in question.

Thanks for your help!

Naridox

 

[NBVC]

Does this work?

=INDIRECT("'"&$B28&"'!"&ADDRESS(14,COLUMN()))

 

[naridox]

It worked! Why?

 

[NBVC]

The COLUMN() function simply replaces your "4" with the current column number the formula is in.... so as long as your data in the other tabs are in the same relative columns, dragging this formula across changes the column index only...

 

[naridox]

Thank you!