Sheetnames in formula

I have 3 sheets (named 2001, 2002, 2003 representing 
different years) and a sheet named variance.  All have 
identical row/column headings.

In the sheet variance, I'd like to supply names of two 
sheets and calculate the variance between them.  I could 
create a different sheet for each variance (2003-2002, 
2002-2001, 2003-2001) but I don't want to do that.

Any ideas?
0
anonymous (74720)
6/30/2004 7:36:50 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
417 Views

Similar Articles

[PageSpeed] 6

Hi Frank

With 2002 in A1 and 2003 in A2, this formula sums their cell C7:
=INDIRECT(A1&"!C7")+INDIRECT(A2&"!C7")

HTH. Best wishes Harald

"Frank" <anonymous@discussions.microsoft.com> skrev i melding
news:23b5701c45ed9$96fec650$a301280a@phx.gbl...
> I have 3 sheets (named 2001, 2002, 2003 representing
> different years) and a sheet named variance.  All have
> identical row/column headings.
>
> In the sheet variance, I'd like to supply names of two
> sheets and calculate the variance between them.  I could
> create a different sheet for each variance (2003-2002,
> 2002-2001, 2003-2001) but I don't want to do that.
>
> Any ideas?


0
innocent (844)
6/30/2004 7:50:51 PM
PERFECT!  Thanks.

>-----Original Message-----
>Hi Frank
>
>With 2002 in A1 and 2003 in A2, this formula sums their 
cell C7:
>=INDIRECT(A1&"!C7")+INDIRECT(A2&"!C7")
>
>HTH. Best wishes Harald
>
>"Frank" <anonymous@discussions.microsoft.com> skrev i 
melding
>news:23b5701c45ed9$96fec650$a301280a@phx.gbl...
>> I have 3 sheets (named 2001, 2002, 2003 representing
>> different years) and a sheet named variance.  All have
>> identical row/column headings.
>>
>> In the sheet variance, I'd like to supply names of two
>> sheets and calculate the variance between them.  I could
>> create a different sheet for each variance (2003-2002,
>> 2002-2001, 2003-2001) but I don't want to do that.
>>
>> Any ideas?
>
>
>.
>
0
anonymous (74720)
6/30/2004 8:11:56 PM
Reply:

Similar Artilces:

Formulas #24
Trying to create a work sheet that will seach through entries for the greatest value One way: =MAX(A1:A1000) change the cell reference to suit. In article <4D85A1A0-6894-4177-9A4B-229EAB7954CD@microsoft.com>, bubba <bubba@discussions.microsoft.com> wrote: > Trying to create a work sheet that will seach through entries for the > greatest value ...

Date Formulas
I am trying to find a way to set a start and finish date based on other tasks. I have 3 design tasks 18, 35 and 40. I would like to create a single "milestone" task that looks at these tasks and determines what the first start date is and what the last finish date is? For example: Task 18 has a start date of 4/1/2010 and a Finish date of 4/30/2010 Task 35 has a start date of 4/15/2010 and a Finish date of 5/15/2010 Task 40 has a start date of 4/23/2010 and a Finish date of 5/29/2010. I would like to create a task called "Milestone - Design" and look ...

special account numbers different formulas macro???
I have a worksheet that has 1000 account numbers. Most of the account numbers have a simple formula, although 25 of them use a different formula because they are given a discount. My account numbers are 8 digits long and are in column A. My formula is in column J. So I've poplulated all of column J with the formula that is correct for most of the accounts. This is what I'm trying to accomplish. I want a macro that looks for specific account numbers, not a range. Then I want it to plug in the new formula. Any advice. Thanks. ------------------------------------------------ ~~ Messa...

Apply a formula in cell with a formula massively
Hi there, would like to get your input/hints on how to solve the following: In a series of cells in a row, I already have a formula calculating a division among two other columns. The rows are many (more than 500). But now I need to apply a ROUND formula, on top the existing ones. ex. cell C5 = A1/B1 and I want to massively for all rows/cells to make them: ROUND(A1/ B1,3) Anyone who can help? THanks M On 3/4/10 9:58 AM, mario wrote: > Hi there, > > would like to get your input/hints on how to solve the following: > > In a series of cells in a row, I a...

Formulas on double click
Hi If I double click on a cell that contains a formula the formula appears instead of the result. How do I deactivate this functionality? On Sat, 8 Oct 2011 06:50:58 -0700 (PDT), peter@excel-vba.com wrote: >Hi > >If I double click on a cell that contains a formula the formula >appears instead of the result. > >How do I deactivate this functionality? Double-clicking on a cell puts you into edit mode. That is why you see the contents of the cell, which may be a formula or a constant value. What would you like to happen when you double-click on a cell? peter@excel-vba.c...

How To Simplify Formula with SUMIFs
Code Double Feature Senior Pass Redemption Hubble COMP- staff Double Feature General IMAX- General Pass Redemption Hubble COMP Companion Gen Companion Snr Double Feature Child Double Feature General IMAX- Child* IMAX- Child* IMAX- General IMAX- General IMAX- Senior IMAX- Senior Pass Redemption Hubble Pass Redemption Hubble 2 for 1 DF- General 2 for 1- General COMP Companion DF Gen Csc- Corporate Single $5 Daycare- Adult Daycare- Child IMAX- Child* IMAX- Child* IMAX- General IMAX- General Pass Redemption Hubble COMP Companion DF Chd Double Feature Child Double Feature General Double Feature Gen...

