Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with A 3-D reference type formula (real novice here)
I think this is what I need, but I am not sure, as I have never done anything
this involved before - using XL 2003 from MS Ofc Pro. I have a workbook with 12 simple worksheets (Jan '05 thru to Dec '05). Each WS lists their respective months income & expenses and calculates & verifies the totals. I then created a few simple functions to show monthly gross income, expenses & net income on @ WS. Now I would like to be able to do a progressive year-to-date type calculation from each of the WS - much like you would see on a paycheck. i.e WS1 - cell W49 Calculates and displays sum of gross income WS1 - cell W50 Calculates and displays sum of expenses WS1 - cell W51 Calculates and displays the sum of the above 2 cells WS2 uses cells W50, W51, W52 WS3 uses cells W33, W34, W35 WS4 uses cells W48, W49, W50 etc each WS seems to use different cells for the month end totals. So I think I would need to identify the cells I want to use i.e WS1 gross income + WS2 gross income = Year to date gross income then the next step would be total of WS1 and WS2 plus new value from WS3 for a new year to date value. Just don't know how to proceed. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with A 3-D reference type formula (real novice here)
Hi!
each WS seems to use different cells for the month end totals. If you setup all your monthly sheets the same this is relatively easy. On each of your monthly sheets put your month end total formulas in the same cell, say, A1 Then, assume your workbook tabs are laid out like this: Summary | Jan | Feb | Mar | Apr |.....................| Dec | To get a YTD sum from all the monthly sheets: =SUM(Jan:Dec!A1) Any sheets that are inserted between Jan and Dec will be included in the sum formula. Another popular way to do this is to insert a blank sheet before Jan and name it Start. Then insert another blank sheet after Dec and name it End. Like this: Summary | Start | Jan | Feb | Mar | Apr |.....................| Dec | End | Then use the same formula: =SUM(Start:End!A1) Biff "topaz" wrote in message ... I think this is what I need, but I am not sure, as I have never done anything this involved before - using XL 2003 from MS Ofc Pro. I have a workbook with 12 simple worksheets (Jan '05 thru to Dec '05). Each WS lists their respective months income & expenses and calculates & verifies the totals. I then created a few simple functions to show monthly gross income, expenses & net income on @ WS. Now I would like to be able to do a progressive year-to-date type calculation from each of the WS - much like you would see on a paycheck. i.e WS1 - cell W49 Calculates and displays sum of gross income WS1 - cell W50 Calculates and displays sum of expenses WS1 - cell W51 Calculates and displays the sum of the above 2 cells WS2 uses cells W50, W51, W52 WS3 uses cells W33, W34, W35 WS4 uses cells W48, W49, W50 etc each WS seems to use different cells for the month end totals. So I think I would need to identify the cells I want to use i.e WS1 gross income + WS2 gross income = Year to date gross income then the next step would be total of WS1 and WS2 plus new value from WS3 for a new year to date value. Just don't know how to proceed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create reference formula that looks through all sheets for matchin | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
I am not real sure what type formula to use | Excel Discussion (Misc queries) | |||
"real" formula | Excel Discussion (Misc queries) | |||
Formula to reference column heading | Excel Worksheet Functions |