Summary list of worksheets in workbook
 

Hello all,
Newsgroup protocol suggests one should scan threads first and maybe
reply to one or two before jumping in with a post, particularly a
request, but since I can't see what I'm looking for, I'm just going to
go for it!

What I'm trying to do is, I'm sure, quite straightforward, but I can't
find a quick, automated or semi-automated way to do it without delving
into VB scripting a bit further than I'd like.
I have a spreadsheet which contains a worksheet for each quotation,
order or invoice we create. I'd like to have a summary list of these
items on the first worksheet, showing basic details like reference
number, date, customer etc. with one row for each worksheet.
Ideally I'd like to set up the formulas in advance, but since the
sheets don't exist until we create them, that may not be possible. As
an alternative, a quick way of adding to the list would be okay. The
only way I can find at the moment is to replicate a row, then do a find
and replace to change the sheet name in the formulas to the new one.
Admittedly this isn't *so* laborious, but if a neater way is possible
it would be nice!

Thanks in advance for any thoughts,
Simon.

Summary list of worksheets in workbook
 

Hi Simon

Two macro's examples

One that create formula links
http://www.rondebruin.nl/summary.htm

And one that copy the data
http://www.rondebruin.nl/copy2.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message ups.com...
Hello all,
Newsgroup protocol suggests one should scan threads first and maybe
reply to one or two before jumping in with a post, particularly a
request, but since I can't see what I'm looking for, I'm just going to
go for it!

What I'm trying to do is, I'm sure, quite straightforward, but I can't
find a quick, automated or semi-automated way to do it without delving
into VB scripting a bit further than I'd like.
I have a spreadsheet which contains a worksheet for each quotation,
order or invoice we create. I'd like to have a summary list of these
items on the first worksheet, showing basic details like reference
number, date, customer etc. with one row for each worksheet.
Ideally I'd like to set up the formulas in advance, but since the
sheets don't exist until we create them, that may not be possible. As
an alternative, a quick way of adding to the list would be okay. The
only way I can find at the moment is to replicate a row, then do a find
and replace to change the sheet name in the formulas to the new one.
Admittedly this isn't *so* laborious, but if a neater way is possible
it would be nice!

Thanks in advance for any thoughts,
Simon.

Summary list of worksheets in workbook
 

One option might be to use a the INDIRECT function which allows you to
create a link from text.

Set yourself up your summary list with one column of values being the sheet
name. Then use that cell from each row within an INDIRECT function to link
into the relevant cells, ensuring that you use absolute references to the
cells in question.

Now when you create a new sheet, simply copy down the previous row on your
summary list and change the sheet name in the column of sheet names to your
new sheet name. The data will all then be picked up from your new sheet.

--
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
ups.com...
Hello all,
Newsgroup protocol suggests one should scan threads first and maybe
reply to one or two before jumping in with a post, particularly a
request, but since I can't see what I'm looking for, I'm just going to
go for it!

What I'm trying to do is, I'm sure, quite straightforward, but I can't
find a quick, automated or semi-automated way to do it without delving
into VB scripting a bit further than I'd like.
I have a spreadsheet which contains a worksheet for each quotation,
order or invoice we create. I'd like to have a summary list of these
items on the first worksheet, showing basic details like reference
number, date, customer etc. with one row for each worksheet.
Ideally I'd like to set up the formulas in advance, but since the
sheets don't exist until we create them, that may not be possible. As
an alternative, a quick way of adding to the list would be okay. The
only way I can find at the moment is to replicate a row, then do a find
and replace to change the sheet name in the formulas to the new one.
Admittedly this isn't *so* laborious, but if a neater way is possible
it would be nice!

Thanks in advance for any thoughts,
Simon.