Forum Discussion
using VBA to copy formulas down as well as across and keeping the ranges variable.
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.