determine a range using a formula
 

I have a spreadsheet with budgeted financial data in columns, the heading for
each column is a 3 letter monthly abbreviation. Each month actual financial
data is entered in a corresponding table. I want to build an automated array
for the YTD budgeted data based on one cell in the spreadsheet, the currrent
month being evaluated.

Example, its March and I need to compare actual March ytd expenses to
Bugeted YTD expenses. The function would look at the single cell that
contains the string 'Mar Actual.' I would use the function left(Cell
Reference, 3) to return the string value 'Mar'. I want to look convert that
to an array in the formula sum(c1r1:c3r1) where = the month.
--
Thx Jonathon

determine a range using a formula
 

With the string 'Mar Actual' in cell M1; try

=SUM(OFFSET(A:A,,MATCH(M1,1:1,0)-1))

--
Jacob (MVP - Excel)


"Jonathon Shull" wrote:

I have a spreadsheet with budgeted financial data in columns, the heading for
each column is a 3 letter monthly abbreviation. Each month actual financial
data is entered in a corresponding table. I want to build an automated array
for the YTD budgeted data based on one cell in the spreadsheet, the currrent
month being evaluated.

Example, its March and I need to compare actual March ytd expenses to
Bugeted YTD expenses. The function would look at the single cell that
contains the string 'Mar Actual.' I would use the function left(Cell
Reference, 3) to return the string value 'Mar'. I want to look convert that
to an array in the formula sum(c1r1:c3r1) where = the month.
--
Thx Jonathon

determine a range using a formula
 

ended up resolving as follows after looking through some other posts....

=SUM(C3:INDEX(C3:N3,('Lookup Tables'!$B$1)))
--
Thx Jonathon


"Jacob Skaria" wrote:

With the string 'Mar Actual' in cell M1; try

=SUM(OFFSET(A:A,,MATCH(M1,1:1,0)-1))

--
Jacob (MVP - Excel)


"Jonathon Shull" wrote:

I have a spreadsheet with budgeted financial data in columns, the heading for
each column is a 3 letter monthly abbreviation. Each month actual financial
data is entered in a corresponding table. I want to build an automated array
for the YTD budgeted data based on one cell in the spreadsheet, the currrent
month being evaluated.

Example, its March and I need to compare actual March ytd expenses to
Bugeted YTD expenses. The function would look at the single cell that
contains the string 'Mar Actual.' I would use the function left(Cell
Reference, 3) to return the string value 'Mar'. I want to look convert that
to an array in the formula sum(c1r1:c3r1) where = the month.
--
Thx Jonathon