Referencing worksheet name in formula

I am trying to find an elegant way to refer a worksheet name in an easily
copied formula. In this particular example, I have a sequence of
month-specific worksheets, eleven of which reference the month prior to
bring up YTD figures. I've just become acquainted with the INDIRECT function
but that seems a bit cumbersome, as it entails either modifying the cell
reference portion of the formula (e.g., =INDIRECT($A$37&"A16")+D16, where
$A$37 references the previous month) or requires a reference cell on each
sheet for each desired referenced field. I'd like to find a reference that I
can easily drag and copy down a column and duplicate to each month's sheet
without all the supporting reference cells (e.g., =PreviousMonthReference!
D16+D16), where the reference is either a simple equation (e.g.,
CurrentSheet -1 if the months are assigned numeric names) or a single lookup
table (e.g., =LOOKUP(CurrentSheetName,Table,2)).  Thanks for any
suggestions.


0
sndchaser (2)
2/15/2004 2:13:15 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
600 Views

Similar Articles

[PageSpeed] 24

Hi
one way:
If you have named your worksheets with a month name you can use the
following formulas:
1. To get the current worksheet name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
place this in lets say cell A1

2. To get the previous month name:
=TEXT(DATEVALUE("1-" & A1 & "-2004")-1,"MMMM")
-> you have to adapt the DATEVALUE parameter to your regional settings
(this works for German date settings: "MMMM-DD-YYYY").
Lets say you placed this formula in B1

3. Now you can use INDIRECT with the result from B1:
=INDIRECT("'" & B1 & "'!A16")+D16
note the multiple apostrophes (" ' " at the beginning and " ' ! in the
middle)

Of course you can combine this to one formula:
=INDIRECT("'" & TEXT(DATEVALUE("1-" &
MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) &
"-2004")-1,"MMMM") & "'!A16")+D16


--
Regards
Frank Kabel
Frankfurt, Germany

Alan D. wrote:
> I am trying to find an elegant way to refer a worksheet name in an
> easily copied formula. In this particular example, I have a sequence
> of month-specific worksheets, eleven of which reference the month
> prior to bring up YTD figures. I've just become acquainted with the
> INDIRECT function but that seems a bit cumbersome, as it entails
> either modifying the cell reference portion of the formula (e.g.,
> =INDIRECT($A$37&"A16")+D16, where $A$37 references the previous
> month) or requires a reference cell on each sheet for each desired
> referenced field. I'd like to find a reference that I can easily drag
> and copy down a column and duplicate to each month's sheet without
> all the supporting reference cells (e.g., =PreviousMonthReference!
> D16+D16), where the reference is either a simple equation (e.g.,
> CurrentSheet -1 if the months are assigned numeric names) or a single
> lookup table (e.g., =LOOKUP(CurrentSheetName,Table,2)).  Thanks for
> any suggestions.

0
frank.kabel (11126)
2/15/2004 3:01:09 PM
 Alan
             consider the following formul

                =INDIRECT("January"&"!"&CELL("address",A9))+A

 it  adds in the current cell the contents of cell A9 of the current sheet with the contents of cell A9 in another sheet called January.  This formula can be copied to another cell without editing it. You can also store the value "January" in one cell (e.g. A1) and change the formula into

                =INDIRECT($A$1&"!"&CELL("address",A9))+A

Then you can duplicate a sheet to create the next month and all you have to do is to change the name of the previous month sheet in cell A1 of the new shee

If you want to add the column C of the same row in the previous month with column A of this month, the formula should oubviously b

                     =INDIRECT("A1"&"!"&CELL("address",C9))+A

Hopping it can help
Jacque
     ----- Alan D. wrote: ----
    
     I am trying to find an elegant way to refer a worksheet name in an easil
     copied formula. In this particular example, I have a sequence o
     month-specific worksheets, eleven of which reference the month prior t
     bring up YTD figures. I've just become acquainted with the INDIRECT functio
     but that seems a bit cumbersome, as it entails either modifying the cel
     reference portion of the formula (e.g., =INDIRECT($A$37&"A16")+D16, wher
     $A$37 references the previous month) or requires a reference cell on eac
     sheet for each desired referenced field. I'd like to find a reference that 
     can easily drag and copy down a column and duplicate to each month's shee
     without all the supporting reference cells (e.g., =PreviousMonthReference
     D16+D16), where the reference is either a simple equation (e.g.
     CurrentSheet -1 if the months are assigned numeric names) or a single looku
     table (e.g., =LOOKUP(CurrentSheetName,Table,2)).  Thanks for an
     suggestions
    
    
     
