Sep 29 2021 08:06 PM
Sep 29 2021 08:49 PM
@sbast22 I may not have a clear picture in my mind of what you are dealing with, but is seems unwise to maintain a large list of funding information and then insert totals by project in the same list. Better to summarise the "funding data base" in a pivot table.
Sep 29 2021 09:11 PM
Sep 29 2021 09:16 PM
@sbast22 Can you upload a file with some made-up data for three or four projects and visualise what you want to achieve.
Sep 30 2021 05:22 AM
@Riny_van_Eekelen Please see file attached. This is much smaller scale as I mainly need to give a visual for how this works. Previously, we had to hand jam each total to be invoiced. But with this workbook I built we can use vlookup to line up the billing totals. The issue is that we get funding as we go and so the lines will change. Typically what I do is copy S4 & T4 all the way down Then copy columns S over to column R as Values Only. From there, I basically drag the sum total formula across to column T. When doing this I also copy the blank cells in between because the spacing is basically the same in the workbook that I will then paste column R into once i've ensured that the bill looks accurate. I hope that's not too confusing :-/.
Sep 30 2021 06:03 AM
@sbast22 I took the liberty to restructure you data a bit, so that I could create a pivot table. See attached.
Though, I realise that it has totally messed up your lookups, the point I would like to make is that you should not format raw data with merged cells, double header rows and empty columns. Keep it clean, so that you use pivot tables to the summarising for your.
But then again, perhaps I totally misunderstand your needs. Then I can only apologise for having taken your time.
Sep 30 2021 10:16 AM