Referencing Multiple Sheets to Access Real Time Information

Seth Clark

Hello,

I run a certification company and I use SmartSheets to assist in license management. Currently, I track purchased, used and available licenses for 50+ companies in one sheet. Think inventory management for multiple companies. For each company, I also have individual sheets where I enter their unique certification information. I use this to send reminders and generate pdf certificates. Think local inventory management and POs. Here is my question: If company A purchases 50 licenses as indicated in my licenses management sheet, is there a way for me to reference this information and identify used and available licenses as indicated within their local sheet? Essentially, if someone wants to enter a new record, I want to know the amount of licenses they have, the number they have remaining and the specific license numbers available (e.g., MCPP_1001). Combining sheets would not be an option as the amount of information is to large for 1 sheet to contain. I also have a new LMS I am transferring to, but I need a solution for the next 6 months.

Andrée Starå

Hi @Seth Clark

I hope you're well and safe!

Yes, you could use cross-sheet formulas to gather and show the data as you need.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Seth Clark

Good morning Andrée- Could you provide an example of what you mean?

Andrée Starå

@Seth Clark

How many local sheets do you have?

Can you maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Brian_Richardson

Probably the easiest thing would be to leverage sheet summary fields and a summary report.

In each license tracker sheet, click on the right side to open the Summary tab. Add three fields, "Company Name" and "Licenses Purchased" and "Licenses Used" (for example, do whatever metrics you want :-) ).

In the Company Name field, fill out the company name for that tracker sheet

In the Licenses Purchased / Licenses Used summary fields, enter formulas to sum up the licenses on that sheet. I assume you probably have a column somewhere with the license counts, so a simple =SUM([Purchased Licenses]:[Purchased Licenses]) or similar, using the column names that have the info you want to add up.

Now, create a new Report and make it a Summary Report type when asked. Point the report at your tracking sheets and add the Company Name, Licenses Purchased, and Licenses Used fields. You'll now have a running list with the totals by company in your report. You can chart that data, export it, use it elsewhere etc.

Note that this solution does not populate a central tracker sheet, it provides the counts in a report that's easy to setup and leverage. If you want to have formulas in a central tracker do the same, you'll need to construct a formula for each company that points to the tracking sheet for that company. Like =SUM({company a tracking sheet license count column}). You'll need to add references for each new company you add. See the Cross-Sheet References learning for more info on that: https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets