Using Sheet Name

[MNBridge]

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.

Any ideas?

[Obi-Wan Kenobi]

Idea A: Don't do it.
Idea B: The INDIRECT function.

[Cho Da]

=INDIRECT()

[Sue Reitz]

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]

An off-the-box idea:
Put all the data into one sheet, defining LOB with a new column. Then massively SUMPRODUCT() the thing.

[Obi-Wan Kenobi]

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.

[TwistedMentat]

If you set up the large data table, why not just present it in a pivot table?

[Dr T Non-Fan]

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.

[Chuck]

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...

Sum=A1*(LOB1!A1+LOB1A2...)+A2*(LOB2!A1+...) +...+A6*(LOB6!A1+...)

This way not only could you summarize a particular LOB, but you could summarize any combination of LOBs.

[Dr T Non-Fan]

Good idea, Chuck!
There's probably a limit to the number of LOBs that could exist, though.

[MNBridge]

Thanks all,

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.