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  |