Cross sheet formula/reference

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? 

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Yes, possible, depending on number of columns.

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

    Exact solution varies.

    Craig

  • 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! 

     

     

    Pic.png

  • J. Craig Williams
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!