Reference previous column - =PrevCol(Cumulative_Customers)+New_Customers

[Sweedler]

Hello lovely Excellites,

So, I am working on a rather large financial document and when calculating/making prognoses I need to have some formulas that reference the previous cell (previous column in a specific row). I have a friend who did something similar and had FOR EXAMPLE:

=PrevCol(Cumulative_Customers)+New_Customers

Cumulative_Customers and New_Customers are both named ranges (and the ranges are a whole row)

The formula is meant to calculate the cumulative customers by adding the value from the previous column value of cumulative customers and add the value for New customer to that ... and so on and so on for each cell/column moving on. I assumed PrevCol was something that referred to the PREVious COLumn .. but it is not working.

I thought perhaps this could be some Macro that I am not using that he has. Any ideas out there people.

 

[StephenCrump]

It sounds like you have something like this in mind?

	A	B	C	D	E	F	G
1
2	New_Customers	2	3	5	7	11	13
3
4	Cumulative_Customers	2	5	10	17	28	41
Sheet1

Cell Formulas
Range	Formula
B4	B4	=New_Customers
C4:G4	C4	=prevcol(Cumulative_Customers)+New_Customers

Named Ranges
Name	Refers To	Cells
Cumulative_Customers	=Sheet1!$4:$4	C4:G4

and New_Customers:=Sheet1!$2:$2

where:

Function PrevCol(RowRef As Range) As Variant

    PrevCol = RowRef(Application.Caller.Column - 1).Value

End Function

But isn't this unnecessarily convoluted when you could simply have:

C4: =B4+C2, copied to the right

 

[Sweedler]

You may be right about this method being overly convoluted ... but I like the aesthetics of it.

Thank you so much for the code string, certainly did solve my issues.