using VBA to copy formulas down as well as across and keeping the ranges variable.

Copper Contributor
Hello, I've got a large workbook of projects and several lines of funding for each project. at the base of each chunk of said project's funding there is a sum total. I'm wondering if it's possible to then drag each of those sum total formulas across the 3 columns to the right so that right next to the funding totals I can also total up some other datapoints. each project has different amounts of funding lines and so I need the formula to be smart enough to detect the sum totals to drag across and to apply this down the entire sheet of data. Is this possible and how can I achieve this?
Thank you in advance for all your help!
6 Replies

@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.

let's say that the totals to the right of each funding line is what's being billed against said project for billing period 1 ... then billing period 2 and so on. If I cannot figure out a way to sum across using vba then i have to manually do it to learn for each new billing period what the total billed will be. then verify that against my invoice.

@sbast22 Can you upload a file with some made-up data for three or four projects and visualise what you want to achieve.

@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 :-/.

@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.

I appreciate the attempt. Although what I am trying to do is not about visualization for anyone. This is a billing process for a very large contract and it takes up a lot of time having to do repetitive copying and pasting and formatting (the dragging formulas). I was trying to build a macros to help with speeding up that portion while keeping the rows variable since the # of funding lines a project would have are not always the same.