+ Reply to Thread
Results 1 to 7 of 7
Discussion: General Forums / Hardware & Software Discussion - Excel Insanity - So, I've got some logs for a monthly deal. This whole spreadsheet consists of separate
-
05-31-2007, 01:19 PM #1
- Join Date
- Jan 2004
- Location
- Houston, TX
- Age
- 29
- Posts
- 4,799
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.
-
05-31-2007, 01:50 PM #2
- Join Date
- Feb 2007
- Posts
- 525
Re: Excel Insanity
try ='1'!$G$17
The $ should fix the column/row number in formulas
-
05-31-2007, 01:55 PM #3
- Join Date
- May 2007
- Posts
- 2,015
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.
-
05-31-2007, 02:06 PM #4
- Join Date
- Jan 2004
- Location
- Houston, TX
- Age
- 29
- Posts
- 4,799
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.
-
05-31-2007, 02:14 PM #5
- Join Date
- Jan 2004
- Location
- Houston, TX
- Age
- 29
- Posts
- 4,799
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......
-
05-31-2007, 02:16 PM #6
Re: Excel Insanity
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.
Originally Posted by TheFeniX
A captain of the most awesome IHS ever and remember to burn before you pillage.
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 call us you are in need of serious psychiatric attention. We are available 24/7 at 1-973-409-3277, please scream into the phone as soon as it's answered, we will identify you as a catman follower from that and direct your call to someone who can attend to you immediately.
-
05-31-2007, 02:20 PM #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)
DBLast edited by Dick Blonov; 05-31-2007 at 07:16 PM.
|TG-6th|Blonov
«That looks like a really nice house except for that horrible bathroom.» Donrhos
PR:How to get started | Teamspeak | Banned ? Kicked ? | The 6th Devils Brigade
Reply With Quote
Thread Information
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
Bookmarks