0
anonymous (74722)
2/15/2004 3:26:06 PM
Reply:

Similar Artilces:

Importing from one CRM to another by name not display name
Hi All, Im trying to export and import the content of a custom entity. There are numerous fields with the same display name, but have different true names, making them unique. However, as you are probably aware, the export / import process by default uses the Display name as the column headers in the import csv. Is there any way to have it use the true field names instead?! Regards, DiDGE ...

excel file name is too long
I am wondering if there is a bug in excel 2000 that won't allow for long file names. It seems that a file name over 51 causes an error when trying to open a file, yet the same file name in word works fine. Has anyone noticed this before? Thanks, Dennis Sometimes one of these works: Tools|Options|General|Ignore other applications (uncheck it) --- or --- Close Excel and Windows Start Button|Run excel /unregserver then Windows Start Button|Run excel /regserver The /unregserver & /regserver stuff resets the windows registry to excel's factory defaults. ==== I bet it was sp...

Unprotect Worksheet Prompt
I'm running Excel 97 (sorry!) and I have built a workbook which obtains inputs from a number of users. All sheets are protected with only the input cells unlocked. How do I switch off the prompts when the workbook opens so that macros enable automatically and users aren't prompted to unprotect 12 worksheets? Some users are not very savvy, and struggle to hit "enable", then ESC 12 times. >>How do I switch off the prompts when the workbook opens so that macros >>enable automatically That's not possible since it would leave users with no way to stop malicious ...

Download from external database spread over multiple worksheets.
I need to download over 300,000 records from a main frame computer Excel 2000 has 65,536 rows on a worksheet.. Is there any way I can download all 300,000 records from an External Data Query. at one time and distribute the data over multiple worksheets??? If so how? Thanks.. Hi this KB article describes a macro which splits the textfile into several worksheets http://support.microsoft.com/default.aspx?scid=kb;EN-US;q120596 Though written for Excel 95, changes for Excel 97+ are noted -- Regards Frank Kabel Frankfurt, Germany AgaPSDIVER wrote: > I need to download over 300,000 records fr...

Dates in Formula
I want to create an IF statement that says if a cell value is equal to a set date then "True" otherwise "False" The formula used is: =IF(A12="01/04/2004", "TRUE", "FALSE") The value in A12 is 01/04/2004 (created by formatting cell to dd/mm/yyyy) This returns "False" regardless of what I put in A12 I am using Excel 2000 on an XP Pro O/S. Any help greatly appreciated. Thanks On Fri, 14 Jan 2005 03:09:03 -0800, John G <JohnG@discussions.microsoft.com> wrote: >I want to create an IF statement that says if a cell value is equ...

Smart use of .Activate; .Select; .Copy with Sheets(1) va Worksheet
Using 2003 First, when is it necessary to: Activate(to ultimately Select)Worksheets(myWorksheet).Select must I first do .Worksheets(myWorksheet).Activate then .Worksheets(myWorksheet).Select then .Worksheets(myWorksheet).Copy which approach is better? must I first do .Sheets(myWorksheet).Activate then .Sheets(myWorksheet).Select then .Sheets(myWorksheet).Copy with "Sheets" can I use either an: Alpha variable or "Actual W/S ...