Why does formula automatically change to its result
OK, I'm stumped. When I enter a formula into a cell, for instance; (d27*d28/144)*d23 and then enter a number into cell d23, the formula gets replaced by th value of the result automatically. I've searched the help files and found no setting that does this. I reminds me of an old question I've had lingering since I've used exce and that is why sometimes when you enter a math calculation, fo intance; =25/5, the number 5 is entered into the cell instead of th formula. Anyone -- Message posted from http://www.ExcelForum.com Is this a spreadsheet you originally created. Possib...

Excel 2003-Sp2
Resize annoying formula box, when e.g. Collumn A has very large text in fields, and the formula field drowp down and covers the 1st row or more. This were not an issue in Excel 2000 and in Excel 2007 you can hover the mouse or use CTRL + SHIFT + U and choose number of lines / the height, and set it back to 1 line. But in Excel 2003 you are stuck ..... arrrggggg. In the follow article it is describes how to remedy this in Excel 2007 - please help with a solution for Excel 2003. http://office.microsoft.com/en-us/excel/HA100818641033.aspx /Alex I think xl95 through xl2003 all behaved...

Formula #19
What is the formula to divide a number in a cell by the number of months to date in the current year? One way: =A1/MONTH(TODAY()) In article <1137109107.464981.10720@g49g2000cwa.googlegroups.com>, "Jim" <jim.hogan@whfinancial.com> wrote: > What is the formula to divide a number in a cell by the number of > months to date in the current year? That is the formula I've been using, or =A1/MONTH(NOW()), however bot are simply carrying the number in A1 to the new cell. Any other ideas? I think I'm missing something. It's January, so you are divi...

