Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-27-2011, 10:07 AM
markg2 markg2 is offline Carry-forward: Tab to Tab? Windows 7 Carry-forward: Tab to Tab? Office 2007
Expert
Carry-forward: Tab to Tab?
 
Join Date: Nov 2009
Location: Evergreen, CO
Posts: 344
markg2 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 01-27-2011, 01:13 PM
BjornS BjornS is offline Carry-forward: Tab to Tab? Windows Vista Carry-forward: Tab to Tab? Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 01-27-2011, 03:54 PM
markg2 markg2 is offline Carry-forward: Tab to Tab? Windows 7 Carry-forward: Tab to Tab? Office 2007
Expert
Carry-forward: Tab to Tab?
 
Join Date: Nov 2009
Location: Evergreen, CO
Posts: 344
markg2 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 01-28-2011, 04:10 AM
macropod's Avatar
macropod macropod is offline Carry-forward: Tab to Tab? Windows 7 32bit Carry-forward: Tab to Tab? Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 01-28-2011, 06:58 AM
markg2 markg2 is offline Carry-forward: Tab to Tab? Windows 7 Carry-forward: Tab to Tab? Office 2007
Expert
Carry-forward: Tab to Tab?
 
Join Date: Nov 2009
Location: Evergreen, CO
Posts: 344
markg2 is on a distinguished road
Default

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

Mark
Reply With Quote
  #6  
Old 01-28-2011, 07:56 AM
markg2 markg2 is offline Carry-forward: Tab to Tab? Windows 7 Carry-forward: Tab to Tab? Office 2007
Expert
Carry-forward: Tab to Tab?
 
Join Date: Nov 2009
Location: Evergreen, CO
Posts: 344
markg2 is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 01-28-2011, 01:16 PM
macropod's Avatar
macropod macropod is offline Carry-forward: Tab to Tab? Windows 7 32bit Carry-forward: Tab to Tab? Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 01-29-2011, 02:40 AM
BjornS BjornS is offline Carry-forward: Tab to Tab? Windows Vista Carry-forward: Tab to Tab? Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 01-29-2011, 02:52 AM
BjornS BjornS is offline Carry-forward: Tab to Tab? Windows Vista Carry-forward: Tab to Tab? Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

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
Reply With Quote
  #10  
Old 01-29-2011, 07:03 AM
markg2 markg2 is offline Carry-forward: Tab to Tab? Windows 7 Carry-forward: Tab to Tab? Office 2007
Expert
Carry-forward: Tab to Tab?
 
Join Date: Nov 2009
Location: Evergreen, CO
Posts: 344
markg2 is on a distinguished road
Default

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
Reply With Quote
  #11  
Old 01-29-2011, 07:35 AM
BjornS BjornS is offline Carry-forward: Tab to Tab? Windows Vista Carry-forward: Tab to Tab? Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

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
Reply With Quote
  #12  
Old 01-30-2011, 01:07 AM
macropod's Avatar
macropod macropod is offline Carry-forward: Tab to Tab? Windows 7 32bit Carry-forward: Tab to Tab? Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Forward without forwarding formatting whatever61 Outlook 0 12-14-2010 08:08 AM
Carry-forward: Tab to Tab? forward message to outlook category knoutam Outlook 1 11-07-2010 02:13 PM
Can I carry Outlook 2007 with me on a usb stick? Hansio Outlook 2 09-09-2010 01:29 PM
Auto-Forward Read Reply timcar1 Outlook 0 10-13-2009 09:33 PM
Auto forward return receipt to Requestor sarav@msoutlook Outlook 0 12-09-2008 06:34 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:42 PM.


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