If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|