Compare one year to another, but just YTD

Let's see if I can explain this. I have two sheets, the first sheet is 
labeled 2005 and the second one is 2006.  In A1 thru A12 on both sheets is 
Jan, Feb, Mar, etc.  On 2005 B1 thru B12 we have monthly values. On 2006 B1 
thru B7 (as we are just now going into Aug.) we have monthly values. In B13 
on both sheets are totals. The problem is I can't compare on year to the 
next as the total on the 2005 sheet is for the full year. I'd like to add 
Label in A14 that says 2005 YTD and have a formula in B14 that looks at how 
many cells in the range B1 thru B12 on 2006 have values then sum that same 
number of cells from the B column on sheet 2005. This way I'd be able to 
compare 2006's YTD with the same YTD of 2005. 


0
John.Doe1 (116)
8/1/2006 2:16:30 PM
excel 39879 articles. 2 followers. Follow

5 Replies
561 Views

Similar Articles

[PageSpeed] 16

=SUM(OFFSET('2005'!$B$1,,,COUNT('2006'!$B$1:$B$12),1))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"M.Siler" <John.Doe@NoSpam.com> wrote in message
news:#KsuWUXtGHA.1512@TK2MSFTNGP03.phx.gbl...
> Let's see if I can explain this. I have two sheets, the first sheet is
> labeled 2005 and the second one is 2006.  In A1 thru A12 on both sheets is
> Jan, Feb, Mar, etc.  On 2005 B1 thru B12 we have monthly values. On 2006
B1
> thru B7 (as we are just now going into Aug.) we have monthly values. In
B13
> on both sheets are totals. The problem is I can't compare on year to the
> next as the total on the 2005 sheet is for the full year. I'd like to add
> Label in A14 that says 2005 YTD and have a formula in B14 that looks at
how
> many cells in the range B1 thru B12 on 2006 have values then sum that same
> number of cells from the B column on sheet 2005. This way I'd be able to
> compare 2006's YTD with the same YTD of 2005.
>
>


0
8/1/2006 2:23:09 PM
I had to modify it to 
=SUM(OFFSET('2005'!$B$1,,,COUNT('2006'!$B$1:$B$12)+1,1)) as it was off my 
one month. I don't totally understand the fomula, but I'm looking at it now.


"Bob Phillips" <bob.NGs@xxxx.om> wrote in message 
news:u$hiEYXtGHA.1512@TK2MSFTNGP03.phx.gbl...
> =SUM(OFFSET('2005'!$B$1,,,COUNT('2006'!$B$1:$B$12),1))
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "M.Siler" <John.Doe@NoSpam.com> wrote in message
> news:#KsuWUXtGHA.1512@TK2MSFTNGP03.phx.gbl...
>> Let's see if I can explain this. I have two sheets, the first sheet is
>> labeled 2005 and the second one is 2006.  In A1 thru A12 on both sheets 
>> is
>> Jan, Feb, Mar, etc.  On 2005 B1 thru B12 we have monthly values. On 2006
> B1
>> thru B7 (as we are just now going into Aug.) we have monthly values. In
> B13
>> on both sheets are totals. The problem is I can't compare on year to the
>> next as the total on the 2005 sheet is for the full year. I'd like to add
>> Label in A14 that says 2005 YTD and have a formula in B14 that looks at
> how
>> many cells in the range B1 thru B12 on 2006 have values then sum that 
>> same
>> number of cells from the B column on sheet 2005. This way I'd be able to
>> compare 2006's YTD with the same YTD of 2005.
>>
>>
>
> 


0
John.Doe1 (116)
8/1/2006 3:17:57 PM
I modified the formula to I could copy it to other columns

 =SUM(OFFSET('2005'!B$1,,,COUNT('2006'!B$1:B$12)+1,1)) This worked
 =SUM(OFFSET('2005'!C$1,,,COUNT('2006'!C$1:C$12)+1,1)) This did not I had to 
