Pulling data from one sheet into the sheet summary of another - is automation possible?

SL RN

Hello.

I am using Smartsheet to conduct and keep track of several surveys.

I have my master list, which has the survey name, sheet, form, and contacts/email addresses. Each survey is sent out automatically on a given date.

When I program my sheet summary, I want to know what percentage of people responded. Right now I have a formula for count of responses, and I need to manually reference the count of contacts in the original masterlist: =countm({Masterlist Range 1})

Then I use this formula to find the percentage: =[Number of Responses]#/[Count of Attendees]#

Is there a better way to do this? Is automation possible?

Genevieve P.

Hi @SL RN

You should be able to combine both of your formulas into one big formula, so you don't need the extra two cells.

For example:

=COUNT([Response]:[Response]) / COUNTM({Masterlist Range 1})

Does that make sense? Let me know if this helped or if I misunderstood your question! If so, it would be helpful to see a screen capture of your sheet, but please block out sensitive data.

Cheers,

Genevieve

SL RN

My question was really about getting the data from the other sheet automatically without needing to hand select the range

Genevieve P.

Hi @SL RN

Can you identify what {Masterlist Range 1} is referencing? You should be able to click on the Column Name which will select the entire column. Then as new people are added to your Masterlist they will automatically appear as a number in your formula.

SL RN

ce list.jpg

I'm using this MasterList to keep track of courses. Each course has a separate corresponding sheet and form for a feedback survey. I want the number of course attendees in my Sheet Summary report, so I've imported the =countm(masterlist range), which is the count of email addresses entered in the contact field. Right now I need to enter the formula manually for each course. I'd love for it to pull directly from the appropriate row.

Genevieve P.

Hi @SL RN

I think I understand, now! It sounds like you're looking for a way to ensure that the {range} is automatically pointing to the right column when you create a fresh, new sheet, is that right?

If so, how are you creating these new sheets? If you're using an active sheet then saving it as a duplicate with "Save as New", this is a setting you can toggle on:

SL RN

I've created a template that has all the correct formulas built in and I use that for new sheets. I have a link for the sheet and the corresponding form in the MasterList.