How to Automatically Populate Summary Data for Multiple Jobs Across Sheets?

System

This discussion was created from comments split from: Assistance pulling information from one sheet to another automatically.

Steve Smith 4658

Hi, I'm hoping someone can help as I have a similar problems.

The problem I have is:

I column A of all my sheet (there are 4) are all the same. They are the "Job Number"

In each sheet, there is a different task for each job number.

When I completed my first sheets that populated each one of the idividula task it populated each sheet with the information, then gave me a summary (which is where the porblem is) of key bits from each sheet which I want to see at a glance.

However, when I then followed the same process for the next job, it didn't moved the original job down (to line tow in the mast sheet) and didn't pull through any of the new information for Job 2.

If I sent new submission on the sheets as arriving at the bottom (instead of at the top as they are now) will it then work?

Thanks in advance

Steve Smith

Georgie

Hi @Steve Smith 4658,

It sounds like your sheets are populated by a form and that conditions for formula autofill are not being met. Formulas populate automatically when you type in a newly inserted or blank row when certain conditions are met (these can be found under “Conditions that trigger formula autofill” in this article: Automatic Format and Formula Autofill).

So, it might be that you need to type into the newly inserted rows for the formula to autofill. To avoid the need to do this, you could convert your formulas to column formulas so that they are always automatically applied to all rows including new rows. To do so, right-click on a cell containing a formula and select Convert to Column Formula. More on this here: Use column formulas to apply calculations to all rows in a sheet.

Hope that helps!

Georgie

Steve Smith 4658

Hi Georgie.

So sorry, I have only just saw your reply.

I'm very new to all this let alone doing formulas. I have a look at the articles and if I can't get it to work am I ok to come back to you for further advice?

Thanks in advance.

Steve Smith

Georgie

Hi @Steve Smith 4658,

Of course - just comment back on this thread if you need more help with this. If you do continue to have trouble with it, any screenshots you can provide of the sheet (with any sensitive data removed) will help me, as well as other members, to determine what's happening!

Thanks,

Georgie

Steve Smith 4658

Hi Georgie

I’m still not getting my head around what I need to do and even if it can be done at all.

I have made up a few sheets which in principle would need to do the same as what my actual Smartsheet (master copy) to do.

Basically this is what I am trying to achieve:

Master sheet.png

Cleaning.png

inspection.png

maintenance.png

Our business uses a unique reference numbers for a job (for the purposes of this fictional example, I have chosen room Numbers). This job reference will stay with the job for as long as it is here. There will be several repetitive actions made on each job and these action will be stored within the appropriate smart sheet.

Each update on a job (room Number) is completed by a third party and the info is populated by using a smart form.

The information populated by the smart form needs to then pull through as an overview on to a master sheet. In essence a snap shot of where we are with it or the last time anything was done under that specific heading. Just a date, the actual information can remain in the designated sheet.

So what I want to see in on the overview is the last time that action was made again that specific job.

I can’t seem to get my head around how to make it work when the Master sheet is static i.e each line won’t be moved up or down when a new form is completed like it does on the other sheets which pull through the info from the form.

I’m very sorry if this doesn’t make sense but I’m not familiar with using either excel or smart sheet so all the terminology etc is lost on me.

Thank you so much in advance for any help you can give me.

Steve Smith

Georgie

Hey @Steve Smith 4658,

To pull the dates from the three separate sheets into your master sheet, you can use Index(Match) formulas in each of the Date columns in the master sheet. You’ll first need to ensure that these columns (“Date Last Cleaned”, “Date Last Inspected”, and “Date the last maintenance took place”) are set up as Date columns in both the master sheet and the three sheets you’ll pull the dates from (double-click the column header to check or change the Column Type).

Index(Match) formulas require a unique ID that matches between the destination sheet (in your case, the master sheet) and the source sheet (the cleaning, inspection, or maintenance sheets). You already have this in the form of the room or job number.

To create the formulas, we’ll need to create cross-sheet references as we type the formulas. As an example, in the “Date Last Cleaned” column in the master sheet in your example, your formula would look similar to below:

• =INDEX({Last Cleaned Date}, MATCH([Room Number]@row, {Room Number | Cleaning}, 0))

The cross-sheet references are those in curly brackets {}. So, the {Last Cleaned Date} reference will be the entire “Date Last Cleaned” column in the “Cleaning” sheet, and the {Room Number | Cleaning} reference will be the entire “Room Number” column in the “Cleaning” sheet. You can name references as you wish - these are just examples. See the article I’ve linked above for more on creating references. This formula says: Return the value from the “Last Cleaned Date” column in the “Cleaning” sheet where the Room Number for the row in the “Cleaning” sheet matches the Room Number of this row. 

Once you’ve got the formula working, you can convert it to a column formula (by right-clicking the cell in which you created the formula and clicking Convert to Column Formula). You can then create similar formulas in your “Date Last Inspected” and “Date the last maintenance took place” columns in the master sheet, creating the relevant cross-sheet references each time so that they’re looking at the relevant sheet, and also convert those to column formulas. 

For more information, check out the following resources:

• Cross-sheet formulas
• Formula combinations for cross sheet references
• INDEX Function
• MATCH Function

Hope that helps - if you have any further questions, just ask!

Georgie

Steve Smith 4658

Hi Georgie

Thank you for your time and help.

I will give it a go, I suspect the formulas or going to go over my head but I'll give it a go.

Thanks again

Steve Smith