remove the +1 so it looks like this
 =SUM(OFFSET('2005'!C$1,,,COUNT('2006'!C$1:C$12),1)) What am I missing?? I 
need to copy this to the right for columns B thru K

"M.Siler" <John.Doe@NoSpam.com> wrote in message 
news:unUes2XtGHA.3264@TK2MSFTNGP03.phx.gbl...
>I had to modify it to 
>=SUM(OFFSET('2005'!$B$1,,,COUNT('2006'!$B$1:$B$12)+1,1)) as it was off my 
>one month. I don't totally understand the fomula, but I'm looking at it 
>now.
>
>
> "Bob Phillips" <bob.NGs@xxxx.om> wrote in message 
> news:u$hiEYXtGHA.1512@TK2MSFTNGP03.phx.gbl...
>> =SUM(OFFSET('2005'!$B$1,,,COUNT('2006'!$B$1:$B$12),1))
>>
>> --
>>
>> HTH
>>
>> Bob Phillips
>>
>> (replace xxxx in the email address with gmail if mailing direct)
>>
>> "M.Siler" <John.Doe@NoSpam.com> wrote in message
>> news:#KsuWUXtGHA.1512@TK2MSFTNGP03.phx.gbl...
>>> Let's see if I can explain this. I have two sheets, the first sheet is
>>> labeled 2005 and the second one is 2006.  In A1 thru A12 on both sheets 
>>> is
>>> Jan, Feb, Mar, etc.  On 2005 B1 thru B12 we have monthly values. On 2006
>> B1
>>> thru B7 (as we are just now going into Aug.) we have monthly values. In
>> B13
>>> on both sheets are totals. The problem is I can't compare on year to the
>>> next as the total on the 2005 sheet is for the full year. I'd like to 
>>> add
>>> Label in A14 that says 2005 YTD and have a formula in B14 that looks at
>> how
>>> many cells in the range B1 thru B12 on 2006 have values then sum that 
>>> same
>>> number of cells from the B column on sheet 2005. This way I'd be able to
>>> compare 2006's YTD with the same YTD of 2005.
>>>
>>>
>>
>>
>
> 


0
John.Doe1 (116)
8/1/2006 3:30:01 PM
Sorry... nothing wrong with the orginal formula... one of the numbers was 
entered wrong so excel treated it as text and that messed up the formula.

Thank you.

"M.Siler" <John.Doe@NoSpam.com> wrote in message 
news:unUes2XtGHA.3264@TK2MSFTNGP03.phx.gbl...
>I had to modify it to 
>=SUM(OFFSET('2005'!$B$1,,,COUNT('2006'!$B$1:$B$12)+1,1)) as it was off my 
>one month. I don't totally understand the fomula, but I'm looking at it 
>now.
>
>
> "Bob Phillips" <bob.NGs@xxxx.om> wrote in message 
> news:u$hiEYXtGHA.1512@TK2MSFTNGP03.phx.gbl...
>> =SUM(OFFSET('2005'!$B$1,,,COUNT('2006'!$B$1:$B$12),1))
>>
>> --
>>
>> HTH
>>
>> Bob Phillips
>>
>> (replace xxxx in the email address with gmail if mailing direct)
>>
>> "M.Siler" <John.Doe@NoSpam.com> wrote in message
>> news:#KsuWUXtGHA.1512@TK2MSFTNGP03.phx.gbl...
>>> Let's see if I can explain this. I have two sheets, the first sheet is
>>> labeled 2005 and the second one is 2006.  In A1 thru A12 on both sheets 
>>> is
>>> Jan, Feb, Mar, etc.  On 2005 B1 thru B12 we have monthly values. On 2006
>> B1
>>> thru B7 (as we are just now going into Aug.) we have monthly values. In
>> B13
>>> on both sheets are totals. The problem is I can't compare on year to the
>>> next as the total on the 2005 sheet is for the full year. I'd like to 
>>> add
>>> Label in A14 that says 2005 YTD and have a formula in B14 that looks at
>> how
>>> many cells in the range B1 thru B12 on 2006 have values then sum that 
>>> same
>>> number of cells from the B column on sheet 2005. This way I'd be able to
>>> compare 2006's YTD with the same YTD of 2005.
>>>
>>>
>>
>>
>
> 


