1

I have three different gantts with some summary data that is fed into the main project data sheet. Only one of the gantt sheets is used in a project, so I thought of linking each of the gantts' data (exact same field/cell in each sheet) to a cell in the project data sheet - then just grab the one that isn't blank or an error.

  • =IFERROR([Column X]@row, IFERROR([Column Y]@row, [Column Z]@row))

Unfortunately, the two cells that should not have data actually do have data and a "Link In from <unknown sheet>" (broken link/red arrow cell).

I am able to work around this, but was hoping to avoid another pull from one of the summary sheets with

  • =INDEX({Range1}MATCH([Column A]1,{Range2},0))

Comments

The "ghost" data you are seeing is the initial value from the linked in sheet.  The ones that are not used will not be blanked out or have an error (as you have figured out).

I suggest linking in another piece of summary data that is updated as part of project provisioning and key off that.

For example:  

- Each gantt may have a field called "Project Name" with a default value of "<project name>".

- Link that to your main project data sheet

- the ones that is actually present will have a project name filled in and the other two will have the red arrow and still have "<project name>"

- have your formula look for which column is NOT "<project name>"

Hope this helps.