Cross referencing same range across multiple sheets

Gary S

I am using one sheet, call it Program Data, as my source data for piping into my many Project Sheets. The Project Sheets have a Project ID in one cell that determines which line of the Program Data sheet to call out (e.g. Start Date, Team Lead 1, Team Lead 2, Team Lead 3, ...). I can't find a way to link each cell without manually defining the ranges for every cell. Is there a way to define an absolute range in a cross-reference that spans different sheets? Or simply a way to cross-reference a range or another entire sheet without having to click through the range every time?

In other words, if I define the range {Program Data Start Date} or {Program Data Project ID} while referencing from Project Sheet X, that range can successfully be referenced in Project Sheet X but {Program Data Start Date} or {Program Data Project ID} are not defined in Project Sheet Y. I would have to manually click 'Reference another sheet' and define the ranges for every cell in Project Sheet Y and all additional sheets I wanted to autopopulate from Program Data.

My formula is a simple Index Match and works just fine.

If not possible, then I would like to request this functionality, as it is a base functionality for any automation in my opinion.

Paul Newcome

It is not possible to transfer cross sheet references from one sheet to another already existing sheet. Feel free to submit an enhancement request using the "Product Ideas" tab at the top of this sheet.

If you have created these cross sheet references and then "save as new" the project sheet, the cross sheet references will copy over as well. It is just transferring them to an already existing sheet that isn't possible.

Andrée Starå

Hi @Gary S

I hope you're well and safe!

Not sure I follow!

You'd have to create each range for each sheet except if you create a master sheet and then copy it for each project.

Make sense?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

It is not possible to transfer cross sheet references from one sheet to another already existing sheet. Feel free to submit an enhancement request using the "Product Ideas" tab at the top of this sheet.

If you have created these cross sheet references and then "save as new" the project sheet, the cross sheet references will copy over as well. It is just transferring them to an already existing sheet that isn't possible.