Hi All,
I am trying to figure out a better way to get monthly totals for data imported from a Gantt chart into columns A to D.
I added a hidden reference column that gets imported to column A in Excel.
The table in the image below needs to show the monthly orders for:
I am currently using the formula below:
=SUMIFS(australia_orders,australia_dates,">="&G$2,australia_dates,"<="&EOMONTH(G$210))
But this involves manually selecting and naming ranges and with over 40 customers it takes a long time. Other people are working collaboratively on the Gantt chart and constantly updating and changing rows/columns. So the next time I import the data, I need to check and redo the ranges all over again.
It will take a fraction of the time if I can assign each row a reference in the Gantt chart, then get Excel to do the rest.
Any suggestions will be greatly appreciated.
Cheers,
Russell
I am trying to figure out a better way to get monthly totals for data imported from a Gantt chart into columns A to D.
I added a hidden reference column that gets imported to column A in Excel.
The table in the image below needs to show the monthly orders for:
- Australia which includes references ref1 + ref2 + ref3
- Customer 1 which includes ref2, and so on for the other customers...
- Then a total including all references from ref1 to ref6
I am currently using the formula below:
=SUMIFS(australia_orders,australia_dates,">="&G$2,australia_dates,"<="&EOMONTH(G$210))
But this involves manually selecting and naming ranges and with over 40 customers it takes a long time. Other people are working collaboratively on the Gantt chart and constantly updating and changing rows/columns. So the next time I import the data, I need to check and redo the ranges all over again.
It will take a fraction of the time if I can assign each row a reference in the Gantt chart, then get Excel to do the rest.
Any suggestions will be greatly appreciated.
Cheers,
Russell