With the recent expansion of formulas to cross reference other sheets I would love to be able to split projects to other sheets. If I could pull the Primary column and the Start Date column via formula I could have secondary sheets that have their own unique information.

The new help sheet here, Referencing Data from Other Sheets, says you can reference entire columns with your formulas.  However, when I try to draw direct references to another column through the range picker it says "#INVALID COLUMN VALUE"  I can individually draw the reference "=B1" when B1 is on another sheet.  However I cannot "=[Column1]:[Column1]" through the range picker?

Thanks for your thoughts.




You must sent up the reference. The syntax will end up being {Your Defined Name Here}

Something like this :

=COUNTIFS({Action Items Action_ID}, LEN(@cell) = 4, {Action Items Status}, [Project Status]9)


In reply to by J. Craig Williams

I see that you can use "function" formulas to summarize data from other sheets into a single new cell.  But this type of cross reference cannot link an entire column or be filled down to reference new cells?  It works for individual cells to say ={My Defined Range Here} However, if you fill down it simply repeats the same defined range.

I'm wanting a dynamic way to tie my primary column to another sheet (one way link is fine).  Cell Linking is too 1 to 1 where any newly inserted rows need to be manually linked. 

There needs to be something that tells the system what you are trying to link.

I can use x-sheet references to duplicate an entire sheet ... as long as there is something that tells it what the matching criteria is. In a relational database, that is a key field. If it is a one-to-one match, then you need something like a RowID that gets created on both sheets to be the link.

Very possible.