0
John.Doe1 (116)
8/1/2006 3:36:53 PM
I just sat back and sent hints across the ether <bg>

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"M.Siler" <John.Doe@NoSpam.com> wrote in message
news:O#ZWRBYtGHA.3552@TK2MSFTNGP03.phx.gbl...
> Sorry... nothing wrong with the orginal formula... one of the numbers was
> entered wrong so excel treated it as text and that messed up the formula.
>
> Thank you.
>
> "M.Siler" <John.Doe@NoSpam.com> wrote in message
> news:unUes2XtGHA.3264@TK2MSFTNGP03.phx.gbl...
> >I had to modify it to
> >=SUM(OFFSET('2005'!$B$1,,,COUNT('2006'!$B$1:$B$12)+1,1)) as it was off my
> >one month. I don't totally understand the fomula, but I'm looking at it
> >now.
> >
> >
> > "Bob Phillips" <bob.NGs@xxxx.om> wrote in message
> > news:u$hiEYXtGHA.1512@TK2MSFTNGP03.phx.gbl...
> >> =SUM(OFFSET('2005'!$B$1,,,COUNT('2006'!$B$1:$B$12),1))
> >>
> >> --
> >>
> >> HTH
> >>
> >> Bob Phillips
> >>
> >> (replace xxxx in the email address with gmail if mailing direct)
> >>
> >> "M.Siler" <John.Doe@NoSpam.com> wrote in message
> >> news:#KsuWUXtGHA.1512@TK2MSFTNGP03.phx.gbl...
> >>> Let's see if I can explain this. I have two sheets, the first sheet is
> >>> labeled 2005 and the second one is 2006.  In A1 thru A12 on both
sheets
> >>> is
> >>> Jan, Feb, Mar, etc.  On 2005 B1 thru B12 we have monthly values. On
2006
> >> B1
> >>> thru B7 (as we are just now going into Aug.) we have monthly values.
In
> >> B13
> >>> on both sheets are totals. The problem is I can't compare on year to
the
> >>> next as the total on the 2005 sheet is for the full year. I'd like to
> >>> add
> >>> Label in A14 that says 2005 YTD and have a formula in B14 that looks
at
> >> how
> >>> many cells in the range B1 thru B12 on 2006 have values then sum that
> >>> same
> >>> number of cells from the B column on sheet 2005. This way I'd be able
to
> >>> compare 2006's YTD with the same YTD of 2005.
> >>>
> >>>
> >>
> >>
> >
> >
>
>


0
8/1/2006 4:11:45 PM
Reply:

Similar Artilces:

How many worksheets in one workbook?
Is there a limit to how many worksheets can be inserted into one workbook? If so how many? Thanks, Janice AFAIK you are limited only by the size of your RAM "Janice Lathen" <anonymous@discussions.microsoft.com> wrote in message news:e1e901c43c26$938e3210$a101280a@phx.gbl... : Is there a limit to how many worksheets can be inserted : into one workbook? If so how many? : : Thanks, : Janice Hi also see http://www.decisionmodels.com/memlimitsc.htm -- Regards Frank Kabel Frankfurt, Germany Janice Lathen wrote: > Is there a limit to how many worksheets can be inserted ...

including date and time in one function
I am trying to make a worksheet that will return a value during certain time period. For example: On Monday at 8:00-9:00, I would lik it to display a different value than the same time on Tuesday. Also, need it to show a different value later on in the same day. I canno figure out if there is a way to combine DATEVALUE and TIMEVALUE, nor a i sure if a combination of the two is what is exactly what I want, bu it is somewhere along those lines. I would appreciate any help tha anyone can give me -- Message posted from http://www.ExcelForum.com Hi you can add them. e.g. try =IF(AND(A1>=DAT...

