Cross sheet formula/reference

tye.gardner35371

Hi everyone, 

This may be a basic answer, but I am trying to build a dashboard for my company. I have 6 reference sheets that I want to filter into 1 large sheet (the dashboard). The dashboard is an accumulation of weekly data that the 6 sheets feed into. Each sheet has a different employee inputting different data. 

So, If an employee inputs data into week 1 (reference sheet), it will populate into week 1 on the dashboard. Yes, I can do that by cell linking, but there are many data points (52 weeks worth). I also don't have the time to link each individual cell. Is there a way I can have whole rows reference/populate on the dashboard? So, as each week goes by and they input data, it will automatically populate on the dashboard? 

I know reporting can do similar functions, but they are limited in how editable they are (at least I think) Any suggestions? 

J. Craig Williams

Yes, possible, depending on number of columns.

I use INDEX( .. , MATCH()) functionality. 

Exact solution varies.

Craig

tye.gardner35371

Thanks for the response, Craig

I do not believe index/match will do it- maybe I am wrong! However, I have a pic for an example. The part you see is my "Roll-up" section of the dashboard. The left hand column is the criteria to be accumulated weekly, rolling left to right. So, lets say I create a cell reference from 3 different sources to get the "Quote $" of "150". I know in Excel, if I would create that connection, I could just slide that formula/reference to the right, and it would pick up the proper cells and sum them correctly. Smartsheet just copies the same cell reference and pastes the same value. I want that data to roll with the weeks so I can have just the "roll up" section essentially populate freely. 

I just wish I could build simple sum formulas within reports- it would make a roll- up alot easier. Does that make sense? Any further suggestions or is the index/match function it? 

 

Thanks! 

 

 

J. Craig Williams

If not INDEX(..,MATCH()), the X-Sheet References.

Are the [Week 1]2,  [Week 2]2, [Week 3]2, etc.. cell formulas looking at the same (6?) sheets?

This does not look like it would take long. A few hours or so and that sheet would be finished. Difficult to do without all the details, but I don't see anything that seems like it can't be done.

Sorry I can't be more specific.

Craig