reference to future worksheets

V

viilis

Hello,

I am pretty new to Excel and need some advise.

I am trying to create a workbook which will be edited by several users
Every user will create their own worksheet according to a template whic
is included in the workbook.

My task is to make a summary of the data from all the worksheets adde
to the file. However I do not know, yet, how many worksheets will b
created or what their file names will be.

How can I tell Excel to look for the "next worksheet" ? (For instanc
to be used in CONCATENATE('-first worksheet-'!A3;'-nex
worksheet-'!A3;-worksheet after next-'!A3
 
A

Andy B

Hi

I reckon the best way to handle this is to have one sheet at the front of
the others, and another at the back (both hidden if necessary). Any new
sheets you get can be inserted in between these two. You can then use
formulae such as:
=SUM(firstsheet:lastsheet!A2)
 
D

Dave Peterson

I think Andy's suggestion will work fine for those cells that you want to sum.

But I think I'd create individual sheets based on that template. Then let the
user just copy|paste his/her data right into their nicely named existing
worksheet.

If they must merge their worksheet into your workbook--and you knew the names of
the sheets they were going to use, you could use something like:

=IF(ISERROR(INDIRECT("sheet1!a3")),"",INDIRECT("sheet1!A3"))
&IF(ISERROR(INDIRECT("sheet2!a3")),"",INDIRECT("sheet2!A3"))
&IF(ISERROR(INDIRECT("sheet3!a3")),"",INDIRECT("sheet3!A3"))

But remember, a formula cannot exceed 1024 characters when measured in R1C1
reference style.

This one example with just 3 sheets is 182 characters.
 
Top