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.

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

Steve Smith 4658

Me again Georgie.

I still can't get my head around the formlas. I have used the formula you provided and all I get back is IdexErro (or something like that).

I have tried doing is using the smartsheet help process i.e. the "Reference another sheet" option and keep getting back incorrect augment set.

Neither one means anything to me and as such I have no idea how to fix it (or where to start to fix it).

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

into the sheet and got nothing. I changed the "{Last Cleaned Date}" to "{Date Last Cleaned}" as that is the actual name on the column.

I have made sure all the column are dates as you mentioned.

I'm just stuck to be honest.

Sorry to keep coming back to you but the formula thing real does go right over my head.

Thanks yet again in adavance.

Steve Smith

Georgie

Hey @Steve Smith 4658,

It sounds like you haven’t been able to set up the cross-sheet references correctly. Follow the steps below to set up the first formula:

1. In your master sheet, click the cell in row 1 of the “Date Last Cleaned” column, then type in =INDEX and click the INDEX function that pops up to populate the cell with the opening bracket for the function, as seen below:
   
2. Next, click the Reference Another Sheet hyperlink seen in the screenshot above. As we’re in the “Date Last Cleaned” column of the master sheet, we want to reference the column in the Cleaning sheet that contains that date for each row. In the Reference Another Sheet window, locate your cleaning sheet, then click the header of the “Date Last Cleaned” column in the sheet to highlight the whole column, as seen below:
   
3. You’ll see that there’s a sheet reference name at the top - this will be populated automatically with a default name (the name of the sheet plus the number of the range) - since this is the first reference range you’ll be creating, it’ll be range 1, but you can rename this as desired to help you understand your formulas - this is the name that will be seen within the curly braces {} in your formula, so you may wish to call this “Date last cleaned”. Once you’ve chosen your sheet reference name, select Insert Reference.
4. Next, add a comma to the formula and then type MATCH and select the MATCH function to populate the opening bracket for the MATCH function. Then, click the cell in the Room Number cell in the same row (row 1), so that your formula populates with [Room Number]@row - my column is called Job Number, so mine looks like this:
   
5. Last, we want to tell the formula what to match the job/room number with, and that’s the job/room number in the cleaning sheet. So, add another comma to the formula and then click Reference Another Sheet again. Your cleaning sheet should come up as that’s the last sheet you referenced. This time, click the header of your Room Number column and update the sheet reference name as desired, then click Insert Reference. 
6. The last thing we need in an INDEX MATCH formula is the search type. In this case, we need a 0 for "not sorted", so that it finds the room/job number anywhere in the cleaning sheet. So, your final formula should look similar to below, but the range references will be named as you named them when you created them.
   
7. You can then either drag the formula down to the cells below, or you can right-click the formula and select Convert to Column Formula. Once you have values in the Job/Room number in the Master sheet, the formula will then automatically populate the last cleaned dates. 
8. You can then set up similar formulas in your other columns in your master sheet. So, for the Last Maintenance date, you’d take all steps above, except your two cross-sheet references will need to use the date column and the room number column on your maintenance sheet rather than your cleaning sheet. 

Hope that helps - let me know if you have any more trouble with this - if you do, please provide screenshots showing the steps you’re taking, the cross-sheet references in your formulas, and the error messages you’re seeing.

Thanks!

Georgie

Humashankar

Well-explained!

Steve Smith 4658

Hi Georgie

Thank you for this, I have managed to get the 1st one to work.

I have tried the same process for the other two heading but if keeps glitching out. When I get the selecting the Colum with the room number and press reference another sheet it seem to delete out what I'd done before hand and takes me back to Number 2.

I will keep plugging away at it but thanks so much for the effort you have put in to help me. It really has been appreciated.

Steve Smith