Help With Formula #4
I have a formula in a spreadsheet that works fine =IF(C17=0," ",(C15^2*0.4465*C15/12*PI()*C17/5000)*3.785) This returns the correct answer and if no entry in C17 is stays blank (as I want it to) What I want it to do is return HALF the answer if there is an entry of a letter M or m in say Cell C13? So if nothing in C13 the normal answer appears and if an M in C13 then half of that answer Many thanks Frenchy Frenchy, Does this do what you want??? =IF(C17=0,"",IF(C13="m",((C15^2*0.4465*C15/12*PI()*C17/5000)*3.785)/2,(C15^2 *0.4465*C15/12*PI()*C17/5000)*3...

Insert Row Under current row (with formulas/formatting)
I've got a sheet that gets appended to regularly but have to do a lot of fiddling to make sure the formatting is correct, calculated fields get added and chart series ranges collect all the data every time I add a new row. The insert (row) command seems to take care of all of this but it inserts the empty row above the current row. As I'm always appending data to the sheet, I would prefer this to be below the current row. I've tried adding a dummy row under all the data containing the formulas and formatting I need but unfortunately I get formula errors and it screws up my chart....

Nested if's limit and Named formulae
This is the second time trying to get an answer, with more detail than last time, so here's hoping I have a cost estimate spreadsheet, comprising ten or so worksheets, the first one of which (Details) is a summary worksheet with a changes log at the bottom of it. This log contains the values Draft1-10 in column B, the Date of change for each in column C, the worksheet affected in column D and the nature of the change in column E. Further up the worksheet is a field that I want to populate to show the current version. I can pick this up by checking to see whether there is a date in colu...

If formula with Yes or No
I am working with 4 column of yes and no's that I need to tally at the end. If there is one no in any column, I need the final colum to say No. I can't seem to get the If function to work on it or any other formula. HELP! =IF(COUNTIF(A1:D1,"No")=1,"No","Whatever answer you want if the number of 'No's is other than 1") -- David Biddulph "skane2" <skane2@discussions.microsoft.com> wrote in message news:E5CBAC8F-B82C-4E98-8FE3-375B74435E81@microsoft.com... >I am working with 4 column of yes and no's that I nee...

Formula for transposing Row Values to Col Values
Excel2003 ... WS1 ... Row Range L2:Z2 ... Contains Text WS2 ... Col Range B3:B17 ... I wish formula to populate Text from WS1 ==> I know I can do this with Copy/Paste/Special/Transpose ... However, I wish a Formula to do it as I need to apply in additional places ... Thanks ... Kha One way... With Values in A1, B1, C1... use this formula in A3 and drag down =OFFSET($A$1, 0, ROW() - 3, 1, 1) Note that this function is volatile and as such will add a lot of calculation overhead to your spreadsheet. If you use a lot of these your performance will suffer... -- H...

Copying and adjusting formulas
Hi Firstly thanks for many tips picked up in this ng - I've learnt a lot. Copying and adjusting formulas 26 sheet workbook (Excel 2000) with a sheet for each weeks figures broken down departmentally and totalled for the week. From sheet 2 onwards the sheet has year to date being current weeks figures + previous year to date. This was originally in .123 and as you copied from one sheet to other sheets the formula adjusted the sheet name - in Excel it keeps the formula without adjustment. I find this weird (and very long winded as each sheets formula will need changing). Am I missin...

avg formula and blank cells
What is the proper way when using the AVG function with cells formatted as numbers, to fill a blank cell so it won't make the avg lower, but rather be ignored in the AVG function? Thanks! --Randy Starkey __________ Information from ESET NOD32 Antivirus, version of virus signature database 4080 (20090515) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com Excel ignores blanks cells when averaging. If you mean ignore cells uncertain a certain value. In Excel 2007, use AverageIf. In earlier versions you need to construct the formula using SumIf ...

Only numbers formula
Hi Friends, I am in requirement of a formula for extracting only last seven (or less) numerical digits from a reference. Ex - 1 - if A1 consists 112233help573847 - I need "573847". 2 - if A1 consists 112233help573 - I need "573". 3 - if A1 consists 112233help - I need "112233". 4 - if A1 consists 112233 - Ineed "112233". Any help is appriciated. Thanks, Thyag > I am in requirement of a formula for extracting only last seven (or > less) numerical digits from a reference. > > Ex - > 1 - if A1 consists 112233help573847 - I need "...

How do I test whether a defined sheetname exists in a closed workbook?
Hi... I've been searching all over and can't find anything on this particula challenge so I thought you guys might be able to help! I'm wondering if anyone knows how to test whether (or not) a specifi sheetname (e.g. "TestSheet") exists in a closed workbook? (I'd really prefer not to have to open the workbook if at all possibl as its one step in an intensive consolidation process from upto 10 workbooks.) Thx muchly! ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http:...

Formula to move cursor when 2 cells equal
Needing a cell formula that moves the cursor to the next row & 1st column when two cells equal. For example when the value in b2 equals the value in c2, the cursor is moved to a3. Thanks , mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24581 View this thread: http://www.excelforum.com/showthread.php?threadid=385378 Hi, Mike. I'm sorry, but this doesn't sound like a reasonable thing to do. Can you describe your workbook a little better--what ...

cant develop a formula
trying to create a scoring program for bowling, block a would be bowlers name the next 7 blocks would be scores, block 8 would total the scores. can't figure the formula to get excel to do that. Also is it possible for the workbook to order the entries by total score (block 8) so when Im done I can "push a button" and have my bowlers ranked from high to low score?? Hi for totalling use =SUM(B1:H1) and you can use 'Data - sort' sor sorting -- Regards Frank Kabel Frankfurt, Germany Bowlin Guy wrote: > trying to create a scoring program for bowling, block a would b...

How to change a formula reference?
I need to create a formula that references a range of cells. The range is supposed to be determined by what the user enters into another cell. Can a formula have a variable in it that will grab a number from another cell? For instance: =COUNTIF(N[B18]:N[B18+B18],"Y")? Where you see B18 the user has entered in a number, we will say it was 5. I need the function to reference the cells in the N column from N5 (cell B18 has a 5 in it) to N10 (B18+B18 or 5+5). I appreciate any help you all can give me. Thanks so much in advance. Brockettb Hi! Assume you enter in cells: A1 = 5...

Need formula help
I'm using Excel 2002 with SP3 and am having difficulty with the formula in one cell. All of the steps of the formula need to reside in cell D14 so the result is put there. May go through up to 20 steps to arrive at result, depending upon current date, which is present in cell C3 as =TODAY(). Cells O4 through O23 have dates 1/21/2006 through 8/21/2007. Contents of these cells are formatted to be raw five digit number. Cells P4 through P23 have differing dollar amounts, from $12,727.27 down to $0.00. Basic formula: =IF(AND(C3>=O4),(C3<O5),-P4,Go to next step) =IF(AND(C3>=O5)...

formula.
Hello, I'm in need for a formula, if in cell A for example i introduce numbe 2 and in cell B number 3, cell C would give me a total of 5. But if fo example i write '23' in the same cell (a) i want cell b to give me total of 5, so even if a write 53 it has to count it separately 5+3=8 can you please help me with this formula, as i'm badly stuck with it. thank you waiting eagearly Moti -- Motilull ----------------------------------------------------------------------- Motilulla's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3715 View this thre...

can you hide rows with a formula?
on my last sheet there should be an overview of the data selected by the user. If the user doesn’t select all the blocks with data “because he thus not need that particular block”. There will be empty rows between the information on the last sheet! I need all the info under each other, so that the user will have a short clear overview that will fit on 1 page to print. (this should be all automatic). Is this possible by hiding the empty rows with a formula? Or is there another way? (The sow called “empty rows” do contain formulas that hide the data blocks that the user thus not need)...

Is there a formula for this?
I have an Excel spreadsheet with 2 columns. The first column has a Box number (example: 01245). The second column has the file numbers that are in that particular box (example: 821-873). So, in other words, box #01245 contains files 821, 822, 823, 824, etc. all the way to 873. Obviously it's difficult to find a particular file number if we don't know what file number range it's in. Rather than list each file number separately (Example: first column I would list the box number and then in the 2nd column list the first file #, then I would go down to the next row and ...