Hiding worksheet row and column identifiers
Is there a way to hide the row numbers and column letters on my screen, or to control their formatting (for example, change their color to pale grey)? ---------------------- Thanks in advance. To hide, Tools>Options>View and uncheck Row and Column headers -- HTH RP (remove nothere from the email address if mailing direct) "salliesatt" <salliesatt@discussions.microsoft.com> wrote in message news:A6DED685-A3F2-49BD-ADCC-96551A95363F@microsoft.com... > Is there a way to hide the row numbers and column letters on my screen, or to > control their formatting (for...

Print addresses from an exel worksheet to envelopes
I am trying to print addresses from an excel file on envelopes. I know how to do the labels, but not sure how to print onto envelopes. Most users with address in Excel use Word for mail merge. It is easy to tell Word to get its data from an Excel worksheet. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Sherry Lee" <SherryLee@discussions.microsoft.com> wrote in message news:C7AE22A2-A765-429A-8F8B-478D0FD10216@microsoft.com... >I am trying to print addresses from an excel file on envelopes. I know how >to > do the labels, bu...

Referencing merged cells
I'm having trouble referencing a merged cell in another workbook. Say I merge cells A1 to C1 in workbook 1. When I make this the active cell, the Name Box says "A1". When I go to another workbook (say, workbook 2), go to cell B1, type "=" and then go to the merged cell and select it, I get 'workbook1'!$A$1:$C$1 as the formula and, of course, the "#VALUE" error. I've successfully tried typing "=sum(" without the quotes hen clicking on the contents of the cell and then adding the ")" and it works O.K. but there should be an easi...

Names and numbers in different cells
I am trying to add the totals given in Columns C, E, G etc etc of each suburb listed in columns B,d,F etc etc. When the formula finds the total is also needs to give me the name in column 1 for that area. (Two areas given in example but not all week) Eg: Find "PNS Sound Hutt" in column B, & column D, add 2.0 (from column c) & (1 from column e) then show John. I need to know how often each person visits each suburb. Name Suburb No Suburb No A B C D E etc etc John MONDAY ...

Problem with sum across worksheets
Hi, I have several applications where I keep monthly data on tabbed worksheets, labled Jan07, Feb07, Mar07... I also add a worksheet I usually call RangeEnd that acts to stop sums for ranges. It has been my practice to make a year end worksheet that would have the same shape data structure and place in each cell the formula "=sum(Jan07:RangeEnd!A1)" and copy it into all the appropriate cells for which I want summed data. This seems to work just fine so long as the sells I am summing are not themselves sums. For example, if cell C1 holds a value, say 10. The C1 cell in th...

Help for formulas in spreadsheet
Let me first explain my problem. I have a booking form used by renters who rent my holiday villa, they book usually 1 or 2 weeks. The cost is dependent on the month so there are 4 price bands. So they could cross a price band ie have a different price for each week. I have used this forum in the past to get a couple of formulas to do most of the calculations, but as i don`t really understand the formulas when changes are required its a no go area for me other than the yearly price increase, I just change the figure (�s) behind the �s sign. All my changes are linked to dates and ...

how to name many columns
how do you name a column to extend over several columns in excel? just select the columns and put a name in the NAME box next to the formula box -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "ben" <ben@discussions.microsoft.com> wrote in message news:4D949A09-DB62-426C-BEF3-04C12A7C5FA3@microsoft.com... > how do you name a column to extend over several columns in excel? Hi Ben I read your question slightly differently to Don, who gave you a method of creating a named range. I interpreted you request as wanting to e...

Referencing another field if result is N/A
Hello, I have a situation where I am referencing data on another workbook. The reason behind this is the data in Column H can be entered incorrectly and often is. I am able to prevent this going forward but can not do so for the items already entered. I enter a formula via VBA, however, sometimes the result will be #N/A and if so I would prefer to leave the old value and just highlight it that it needs to be verified by hand. I am not sure if I can just "leave" the value as it has pretty much already been replaced. Due to this, I copy the column to a different colum...

Check my Conditional Format Formula
Hey guys- trying to get this thing to work, but, it's just staying white. Any ideas as to what's wrong with it? I have this inserted in the 'Formula Is' part of Conditional Formating- obviously without the =Green or =Red remarks. Any ideas? =OR(AND(BC2<=100000,BG<=0.7),AND(BC2>100000,BC2<=140000,BG<=0.75), AND(BC2>140000,BC2<=180000,BG<=0.8), AND(BC2>180000,BG<=0.85)) =GREEN =OR(AND(BC2<=100000,BG>0.7),AND(BC2>100000,BC2<=140000,BG>0.75), AND(BC2>140000,BC2<=180000,BG>0.8), AND(BC2>180000,BG>0.85)) =RED Thank you...

Worksheet doesn't permit F4 absolute referencing tool in formulas
I've never seen this before in a worksheet and can't find a Tool-Option that would change it. You can of course type the dollar signs in, but the F4 key won't toggle. Sure it's something simple, any help appreciated! -- ronthedog ------------------------------------------------------------------------ ronthedog's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26504 View this thread: http://www.excelforum.com/showthread.php?threadid=397725 ...

adding across worksheets
I have a workbook that has several worksheets in it. The first sheet is basically a membership roster. It contains names, social security numbers, membership category, how much their total dues are, and how many paychecks their dues will be deducted out of (most are 16, but it can vary). The other 16 pages in the workbook are for each payroll period. They list names, social security numbers, and how much was deducted during that pay period. Sometimes members are added or deleted during the year. I'd like to add a column in the first worksheet that shows how much they've paid to d...

Wanted -to know how to post FILE NAME and DIRECTORY at bottom of my worksheet
Who knows how to post the formula at the bottom of all my excel worksheets that will give the file name and the directory of my file??? thanks for a reply! =CELL("Filename",A1). file must be saved for the first time. Regards, Jon-jon "Pedro" <pdk1974@hotmail.com> wrote in message news:0b3d01c38f14$216fd180$a401280a@phx.gbl... > Who knows how to post the formula at the bottom of all my > excel worksheets that will give the file name and the > directory of my file??? > > thanks for a reply! In case you wanted to put full path including sheetnam...

This workbook is currently referenced by another workbook and cannot be closed.
The message is given when I try to close a workbook / kill Microsoft Excel. I'm using Microsoft Excel 2002 / XP. The spreadsheet includes a reference to another sheet that someone else referenced in another message here. I put the AddIn sheet in via: Tools, AddIns..., Analysis ToolPack - VBA. I've not referenced any function or procedure in the add-in. I get a Triangular Warning / Error ? It's got a graphic of a Yellow Triangle with an Exclamation Mark inside. The subject is the exact text of the warning. A Microsoft Search on the subject yielded nothing. Google yielded one ...

Worksheet 250
When we run worksheet 250 in to update items at store level it is also updating dynamic fields like quantity. The definition given in theh help file clearly states that 250 updates only static fields. Is this a setup problem or a bug in RMS. Sid There is a hotfix for this and it works fine in 1.3. Call MS and get the fix. Jason "Sid" <Sid@discussions.microsoft.com> wrote in message news:694A0418-AD13-4E9F-8B2A-1E3B44DC6D11@microsoft.com... > When we run worksheet 250 in to update items at store level it is also > updating dynamic fields like quantity. The definit...

Sending error 0x8000 4005, and my name not in From: field
Network admin, am stumped by recent issue with several co-workers. All using the same XP Pro OS install and Office 2K3 with current updates, but only 4 of us have experienced this problem: select a Distribution List to send a message to, but when message is sent, an immediate error is returned that the send failed. Error code given as '0x8000 4005.....'. Subsequent digging revealed that sent message did not have sender's name in 'From:' field, and our Exchange server never saw the message from the Outlook client (error internal to user's PC?) If all member a...

Linking information form one worksheet to another
Hi, i have a big problem i want to create a link between some columns in two seperate worksheets, so that when i type a name on the master sheet it would give me the required information.Dont know if i explained this right, lets say on the master sheet i have columns : Name,Sex and Height on columns A,D and BH respectively i want that if i type a person's name in worksheet 2 it should give me the results on columns A,C,F in that worksheet. Please help i need it asap for my director One way .. Assuming your "master" sheet is named: Master, with data in row1 down In Shee...

Copying Worksheet data
Hi, Is it possible to have data that is typed in a worksheet to be added to another worksheet of the same workbook. I have my inventory worksheet with pricing and other info needed. I also have another worksheet in the same workbook but all I need is the pricing and the description added to my second worksheet. Is there a formula to do this? Thank you, John =Sheet1!A1 Copy the formula down the column and the reference to row 1 will change to 2, 3, etc. -- Ian -- "John Bordieri" <chefjohn@southcountytavern.com> wrote in message news:ZKUBi.227057$BX3.158019@newsfe13...

referencing the cell above
Hi I have a kin a formula wich gets the value from the cell above and then add some. What I get is an list of values incrementing. When I delete one row in this list, all cells below 'crashes' with #REF!. What I need is that for the formulas to always look one cell up, even if I delete a row. Now it seems that if I delete Row8, then when Row9 become Row8 it reference to it self and then ov course goes bananas... Hope I made myself understandable ;-) Any ideas folks? -------- stuhag --------- Instead of using say =A19, use =OFFSET(A20,-1,0). This would go in A20, and as it does...

combine parts of many worksheets in many workbooks
I am looking for a way to combine the data from many worksheets in several (about 30) workbooks. My employer requires me to record my activity for each client using excel workbooks which has separate worksheets for each product. The activity is always recorded from row 16 starting in column B through to column G. Each row contains a mixture of entries derived from a list box, a manually entered date and a text comment. Column B is the client name from a listbax Column c is the client code entered by vlookup from the data in column B Column D is the event date which is entered manually Column...