View Full Version : Using Sheet Name
01-24-2003, 04:07 PM
I'm trying to create a sheet that would allow summary data of Sched P data.
The file currently has each LOB as the sheet name and includes all of Schedule P.
What I'd like to do is create a sheet where the user can pick a LOB and the sheet will give the summary statistics.
So basically I want to creat a sheet where for example cell A1 = LOB!A1 + LOB!A2 where the LOB would be changed based on the line desired.
The LOB would be decided by a pulldown box.
01-24-2003, 04:23 PM
Idea A: Don't do it.
Idea B: The INDIRECT function.
01-24-2003, 04:26 PM
01-24-2003, 04:40 PM
There's a pair of functions in Lotus @@(@coord(sheet;column;row;reference)) that would do the job for you. @coord(1;2;3;8) would return A:B3. Putting @@() around it would return the value in that cell. In Excel, Indirect() does what @@() does. I know there's a way to do the @coord() function in Excel, but I can't remember what it is.
Then you could have the sheet reference be a function of the position of the LOB in the drop down box.
I hope this makes sense and is helpful!
Dr T Non-Fan
01-24-2003, 05:13 PM
An off-the-box idea:
Put all the data into one sheet, defining LOB with a new column. Then massively SUMPRODUCT() the thing.
01-24-2003, 05:26 PM
DTNF's idea is probably better. Unless storage size, memory space, or execution speed issues make the result unusable, it'll be much, much easier to maintain.
01-24-2003, 06:10 PM
If you set up the large data table, why not just present it in a pivot table?
Dr T Non-Fan
01-24-2003, 06:22 PM
Unless storage size, memory space, or execution speed issues make the result unusable, it'll be much, much easier to maintain.
I use that as an excuse to get a faster machine!
I also recommend trying to get the data from the source in a more usable format than in separate LOB sheets.
01-25-2003, 03:35 PM
This may be goofy, but say you have six LOBs. Define six cells (say A1 to A6) equal to 1 (include in sum) or 0 (don't include in sum). Then...
This way not only could you summarize a particular LOB, but you could summarize any combination of LOBs.
Dr T Non-Fan
01-27-2003, 02:29 PM
Good idea, Chuck!
There's probably a limit to the number of LOBs that could exist, though.
01-28-2003, 03:57 PM
I'll try the indirect function.
The reason I don't want to put everything on one sheet is that I plan to set up a macro that deletes any LOBs with 0 premium. Also if it is sent on and someone wants to look at say Work Comp it is easier if you can just click on a sheet name.
vBulletin® v3.7.6, Copyright ©2000-2013, Jelsoft Enterprises Ltd.