Cell range and Sheet Summary

PeggyLang

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.

Paul Newcome

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.

Paul Newcome

What are the formulas you are currently using?

PeggyLang

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?

Paul Newcome

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.

PeggyLang

@Paul Newcome

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!