#1




Carryforward: Tab to Tab?
XP + Office '03
For example, a workbook file containing 3 tabbed spreadsheets. The spreadsheets represent successive months of the year. Once the first tab (month) is completed the second (month) tab is started using the identical layout and formulas contained in the firstminus data input. The second sheet wants to start with totals from the first and the third with totals from the second and so on. How do I arrange a formula that will bring the total (of a formula) from a cell in Tab 1 to a cell in Tab 2? Thanks, Mark 
#2




Hi,
try this: =SUM(Tab1:Tab2!A1) My proposal (might or might not be suitable for your case) is however to create a "FirstTab" and a "LastTab". Both of them should be empty. Place all your monthtab inbetween those two. Change the formula above to: =SUM(FirstTab:LastTab!A1) Otherwise you might need to change your formula every month. Kind regards Bjorn 
#3




Before going further with your suggestion, I was thinking about my question after I left the house and would like to run it by you.
Assume that I have completed Tab1 (January) and wish to bring forward the column total J10 from January to a starting cell in Tab2 (February). With my cursor in Tab2 cell A1, enter '+1*' then click on Tab 1, cursor to cell J10, hit enter. Then the formula in cell A1 of Tab 2 would be 1*(Tab1) J10. I'm assuming that somehow Excel would figure out that I was using cells from two different tabs without me explicitly typing in that fact. Since I haven't explicitly entered any cell coordinates and have cursored to each cell location, Excel would always regard these cells as relative in the event another tab were added in between the Tab1 and Tab2 (which I couldn't imagine doing anyway)? Mark 
#4




hi Mark,
Assuming the carryforward figure from the previous worksheet is in the last cell in the column concerned, you could use a formula like: =INDEX(Sheet1!A:A,MATCH(1E+306,Sheet1!A:A,1)) where 'Sheet1' is the worksheet name and 'A:A' is the column from which you want to bring forward the value.
__________________
Cheers, Paul Edstein [MS MVP  Word] 
#5




Thanks Paul, but way too complex (for me). I'll give Bjorn's method a try.
Mark 
#6




I just tried my 'simplistic' method that I set forth earlier. It seems to work.
I opened a new workbook and added 5 tabs. In tab 3 I added a series of 1's to equal 3. In tab 5 I followed my earlier stated procedure resulting in a total of 3. In that cell it showed a formula of: =1*Sheet_3!H11. Is there a reason not to use my 'easy' method? Mark 
#7




Hi Mark,
All your 'simple solution is doing is retrieving the value from Sheet_3!H11. It's no different than using =Sheet_3!H11. As such, it'll require you to input the specific cell address that you want to reference for the carry forward every month. With Bjorn's solution, as soon as you move last month's sheet to sit between the "FirstTab" and "LastTab" sheets, the formula he gave you will break down (it becomes a circular reference).
__________________
Cheers, Paul Edstein [MS MVP  Word] 
#8




Hi Mark,
I assume that you have the month number anywhere on your sheet. Example: Anywhere in "tab3" there should be a cell containing the number "3". Assume the information is provided in cell A1 for each tab. With the above assumptions, try this formula to refer to cell J10 on the "previous tab": =IF(A1=1,0,INDIRECT("tab"&A11&"!J10")) Kind regards Bjorn 
#9




Hi again Mark,
if you don't have (or want to have) the "tabnumber" in cell A1 on each tab, you could replace the two A1's in the formula above with this formula: 1*MID(CELL("filename",A999),SEARCH("]tab",CELL("filename",A999))+4,50) (the A999 could be any cell in your sheet, it is not important what cell it refers to, but it has to be there) If your tab has other names than tab1, tab2 etc you have you adjust the formula above. (I hope I made the translation from Swedish formula names into English formulas names correctly :) Kind regards Bjorn 
#10




The reason that I have avoided Paul and Bjorn's suggestions is that the correct way of handling my task involves a lot of syntax that I do not understand and I would not cut/paste a solution absent some basic level of understanding. I see Paul's point regarding my simplistic method and agree.
Is there a glossary or funadmentals page that explains the syntax you all are including in your formulas that I could review before pressing forward? Mark 
#11




Hi Mark,
If you fix the month number in cell A1, this formula i quite easy to explain: =IF(A1=1,0,INDIRECT("tab"&A11&"!J10")) the "&"sign concatinates "tab" with "A11" with "!J10" Assume you are in May, i.e. month 5 (means Cell A1 = 5). then "A11" would give the number 51 = 4 which means that "tab"&A11&"!J10" would turn in "tab4!J10". If you just look at that short formula (=tab4!J10) it is of course just a cell reference to cell J10 in the "tab4". This formula is in this example located in "tab5". J10 in tab4 is the sum you want to bring along to from April to May. The INDIRECT function, converts "a text formula" into a cell reference. This means that the formula above will look like this to Excel: =IF(A1=1,0,tab4!J10) and for all months where the month number is not 1 (January), the result will be: =tabXX!J10 which is the reference to "previous month" (= XX) which you would like to have. Month number "1" must of course give the result zero, since "previous month" is not defined. What the whole function above does is actually only calculating the name (number) of the previous tab and then refer to cell J10 in that tab. If you are in "tab9" the result of the formula will be "=tab8!J10" Kind regards Bjorn 
#12




Hi Mark,
The inner workings of the INDEX and MATCH fuunctions are explained in Excel's Help file. FWIW: • 1E+306 is a number expressed in scientific notation. It's actually the largest numnber Excel is capable of storing. • MATCH(1E+306,Sheet1!A:A,1) tells Excel to use 1E+306 to find the highest numbered (ie last used) row in column A on Sheet1. • INDEX(Sheet1!A:A,MATCH(1E+306,Sheet1!A:A,1)) tells Excel to return the value of the cell referenced by the MATCH function.
__________________
Cheers, Paul Edstein [MS MVP  Word] 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Forward without forwarding formatting  whatever61  Outlook  0  12142010 08:08 AM 
forward message to outlook category  knoutam  Outlook  1  11072010 02:13 PM 
Can I carry Outlook 2007 with me on a usb stick?  Hansio  Outlook  2  09092010 01:29 PM 
AutoForward Read Reply  timcar1  Outlook  0  10132009 09:33 PM 
Auto forward return receipt to Requestor  sarav@msoutlook  Outlook  0  12092008 06:34 AM 