Dynamic Formula - Unknown number (and names) of worksheets 1

[Annelies]

Hi,

I have a workbook into which a various number of worksheets will be copied. These worksheets will be named "BU-*" (the * being a different name for each sheet).

I want, on a summary page, for the formulas to sum a cell value on each of these sheets. The complex part is that at the time of creating the workbook, we do not know
a) how many sheets will be copied in (via VBA code)
b) what exactly they will be called (apart from starting with BU-

My question is:

Can I set up the formula's so that they sum, for example, cell A1 of each sheet starting with BU-. Will this require VBA code (I'm thinking of an IF statement - ie, IF worksheet name starts with BU- then formula = formula+[ThisWorksheetsName]!A1) or is there an easier way?

I hope I haven't overcomplicated the question!

Thanks in advance for your help!

Annelies

 

[AnotherHiggins]

This can be quite simple using nothing but a worksheet formula if you set up the workbook properly. There is no need for VBA.

Make sure you know the names of the first and last sheets, or, failing that, the sheet before the first and the sheet after the last.

To see how it works: Create a new workbook with several worksheets (at least 5). Name the very first sheet [!]Start[/!]. Name the next-to-last sheet [!]End[/!]. Name the very last sheet [!]Summary[/!]. Type numbers in cell A1 on the sheets between Start and End. Now, on the Summary sheet, type in
[COLOR=blue white]=SUM(Start:End!A1)[/color]

No matter how many sheets are between Start and End, and no matter what the names of those sheets are, they will be added together with this formula.

Just make sure the VBA inserts the sheets between the Start and End sheets.

BTW, if you don't want to see these sheets, they can be hidden (Format > Sheet > Hide).

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Annelies]

That is fabulous, thank you so much!

I was really hoping that I was over-engineering things, but didn't expect it to be QUITE so simple!

Thanks!