Announcement

Collapse
No announcement yet.

Excel Insanity

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel Insanity

    So, I've got some logs for a monthly deal. This whole spreadsheet consists of separate sheets (one for each day of the month) and a summary table.

    Now, for one of the summary cells, I have this formula: ='1'!G17

    This makes a call to one cell from the "Day 1" spreadsheet. Now, I've got to do this SAME cell for sheets 2-31 for the summary. If I copy and paste the formula down the list, excel trying to be smart, makes it go:

    ='1'!G17
    ='1'!G18
    ='1'!G19

    and so on. That's obviously not what I need. I need excel to go down the list like this:

    ='1'!G17
    ='2'!G17
    ='3'!G17
    ='4'!G17

    and so on.

    Any ideas? I can go at my current rate, but I'm looking at many more hours of work as there's about 10 calls per sheet and 31 days. It adds up.

  • #2
    Re: Excel Insanity

    try ='1'!$G$17

    The $ should fix the column/row number in formulas

    Comment


    • #3
      Re: Excel Insanity

      I don't think the methods of autocomplete will work the way you want it to, here. You could place a string to lock the row value (!G$17), but that won't help the sheet page counter.

      You might check for arcane functions or methods; there might be a built-in way to sum the same cell through a range of pages. Excel needs to do something useful to justify it's weight, right?

      Sorry I'm not of more help; I don't use multipage spreadsheets with any frequency.

      Comment


      • #4
        Re: Excel Insanity

        I found a cop-out: by doing one row manually, then copying and pasting, it keeps the '1', '2', '3', etc numbering and only messes up the letter of the second variable. Ex: '4'!G17 becomes '4'P17. But the number stays the same.

        It works and is faster. Oh well.

        Comment


        • #5
          Re: Excel Insanity

          Ok, another round-about. I pasted in the formulas, then did a find/replace to put in the numbers I needed.

          Good Times......

          Comment


          • #6
            Re: Excel Insanity

            Originally posted by TheFeniX View Post
            I found a cop-out: by doing one row manually, then copying and pasting, it keeps the '1', '2', '3', etc numbering and only messes up the letter of the second variable. Ex: '4'!G17 becomes '4'P17. But the number stays the same.

            It works and is faster. Oh well.
            Like JAMerica said, to keep the column and rows from changing, put a $ before the one that you don't want to change Ex: '4'!$G17, Then copy and paste and the columns will not change.
            Big-eye101: "A true catman post a day keeps the bad mood away"

            Please do not take any posts made by Catman seriously. If you begin to take his posts seriously, please seek psychiatric attention.

            Comment


            • #7
              Re: Excel Insanity

              This formula will copy properly:

              =INDIRECT("'"&A1&"'"&"!A6")

              Just paste that in the cell where you want your summary.

              Set A1 to A31 on the summary sheet to = 1 to 31 (the name of the daily sheets).

              A6 is the position on the daily sheet that you want to add up (like daily total I guess). So if you are summarizing multiple daily totals, just replace that A6 with whatever cell contains that total and leave the rest intact.

              If you want to call your sheets «Day 1» to «Day 31», change the formula to:

              =INDIRECT("'"&"Day "&A1&"'"&"!A6") or fill A1 to A31 with whatever sheet names you have (Day 1, Day 2, etc).

              Is that what you were looking for ?

              ( note the highlighted double quotations marks in red in the first example, single quote marks are bold)

              DB
              Last edited by Dick Blonov; 05-31-2007, 06:16 PM.

              «That looks like a really nice house except for that horrible bathroom.» Donrhos

              | |





              Comment

              Connect

              Collapse

              TeamSpeak 3 Server

              Collapse

              Advertisement

              Collapse

              Twitter Feed

              Collapse

              Working...
              X