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?

Find more posts tagged with

Construction

Formulas

Reports

Project Management

Cell linking

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Logging separate data points of the same type of data for one row
Looking for a way to easily log and reference multiple data points for the same type of data (ex: successful phone contacts) that all show for one entry (by member name). Want this to be both easy to enter the information (ideally by interacting with the same row via Dynamic View), and easy to see data on the back end of…
Help With If Count If to Identify Unique Values in a Column
I'm trying to make a helper column to identify if a new/unique project code has been entered in a column so that I know when a new project has been created. I've been trying to set up this formula and plan to then set up an automation to be notified when "New Project Code" has been entered into a cell. It seems to be ok on…
Why does the parent task my % column show as a decimal value instead of a percentage?
Why does the parent task my % column show as a decimal value instead of a percentage? I checked the column and project settings, and they are identical to another smartsheet in which the values show up as a percentage.