Microsoft Office Forums Carry-forward: Tab to Tab?
 User Name Remember Me? Password
 Register FAQ Search Today's Posts Mark Forums Read

 Thread Tools Display Modes
#1
01-27-2011, 10:07 AM
 markg2 Windows 7 Office 2007 Expert Join Date: Nov 2009 Location: Evergreen, CO Posts: 320
Carry-forward: 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 first--minus 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
01-27-2011, 01:13 PM
 BjornS Windows Vista Office 2003 Competent Performer Join Date: Jan 2010 Location: Sweden Posts: 116

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 month-tab 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
01-27-2011, 03:54 PM
 markg2 Windows 7 Office 2007 Expert Join Date: Nov 2009 Location: Evergreen, CO Posts: 320

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
01-28-2011, 04:10 AM
 macropod Windows 7 32bit Office 2000 Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 19,525

hi Mark,

Assuming the carry-forward 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
01-28-2011, 06:58 AM
 markg2 Windows 7 Office 2007 Expert Join Date: Nov 2009 Location: Evergreen, CO Posts: 320

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

Mark
#6
01-28-2011, 07:56 AM
 markg2 Windows 7 Office 2007 Expert Join Date: Nov 2009 Location: Evergreen, CO Posts: 320

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
01-28-2011, 01:16 PM
 macropod Windows 7 32bit Office 2000 Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 19,525

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
01-29-2011, 02:40 AM
 BjornS Windows Vista Office 2003 Competent Performer Join Date: Jan 2010 Location: Sweden Posts: 116

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"&A1-1&"!J10"))

Kind regards
Bjorn
#9
01-29-2011, 02:52 AM
 BjornS Windows Vista Office 2003 Competent Performer Join Date: Jan 2010 Location: Sweden Posts: 116

Hi again Mark,
if you don't have (or want to have) the "tab-number" 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
01-29-2011, 07:03 AM
 markg2 Windows 7 Office 2007 Expert Join Date: Nov 2009 Location: Evergreen, CO Posts: 320

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
01-29-2011, 07:35 AM
 BjornS Windows Vista Office 2003 Competent Performer Join Date: Jan 2010 Location: Sweden Posts: 116

Hi Mark,
If you fix the month number in cell A1, this formula i quite easy to explain:

=IF(A1=1,0,INDIRECT("tab"&A1-1&"!J10"))

the "&"-sign concatinates "tab" with "A1-1" with "!J10"

Assume you are in May, i.e. month 5 (means Cell A1 = 5).

then "A1-1" would give the number 5-1 = 4
which means that "tab"&A1-1&"!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
01-30-2011, 01:07 AM
 macropod Windows 7 32bit Office 2000 Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 19,525

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 Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post whatever61 Outlook 0 12-14-2010 08:08 AM knoutam Outlook 1 11-07-2010 02:13 PM Hansio Outlook 2 09-09-2010 01:29 PM timcar1 Outlook 0 10-13-2009 09:33 PM sarav@msoutlook Outlook 0 12-09-2008 06:34 AM

All times are GMT -7. The time now is 11:49 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft