Looking for cross sheet reference help

Karla Boyle

Hi,

I am likely making this harder on my self than need be.

My source sheet has data on it which I need, but my coordinator does not need.

I am looking for a formula to copy select columns from Source (Top Pic) to Coordinator Sheet (Bottom Pic) based on the value in the "Wave" column from the Source.

In this example, I do not want to include Wave OOS.

Also, it is important that if data is missing in Source, it reflects that accurately in the Coordinator Sheet.

I am very well versed in Excel, but this is my first time using SmartSheet. Please explain logic so I can learn rather than be a copy monkey

Thanks

Karla

Paul Newcome

How is the Wave column in the first screenshot affecting the data in the second? It looks like maybe PC Name is unique per row, so we can leverage that to pull the data. I am just not sure where the Wave comes into play.

Karla Boyle

The Coordinator will not need any row from Source labelled "OOS" - However I will need those to remain in the source.

Another option would be to filter out OOS on the Coordinator sheet. I would like this done in the back ground though. My coordinator is a great person, but tech challenged

Paul Newcome

What exactly is your coordinator doing with this data? Are they running further metrics, or just editing/viewing the data itself?

Karla Boyle

Big picture.

We are replacing 1000 computers and 2000 mobile devices. My coordinator will be viewing the data, emailing the person, updating status, and adding notes.

I will be reporting on the project progress and providing metrics.

So two grid views and a dashboard are what I am looking at initially. I may end up moving my project plan book into SmartSheet (order tracking, financials, risk management, etc)

Yes, PC Name is the Unique Identifer

Paul Newcome

I am going to suggest a report for your coordinator.

Then for your metrics sheet, you can include the range/criteria set of the Wave column not equaling "OOS" in your different formulas (COUNTIFS/AVG/etc...).

Speaking of metrics... If you are going to reference that Wave column in any formula(s), I would suggest inserting a helper column on the source sheet that has the column formulas of:

=Wave@row + ""

This will convert everything to text values so that all data within the column is consistent (instead of some text and some numerical). Inconsistent data types within a range can cause some issues with metrics. You can then reference this new column in your metrics formulas and choose one to hide to keep the sheet looking a little less crowded.