Compare DTD,XSD, SCHEMATRON and RELAX NG.
I am a beginner in XML schema. Could you please give me some ideas to compare the feature of DTD, XSD, SCHEMATRON RELAX NG in validate aspect? And could you please recommend some good material in this aspect? Thanks a lot! Read "Effective XML" by Elliot-Rusty Harold. Cheers, Dimitre Novatchev "Goodmannewz" <Goodmannewz@discussions.microsoft.com> wrote in message news:FFF25886-0092-47CC-A556-7A1C553ECAC1@microsoft.com... >I am a beginner in XML schema. Could you please give me some ideas to >compare > the feature of DTD, XSD, SCHEMATRON RELAX NG in va...

when copying a worksheet to a new one, some of the information do.
I had a long spreadsheet that I wanted to break in 2. I created a new worksheet and copied the rows and columns that I needed to copy. When I was done, column "A" was not copied and the rest of the columns were misalligned. Any suggestions please ? What I would do is make two identical worksheets and delete in each one what's not needed. If you click on the empty gray block about Row 1 and to the left of Col. A, it's selects the entire worksheet including column sizes. Copy and paste into a new worksheet, and then delete in each one what you don't want in each o...

Adding another Text Box for a Report
Hello and a blessed day to all! I have another question regarding reports...Please bear with me... I wanted to add another text box in my current unbound form for ranging of dates... Here is what i have so far: I have already an unbound form for ranging of dates, it includes two text box StartDate and EndDate and when the user click Preview the report will generate for that range of dates. Here is the code for that: Private Sub Command10_Click() Dim strReport As String 'Name of report to open. Dim strField As String 'Name of your date field. Dim strWhe...

Data from one file to another
Hi, I have Excel 2002 and have one file with fields from db1 which I wish to populate into another file for db2. The problem is that whilst most of these fields are the same (Fname,LName,Email etc), they are in different columns or there are a few that do not exist in the new db2. Another problem is that there are 6000 of them! I have thought that maybe I can bring them into db2 using copy/past for individual columns and place them where they should go in the new db2. The extra columns I will have to leave in db1 as there is no place for them in db2. Thing is, how do I paste these r...

Reading multiple values from another table.
I have a table with general information in it such as Subject, Reference Rumber, Comments. I would like to have another table with Company Reference and Responsibility. So when I create a new record including Subject and Reference Number, I could look up values from the second table to fill in multiple instances of Company Reference and Responsibility. A better example would be a form to order pizza. The main form would contain name, address, Size and type of pizza. Then from the same form, you would look up toppings from a seperate table to add multiple toppings to each instance of a pizza. ...

