If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Relative worksheet reference in 3-D formulas?
But if there's nothing that says it absolutely has to be the second sheet
then my advice was going to be exactly the same as Rob's. I'd name the second sheet 'start' and the third 'finish' or maybe just a and b, and then use as Rob suggested =SUM(start:finish!A1) or =SUM(a:b!A1) instead of references to a sheet2 or whatever the name would be Then just drag your new sheet each time between a and b and drag the old one out to the right of sheet b. Sheet1 will then only pick up data from any sheet between a and b. There will be no data on sheets a or b so all you will get is the data from your new sheet. Failing that take a look at the INDIRECT function and then in a single cell on your summary sheet put the name of the tab you are referring to. Use that cell in your formulas and then just change the sheet name in that one cell to refer to the one you want, eg if the name of your second sheet was in cell C1 on your summary sheet then you could use =INDIRECT(C1&"!A1") on your summary sheet to refer to cell A1 on whatever sheet has it's name in cell C1 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- wrote in message news:bMEEd.13008$B95.2034@lakeread02... Rob - The problem is that there is no "between" involved. It's always the second tab, and every quarter it has a new name. To invent some syntax, I'd want something like A1: = sheet[+1]!A1 from RC notation. ...best, Hash In article , "Rob" wrote: Hash, If the following was the formula in say cell A1 of Sheet1 (your summary), then inserting a new sheet between Sheet2 and Sheet3 would automatically be included in the summary sheet (Sheet1). =SUM(Sheet2:Sheet3!A1) Regards, Rob wrote in message ... Gang - I have a workbook with a summary worksheet at the first tab, and then a new worksheet inserted every quarter as the second tab. Earlier quarters are pushed to the right on the tabs. Is there a way to reference data on the second tab no matter what that second worksheet is titled? It changes every quarter. Right now I do a global replace manually via menu. Thanks in advance. ...best, Hash |
#2
|
|||
|
|||
Ken -
Thanks - I'll consider Indirect. It's volitile, and I won't like it, but it may be the best way to go. Thanks again. ....best, Hash In article , "Ken Wright" wrote: But if there's nothing that says it absolutely has to be the second sheet then my advice was going to be exactly the same as Rob's. I'd name the second sheet 'start' and the third 'finish' or maybe just a and b, and then use as Rob suggested =SUM(start:finish!A1) or =SUM(a:b!A1) instead of references to a sheet2 or whatever the name would be Then just drag your new sheet each time between a and b and drag the old one out to the right of sheet b. Sheet1 will then only pick up data from any sheet between a and b. There will be no data on sheets a or b so all you will get is the data from your new sheet. Failing that take a look at the INDIRECT function and then in a single cell on your summary sheet put the name of the tab you are referring to. Use that cell in your formulas and then just change the sheet name in that one cell to refer to the one you want, eg if the name of your second sheet was in cell C1 on your summary sheet then you could use =INDIRECT(C1&"!A1") on your summary sheet to refer to cell A1 on whatever sheet has it's name in cell C1 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- wrote in message news:bMEEd.13008$B95.2034@lakeread02... Rob - The problem is that there is no "between" involved. It's always the second tab, and every quarter it has a new name. To invent some syntax, I'd want something like A1: = sheet[+1]!A1 from RC notation. ...best, Hash In article , "Rob" wrote: Hash, If the following was the formula in say cell A1 of Sheet1 (your summary), then inserting a new sheet between Sheet2 and Sheet3 would automatically be included in the summary sheet (Sheet1). =SUM(Sheet2:Sheet3!A1) Regards, Rob wrote in message ... Gang - I have a workbook with a summary worksheet at the first tab, and then a new worksheet inserted every quarter as the second tab. Earlier quarters are pushed to the right on the tabs. Is there a way to reference data on the second tab no matter what that second worksheet is titled? It changes every quarter. Right now I do a global replace manually via menu. Thanks in advance. ...best, Hash |
#3
|
|||
|
|||
Hi Ken -
Well, I futzed with it all day. Couldn't use the bracketing worksheets because some of the data is textual, and I couldn't find one of the limited 3-D functions that handled that. I didn't use a straigtforward indirect method because I didn't want to populate more cells in the summary sheet, which is saved off as text and imported to a Lotus Notes database. So I tried J-Walk's SHEETOFFSET function (See his Excel 2003) and a vague memory of how I would do this years ago in XLM. That's what I settled on, after a lot of digging, since it seemed a bit faster. That's important--I can hit the head while this thing calculates and still come back to the hour-glass. ;-) The summary sheet is ~5400 rows by 14 columns. The idea remains to automate this as much as possible. I defined a name as SHEETARRAY = GET.WORKBOOK(1) GET.WORK(1) returns a array of fullpaths to each worksheet in order. and a second name of COPYDATA as =INDIRECT("'"&MID(INDEX(SHEETARRAY,2),FIND("]",INDEX(SHEETARRAY,2))+1,128 )&"'!"&ADDRESS(ROW(),COLUMN())) Works like a champ. I was pleasantly surprised INDEX() worked with the array like that. I put =COPYDATA in wherever needed. Thanks. ....best, Hash In article , "Ken Wright" wrote: But if there's nothing that says it absolutely has to be the second sheet then my advice was going to be exactly the same as Rob's. I'd name the second sheet 'start' and the third 'finish' or maybe just a and b, and then use as Rob suggested =SUM(start:finish!A1) or =SUM(a:b!A1) instead of references to a sheet2 or whatever the name would be Then just drag your new sheet each time between a and b and drag the old one out to the right of sheet b. Sheet1 will then only pick up data from any sheet between a and b. There will be no data on sheets a or b so all you will get is the data from your new sheet. Failing that take a look at the INDIRECT function and then in a single cell on your summary sheet put the name of the tab you are referring to. Use that cell in your formulas and then just change the sheet name in that one cell to refer to the one you want, eg if the name of your second sheet was in cell C1 on your summary sheet then you could use =INDIRECT(C1&"!A1") on your summary sheet to refer to cell A1 on whatever sheet has it's name in cell C1 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- |
#4
|
|||
|
|||
KL -
I will. Thanks for pointing it out. I have a solution (see other post) but I'd love a faster answer. ....best, Hash In article , "KL" wrote: Hash, Following up on Rob's suggestion, you may want to review Tom Ogilvy's posts in the following thread: http://tinyurl.com/699a7 Regards, KL wrote in message ... Gang - I have a workbook with a summary worksheet at the first tab, and then a new worksheet inserted every quarter as the second tab. Earlier quarters are pushed to the right on the tabs. Is there a way to reference data on the second tab no matter what that second worksheet is titled? It changes every quarter. Right now I do a global replace manually via menu. Thanks in advance. ...best, Hash |
#5
|
|||
|
|||
Glad you got sorted, though I'm not surprised it takes a while to calc. :-)
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- wrote in message news:_REFd.15563$B95.4565@lakeread02... Hi Ken - Well, I futzed with it all day. Couldn't use the bracketing worksheets because some of the data is textual, and I couldn't find one of the limited 3-D functions that handled that. I didn't use a straigtforward indirect method because I didn't want to populate more cells in the summary sheet, which is saved off as text and imported to a Lotus Notes database. So I tried J-Walk's SHEETOFFSET function (See his Excel 2003) and a vague memory of how I would do this years ago in XLM. That's what I settled on, after a lot of digging, since it seemed a bit faster. That's important--I can hit the head while this thing calculates and still come back to the hour-glass. ;-) The summary sheet is ~5400 rows by 14 columns. The idea remains to automate this as much as possible. I defined a name as SHEETARRAY = GET.WORKBOOK(1) GET.WORK(1) returns a array of fullpaths to each worksheet in order. and a second name of COPYDATA as =INDIRECT("'"&MID(INDEX(SHEETARRAY,2),FIND("]",INDEX(SHEETARRAY,2))+1,128 )&"'!"&ADDRESS(ROW(),COLUMN())) Works like a champ. I was pleasantly surprised INDEX() worked with the array like that. I put =COPYDATA in wherever needed. Thanks. ...best, Hash In article , "Ken Wright" wrote: But if there's nothing that says it absolutely has to be the second sheet then my advice was going to be exactly the same as Rob's. I'd name the second sheet 'start' and the third 'finish' or maybe just a and b, and then use as Rob suggested =SUM(start:finish!A1) or =SUM(a:b!A1) instead of references to a sheet2 or whatever the name would be Then just drag your new sheet each time between a and b and drag the old one out to the right of sheet b. Sheet1 will then only pick up data from any sheet between a and b. There will be no data on sheets a or b so all you will get is the data from your new sheet. Failing that take a look at the INDIRECT function and then in a single cell on your summary sheet put the name of the tab you are referring to. Use that cell in your formulas and then just change the sheet name in that one cell to refer to the one you want, eg if the name of your second sheet was in cell C1 on your summary sheet then you could use =INDIRECT(C1&"!A1") on your summary sheet to refer to cell A1 on whatever sheet has it's name in cell C1 -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- |
#6
|
|||
|
|||
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
how do I make a copy of a worksheet and retain formulas but not data | FireBrick | Setting up and Configuration | 2 | December 29th, 2004 07:33 PM |
relative formulas | *Scott | Worksheet Functions | 4 | April 16th, 2004 10:26 PM |
Relative vs. Absolute Values in Formulas | Worksheet Functions | 0 | January 22nd, 2004 11:59 PM | |
Printing worksheet (showing formulas instead) | [email protected] | Worksheet Functions | 1 | October 17th, 2003 12:50 PM |