variable number of columns in sum

[spock1971]

Hi all

I'm looking to build a report detailing 12 months figures from opening date. I'm comparing them to budgetted figures.

My problem is that the budgetted figures are for a full 12 months so I'm comparing 3 months actuals to 12 months budget.

What I'd like to do is tell excel how many columns to sum in a variable.

i.e. currently:

=sum(0506budget!n2:02)+sum(0607budget!d2:m2)
(2 months) + (10 months)
making a full 12 months.

However, I only want to sum(0607budget!d2:variablecolumn row2). This report is for aprox. 50 stores and they all have different end columns.

Cheers

 

[HaHoBe]

Hi, spock1971,

to my opinion both the Indirect as well as the Address Function could be of good use for solving the problem without VBA.

Ciao,
Holger

 

[shades]

This will give you the number of the last column (in Row 2):

=MATCH(9.99999999999E+307,2:2)

To make it work with all rows, assuming the last column will always be numeric, use:

=MATCH(9.99999999999E+307,$A2:$IV2)

or to complete this:

=CELL("Address",INDEX(1:1,MATCH(9.99999999999999E+307,1:1)))
________
New mexico medical marijuana dispensaries

 

[spock1971]

Cheers all

Never used address before so will give it a go.

Think I've got it working with offset too. Using a hidden cell as the count I've got:

=sum(a2ffset(a2,0,newvariablecell)