Downloaded Upgrade Version
Earlier in the year I downloaded the Money 2006 upgrade to my desktop. Now that I am traveling more I want to handle my Money related activity from my laptop. Since the version I have of Money 2006 is an upgrade, how do I load this on to my laptop (it's never been loaded on it prior)? In microsoft.public.money, "TimHief at AOL dot Com" <TimHief at AOL dot Com@discussions.microsoft.com> wrote: >Earlier in the year I downloaded the Money 2006 upgrade to my desktop. Now >that I am traveling more I want to handle my Money related activity from my >laptop. Si...

One calendar for the whole office
Is this possbile using resources and showing scheduling conflicts. It would be used as a project calendar <atranthought@gmail.com> wrote in message news:1144352125.487990.126650@i39g2000cwa.googlegroups.com... > Is this possbile using resources and showing scheduling conflicts. > > It would be used as a project calendar > Do you use Exchange Server? If so, what version? And what version of Outlook? It always helps if you provide a lot of detail when you post in here... ...

Excahneg server down when one of the GC's is offline?
Checking the Exchange System Manager Directory Access tab I found the following. When server02 is offline exchange (on server03) stops responding. Any idea why and what I can do about it? Thanks! Server01 (ops master) Config Auto Server02 DC Auto Server01 DC Auto Server02 GC Auto Server01 GC auto Is there a way to change the order of the list so that my Server01 is first in line? If you uncheck auto-discover, you can point it to whichever server you choose... "you know who maybe" <nguser2u@spamnotAOL.com> wrote in message news:1199uk3djse922a@news.supernews.com... &g...

Migrating from one server to another
I have a scenario that I'm looking to get some input on from the Exchange experts out there. I have a client that currently uses Exchange 2003 out of an ASP hosted datacenter. The client is leaving that ASP vendor and is installing the servers in house. My question is, if the ASP vendor has exchange 2003 and my client installs exchange 2003 on a server inhouse, what is the best way to migrate all of the user's information, ie mail, contacts, calendar. I know that there is a "Move Mailbox" tool with Exchange 2003, but that to me looks like it is designed to work with serve...

How can I 'diff' [compare content between] two Excel workbooks?
I need to routinely compare changes made to versions of spcific Excel workbooks. I need to be able to quickly and easily reference updates for historical purposes and as an additional tracking mechanism for woksbook data. Hi, Within tools there is a feature called "Track changes", but it's aimed at tracking changes made by users once the workbook is shared. Using this will automatically share the workbook allowing more that one user to make changes simultaneously. Could this feature fulfill your needs? Cheers, A -- Please rate my response if you found it helpful. Tha...

One more try: ActiveSkin question
This question refers to ActiveSkin control in Visual C++ environment. I would like to change caption of a SkinStatic object in an application using an application-specific skin. This is my most recent attempt to change caption of a SkinStatic object named "static_text": /////////////////////////////////////////////////////////////////////// CComQIPtr<ISkin> pSkin = GetDlgItem(IDC_SKIN1)->GetControlUnknown(); CComPtr<ISkinObject> m_pRootSkinObject; pSkin->GetSkinnedWindow((long)m_hWnd, &m_pRootSkinObject); CComPtr<ISkinObject> pText; m_pRootSkinObjec...

integration MDI child views from VC i VB into one MDI App
Hey, I’d like to integrate two applications: First is MDI application written in Visual C++ 6.0. Second is dialog-based application written in Visual Basic 6.0, but I suppose that we can change the main dialogs to MDIChild forms. Both applications are in DLLs at all. Is it possible to use a MDI child forms created in visual basic 6 and in visual studio c++ together in one MDI application? I suppose that main MDI application should be written in VC++, because it’s extremely flexible and I know this environment the best, but maybe I’m wrong :). Maybe for example in C# .net it’d be easier...

limited No. of functions in one cell ?!
i want to have 30 IF functions in one cell but I can only have 9-10 otherwise I get an error message. Thanks for helping! -- andreas prucker ------------------------------------------------------------------------ andreas prucker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28424 View this thread: http://www.excelforum.com/showthread.php?threadid=480168 Even if you could put 30 IF functions in a formula (you can't, you're limited to 7 levels of parens), the formula would be nearly unreadable and unmaintainable. Most likely, there is a better w...

Three different fields in one record referencing to three different records in another table
Hi. I have TableA and TableB TableA has 3 fields where types of file extensions (for different purpose) are supposed to be stored. Table B has many extension type strings for these TableA three fields (.tif, .jpg, .pdf, .gif, etc). 3 combo boxes refer to Table B to get and store TableB keys in TableA appropriated fields. When I set up table retaionship, Acces 'creates' also TableB_1 and TableB_2 and show a diagram where two other similar to tabelB tables show up. So long so good. I can create records in TableA having chosen, for each different file purpose, the respective extens...

One user unable to send external mail
Hi Running exchange 5.5 on NT4. Have one user who suddenly cannot send external mail. All external messages get instantly bounced with the following NDR Your message did not reach some or all of the intended recipients. Subject: test Sent: 12/31/2003 11:34 AM The following recipient(s) could not be reached: '<valid address>' on 12/31/2003 11:34 AM The recipient name is not recognized The MTS-ID of the original message is: c=US;a= ;p=The Computer Par;l=LMW59-031231113340Z-15 MSEXCH:IMS:The Computer Partnership Ltd:TCPNT1:LMW59...

