Prev: VBA to autofill from relative location
Next: Finding most recent date for each customer - XL 2007
From: Jonathon Shull on 30 Apr 2010 11:24
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
From: Jacob Skaria on 30 Apr 2010 11:32
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
From: Jonathon Shull on 30 Apr 2010 12:23
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
 | 
Pages: 1
Prev: VBA to autofill from relative location
Next: Finding most recent date for each customer - XL 2007