Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel should let its worksheets be arranged (tiled) in a workbook | Excel Worksheet Functions | |||
Spawning worksheets and a summary per worksheet | Excel Discussion (Misc queries) | |||
How do I reference values from 200 worksheets onto a summary sheet | Excel Discussion (Misc queries) | |||
Copying Numerical Totals of separate worksheets to a single Summary Worksheet | Excel Discussion (Misc queries) | |||
Copying Totals of separate worksheets to a single Summary Worksheet | Excel Discussion (Misc queries) |