How to Link to, Or Draw Data from Sheets not yet created

[LibreDummy]

I have a worksheet where new data sets for individual customers are created from the Master Sheet.

When a new customer comes in, we make Copy Of [Master Sheet] → Sheet 1, Sheet 2, Sheet 3.

So the sheets for the customers do not exist at the start of the day.
The number of sheets that exist by the end of the day are undefined but always under 50. So this is not an infinite data series.

Our customers all have a fixed customer identification, eg A1234567.
We do not know what the sheets will be called at the start of the day, but the sheets will always be named after the customers, so they will only have 8 alphanumerics and nothing else. A1278952, Q7282956, H3344556.

The Master Sheet is called the Template. There is also a Data Sheet. We do not want to draw data from them. So I also need to code for drawing data from sheets only with 8 alphanumerics, but not sheets called Template or Data Sheet.

The range of products and data entered is always from a fixed range.
So Sheet 1 (or Sheet 50) 's data can be limited to the data found in specific cells. I do need to code for drawing numeric or text data, because some of the data needs to be copied from drop down boxes/ cells with formula in them.

I’m trying to create a summary sheet for the end of the day.
So this sheet will need to show all the customers (sheets created).
It will need to draw data from the individual customer data sheets created during the day.

My spreadsheet skills are quite low (hence my nickname). How can I do this?

[keme]

Use INDIRECT(). That function creates a cell reference from a string.

Two strategies:

• Use a simple INDIRECT() call to get to the sheet, then use OFFSET() or INDEX() to reference data within the sheet.
• Build the entire reference to each data element as a string, and use INDIRECT() only.

A single INDIRECT() for the entire job may seem like a good option, but in my experience it gets messy very quickly. It is going to work, but if you need to revise your sheet at a later time, editing that formula is going to be a challenge. OFFSET() and INDEX() give cleaner formula structure.

Sorry. I missed the fact that you need a summary, and assumed it was about extraction of individual data elements based on customer selection.

If you want to make a summary across customers, it is a bad idea to put the data in separate sheets. Keep all records in one sheet, with every record tagged with customer ID. At best, use a proper database instead of a spreadsheet.

Use a filter or pivot table (or a database query) to extract/summarize data.

Edit your question to attach a sample file showing your setup. Without knowing exactly what you have, it is very difficult to suggest how to change.

Remember this is a public place, so you should remove/mangle confidential data (like customer info) before uploading.

[LibreDummy]

Thank you for answering!

But right now I don’t even know the names of the other spreadsheets from which I plan to draw data. These haven’t been created. I can have a fixed name for my Summary Sheet (eg Summary Sheet). But I have no clue what the other sheets will be called until the customer shows up.

[Lupp]

Please comment on answers using the tool add a comment. (This is not a forum with a chronological order of posts.)