indirect formula across multiple sheets

[nfs001]

Hello

I have a basic understanding of spreadsheets and formulas but am out of my depth and need your help and advice!

I have a workbook that has multiple spreadsheets. The first is a summary sheet, the others are for each year. I would like the summary sheet to show the number of times a type of job is won per month.

At the moment when the job is won, on the Year Worksheet (e.g. 2017) I put the month and year it was won in Column A (e.g. 1.18 for January 2018, 11.17 for November 2017, 9.16 for September 2016 etc) and the type of job in Column M.

A job might be listed on one worksheet (e.g. 2017) but it might be won in January 2018 meaning Column A will show as 1.18.

I hope this makes sense, and I appreciate any help you can give me.

ThanksWORKSHEET 'SUMMARY'[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Month Won[/TD]
[TD="align: center"]Month Won[/TD]
[TD="align: center"]Month Won[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Job Type[/TD]
[TD="align: center"]6.16[/TD]
[TD="align: center"]4.17[/TD]
[TD="align: center"]1.18[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Washing[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Ironing[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Cleaning[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]
WORKSHEET '2018'[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]4.17[/TD]
[TD="align: center"]Washing[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]6.16[/TD]
[TD="align: center"]Ironing[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]1.18[/TD]
[TD="align: center"]Cleaning[/TD]
[/TR]
</tbody>[/TABLE]
WORKSHEET '2017'[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]6.16[/TD]
[TD="align: center"]Washing[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]4.17[/TD]
[TD="align: center"]Ironing[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]1.18[/TD]
[TD="align: center"]Cleaning[/TD]
[/TR]
</tbody>[/TABLE]
WORKSHEET '2016'[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]4.17[/TD]
[TD="align: center"]Washing[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]1.18[/TD]
[TD="align: center"]Ironing[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]4.17[/TD]
[TD="align: center"]Cleaning[/TD]
[/TR]
</tbody>[/TABLE]

​

 

[Special-K99]

This looks like it may be a job for VBA otherwise it would be a very complicated formula.

You've referenced 3 years but have only listed the months that where jobs have been won.
In essence there are 36 months across that 3 year range but you've only listed the ones that appear in the year sheets.
A simpler way via formulas would be to have ALL the months covering the 3 years in a column of its own but this would widen the output considerably and many columns would comprise completely of zeroes if no job has been won in that month and year.

Hence the suggestion for a VBA solution which is far beyond my expertise.

 

[nfs001]

Hello Special-K-99

Thank you for replying. The Year Worksheets reference the month that the job was tendered in a different column, I just want to calculate how many jobs a month we are winning. As time progresses Column A should hopefully be populated with more months as we win the jobs (fingers crossed!).

I have zero experience with VBA so I think I'll be doing a bit of research!

Thanks again