Cell range and Sheet Summary
I have an expense 'tracker'. Essentially it tracks if employees submitted their expenses
(1) correct the first time;
(2) expense was sent back for revision;
(3) submitted late
(4) did not submit
My columns across top are months of the year. My rows are employee names.
I have a sheet summary that totals (1), (2), (3) and (4). I would LOVE to have a field in the Summary that specifies the range (as each month the range changes) and then set up each of the 4 summary fields to reference the field where the range is. This way I only have to come in once a month and update the Summary field containing the range. However, I've not been able to figure this out.
Perhaps this isn't possible?
Thank you.
Best Answer
-
Unfortunately what you are wanting to accomplish isn't quite possible.
You could insert a bunch of helper columns (can be hidden after setting up) that have the totals in the top rows and then INDEX the entire group of hidden columns referencing a column number in a Summary field, but that isn't very flexible and requires you to keep those top few rows a the top.
Answers
-
What are the formulas you are currently using?
-
Hello @Paul Newcome
I have 'symbols' that correlate to the options. So basically the formula in the Sheet Summary fields are;
=COUNTIF([23 Jan]:[23 Jan], "🔵").
Next month the range will be [23 Feb]:[23 Feb] and following month will be [23 Mar]:[23 Mar]
Instead of editing the 4 Sheet Summary fields I would love to have 1 Sheet Summary field that I edit monthly and then the other Sheet Summary fields reference it.
Does that make sense?
-
Unfortunately what you are wanting to accomplish isn't quite possible.
You could insert a bunch of helper columns (can be hidden after setting up) that have the totals in the top rows and then INDEX the entire group of hidden columns referencing a column number in a Summary field, but that isn't very flexible and requires you to keep those top few rows a the top.
-
You gave me an idea! I don't necessarily need the data in the Summary Sheet section. I already have a cell at the top that I send out an automated update request to change the current monthly submission date. I can use this space that's already being partially used for 'count' rollups of the different categories. I think this will work well. I'll work on it later today.
Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!