how to create a dynamic descending list of months & years
I'm trying to create a spreadsheet whereby in cell A1 I enter in any given Month and Year ("January 2005", formatted as a date), and in cells B1:J1 the previous months and years show up (December 2004; November 2004; October 2004; etc....) If I type in "April 1985", the adjacent cells would read "March 1985; February 1985; January 1985; December 1984; etc... I've thought of a couple laboriuos, crude, potentially faulty ways of doing this, but it seems like there should be an eloquent solution. Very much appreciated!!! Marika :) Can you put the followi...

Create an item price level as a percent markup of another price le
Is there a utility to create an item price level (for all items) that is a percent markup of another price level. We have created a "DEFAULT" price level with a Price Method of Currency Amount. Now we wish to create another price level that is a 10% price markup of the "DEFAULT" price. Thanks, Glen Hi Glen- The standard functionality in GP does not allow this. What I would suggest is using the percent of list price pricing method instead. Then put in your standard price in the list price field, then set up price levels as 100%, 110%, etc.... Another option would b...

another for the experts
I have now figured out my VLOOKUP problem, so let me proceed with my troubles. I have a sheett of data that represents the entire human genome, organized in ascending order by chromosome. The list is around 9500 rows, and consists of 9 columns. The first is always going to be my x series (the whole point of this is to make a dynamic scatter plot), and each subsequent column is a log2 value, for 1 of 8 individual data sets, which represent my y series. I know how to keep the x series constant, and choose which y series i want via a combo box and linked cell, with the help of an offset...

Moving Store to another machine..
Hi there, using Exchange 2K3 and I have seen people move their stores to other drives, I am curious if I can move my store to another machine using the \\servername\share routine? Thanks, Derek Hi, Derek. No, there are various attributes that need to be updated. Your best bet is probably just to move mailbox from the existing store to the store on the new server. Doing so will automatically update the client (MAPI) profiles (leaving the old server up long enough for users to logon and be redirected, then caching the new server). Additionally, Exchange 2003 provides multi-threaded move ...

public calendar share view incorrect for one user
Have a problem where one client has a few extra items showing on his view of the public calendar that several of the users are using. I would like some pointers as to where to begin looking to see if I can determine why this view is only different for the one user. He is accessing the Exchange server via a vpn connection and using Outlook 2007. I was wondering if it has something to do with his profile on the server or if there is an incorrect copy of the public calendar being cached on his machine ? thanks in advance ...

How to print e-mail to another printer in network
We have a small network with several printers, How can I print my email to some other printer other than my default printer? File > Print > choose another printer, if you have added that printer to your printers folder. Is that what you're looking for? -- Kathleen Orland Outlook Tips: http://www.outlook-tips.net/ http://www.howto-outlook.com/ "DK" wrote: > We have a small network with several printers, How can I print my email to > some other printer other than my default printer? Unfortunately some options will still only print to the default printer. ...

Display a person's age in years & months: for example 4;11
How do you show a person's chronological age in years & months in Excel: I want the age to show up as 4;11 (4 yr 11 mo), not as 4.91. Thanks! long winded but it works for me. =ROUNDDOWN(((TODAY()-A1)/365.25),0 &";"&ROUNDDOWN(MOD((TODAY()-A1),365.25)/365.25*12,0) A1 contains the persons birth date -- dcronj ----------------------------------------------------------------------- dcronje's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=528 View this thread: http://www.excelforum.com/showthread.php?threadid=26285 Hi, Try =DATEDIF(A1,...

Rows of data, using status columns to sort data to pie chart on another worksheet tab
Hi there, 1. I have 50 rows of data. There are many columns, but, there is one status column that I use to group the rows of data into relationships. I would like to build individual charts based on the status column. So, if I say these items are part of "Group A", information from those rows that are "Group A" will automatically show up on a chart on the "Group A" worksheet tab pie chart. I will have "Group A", "Group B", "Group C" pie charts on seperate worksheet tabs. Now, I want to move an item from "Group A" to ...