sheet reference in formulas

    Is there a way to reference sheets by their sheet name,ie, sheet1, sheet2, etc, instead of their tab names? I need to do this in a formula, not in VBA. The formula is as follows: =SUMPRODUCT(($A18=INT(Mo.2!$A$2:$A$100))*(Mo.2!$G$2:$G$100)). I want to be able to change the "Mo.2!" designator to the underlying sheet name, in this case sheet3.


    Thanks

    Re: sheet reference in formulas


    Ummm.. refering a sheet by other than the Sheet name, I don't think it's possible..


    Well, what do you want to achieve, by this, prolly some altenatives can be suggested..


    HTH.

    Re: sheet reference in formulas


    Here' goes. My first sheet is my goal sheet with dates in collumn A. The dates are divided into days for a whole year. This column of dates is not static and can be changed according to a starting date input by the user. The workbook also has 12 additional worksheets that are named for the months of the year starting with the date supplied by the user when first starting the project.


    When a user starts the workbook, he supplies a starting date of his/her chosing. Let's say the user picks October 16, 2005. When the date is entered a VBA routine renames the 12 additional sheets according to the beginning date, in this case October - September.


    The monthly sheets are used to log hourly reports for the month. One day may have several reports, another day may have only one, while another may have no reports. Now I need a way to not only sum the hourly reports for each day and return that value to the goal sheet, but I also need that method to be flexible, so when the user starts over with another date and changes all the names of the worksheets, the formula to sum the hourly data still works even though the month tab for the sheets may be different.


    Sorry I can't post a sample because the extra sheets will exceed the 45 KB limit.


    I was just thinking that if there was a way to reference the sheets in order of the tabs, that may work. Right now the tabs read : Goal, May. June, July,etc. If there is a way to reference them as second sheet, third sheet, etc, that may do the trick.

    Re: sheet reference in formulas


    you can reference sheets by there "internal" number, e.g. sheets(3). The internal number stays the same, even after renaming the sheet (tab)

    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

    Re: sheet reference in formulas


    Just remember that, although the internal number stays the same after re-naming the worksheets, moving the worksheets disrupts the internal numbers


    Bill

    Re: sheet reference in formulas


    Could I get an example of the syntax to be used in a formula (not VBA) please? Just make up any formula and give me one ref to sheet1 and one for sheet2.


    BTW, the sheets will never be moved around, just renamed.


    Thank you guys


    Eric

    Re: sheet reference in formulas


    As far as I know you can't use the sheet's CodeName in a worksheet formula.


    Have you tried using INDIRECT?


    Your macro could populate cells with the required (new/changed) sheet names and then those cells can be referenced in the INDIRECT formula.

    Boo!:yikes:

    Re: sheet reference in formulas


    Hi x2ego,


    If it is not in code, then :


    =Sheet2!C9+Sheet2!C5


    will become:
    =Renamed!C9+Renamed!C5


    if you change the name of the Sheet2 tab to Renamed. If you are not working in code, the formulas will change to suit the tab names.


    Bill

    Re: sheet reference in formulas


    Hi Ranger:


    When I use this notation (sheet1!, sheet2!, etc) on my current workbook Excel opens up an Explorer window with "Update Values: sheet2" heading. When I click on the current workbook and select the Mar sheet (which should be sheet2), the formula changes to: =[sheet2]May!A1+[sheet3]Jun!B1 (it also asked me to rename sheet2.)


    I tried this with a brand new workbook in which I renamed the tabs on the second sheet to Jan and the third tab to Feb. Same thing happens as far as asking me to update the values with the results: =[sheet2]Jan!A2 + [sheet3]Feb!A1.


    Seems to me that once I rename the tabs on the sheets, I cannot reference them as sheet(123...) again in a formula.


    I am going to attach a sample workbook to show why I am trying to do this and maybe someone can find a workaround. Wow, just made the size limit :)

    Re: sheet reference in formulas


    Hi Bill:


    I wish it were that easy, but...if you look on sheet2(Feb)and sheet3(Mar), you'll notice that each day can and in this example does have more than one row of points data. This data has to be totaled for the day and then brought to the correct date on sheet1 all while being able to keep the month tab a variable in case the next user needs to start in a different month.



    Wait, I just tried something in the direction of your suggestion...I'll get back to you in a bit...


    Eric

    Re: sheet reference in formulas


    Hi Bill:


    I've been on a small vacation and have just gotten back to things here at home. Thanks for the help on my problem. Now if I could just make the page do the calculations without the command button, but every time I flip to the page or open it up?


    I think I may have actually done something right myself. I stuck your trigger code into the worksheet activate section which runs your formula every time I access the worksheet.


    Thanks again


    Eric

    Re: sheet reference in formulas


    I'm not sure if this solves your problem, but on the Sheet1 page for the Points column you could use a formula like: =SUMIF(INDIRECT(TEXT(A11,"mmm")&"!A:A"), A11, INDIRECT(TEXT(A11,"mmm")&"!D:D")) where A11 is the date column. Notice that is the date changes to a different month, the formula will automatically get the information on the new sheet.

    Re: sheet reference in formulas


    This is just what I was looking for. Thanks so much, and also many thanks to everyone who responded to my query.


    Just so that I can apply this to other data, would you walk me through the line and tell me how it does what it does?


    Also, would you use something similar to count the number of times a day that points are entered for a given day? For example, if the user entered points received six different times for a day, can this formula be changed a bit to bring this sum total for the day to sheet1?


    Very happy...


    Eric

    Re: sheet reference in formulas


    Hi Eric,


    I have commented the Code in Module1, which runs every time the Worksheet is Activated. If you look at the Code for Sheet1, the Worksheet_Activate Event code runs the Macro called Totals in Module1.


    I have changed the Code so that the Formulae go into Column D of Sheet1, I presume this is what you want? and I have added a couple of lines at the bottom of the Code, to remove the Formulae in ColumnD and clear Columns G&H. If you don't want to do this, then just remove the two lines of Code.


    I hope my explanation helps you.


    Bill

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login