[Solved] Auto-filling data into a table when new sheets are added to the workbook

[PJR]

Hi all
First, a little context:
Every month I receive a number of samples from a supplier, which I will then carry out a few tests on. I collect all the data into a single workbook, with each batch having it's own sheet within that workbook, and an index table/sheet displaying some of the key data from each sheet.

When new sheets are added, they are created from a copy of a blank form, so the data will always be in the same locations on the sheets

The table collects data from several locations on the target sheet, often using CONCATENATE to show 2 or more values in the same cell.
At the moment I have to manually re-enter each formula (or copy/paste, changing the sheet name(s) with Replace). The first column of the sheet gives the batch no. which is then hyperlinked to the sheet in question.
What I would like to know is how can I get all these formulas to automatically change to new sheet names as they are added?

Thanks in advance

 

[Seemanto Saha]

Hello PJR,

Welcome to ExcelDemy Forum. I understand that you have a table to extract information from each sheet of the table and you want to avoid manually re-entering the formula with a different sheet name while extracting information from a newly added sheet.

We can execute this by listing sheet names in a range of cells and using these cell references in the INDIRECT function.​

For example, let’s assume we have the following blank form.

We have copied this form to the Sept001 sheet to create the dataset for batch numbers Sept001 and Sept002.​

In the Index Sheet, we want to extract the Supplier Name, Temperature, Humidity, Visual Inspection Result, and Actual Testing Result for Sample Number 1.​

Manually insert the sheet name Sept001 in cell A2 and type in the following formula in cell B2 for Supplier & Sample.​

=TEXTJOIN(", ", TRUE, INDIRECT("'" & A2 & "'!B2:C2"))

Similarly, for cell C2 insert the following formula:

=TEXTJOIN(", ", TRUE, INDIRECT("'" & A2 & "'!F2:G2"))​

And, for cell D2, type in the following formula:

=TEXTJOIN(", ", TRUE, INDIRECT("'" & A2 & "'!H2:I2"))

Here, the TEXTJOIN function concatenates the elements of the cells referenced by the INDIRECT function.

Now for extracting information from the Sept002 sheet, manually enter the sheet name in cell A3 >> select the range B2: D2 and hover your mouse pointer over the right side of cell D2 >> when the Fill Handle icon appears, drag it down.​

Thus we can extract similar information for each sheet without manually re-entering the formula for each sheet.​

Now, let’s assume we added another sheet for a new batch Sept003.​

In the index Sheet, manually enter the sheet name in cell A4 >> select the range B3: D3 and hover your mouse pointer over the right side of cell D3 >> when the Fill Handle icon appears, drag it down.​

All the required information will appear without any need to change in formulas.​

However, if you want to avoid manually entering sheet names and dragging Fill Handle icons, then you need a VBA macro to automatically add sheet names and extract the required information from those sheets.

I hope I was able to provide you with an idea of how to extract data from other sheets without changing the formulas for each sheet name. Let us know your feedback. The workbook used for this solution is attached below.​

Regards,
Seemanto Saha
ExcelDemy

 

[PJR]

Thank you so much, that looks amazing!
I'll be trying it out on the actual table later this week, but your explanations seem very straight forward, so I can't see there will be too many issues.

Thanks once again!
PJR

 

[PJR]

I can confirm the formulas worked perfectly on the copy of the workbook on my laptop at home (running 365), however when I tried it at work, they failed because my workstation is only running Excel 2016, and doesn't have the TEXTJOIN function, is there any easy work around?

One follow up question, (and using your example workbook to explain) do the Sheet names have to be exactly as written in the Batch Name field, as the batches of samples I receive usually have "/" dividers (for example, a typical batch number might read as 'L123456/7/8') which are not accepted as useable characters?

 

[Seemanto Saha]

Dear PJR,
Thanks for your feedback. Yes, you are right. The TEXTJOIN function isn't available in Excel 2016. In that case, we can use the CONCAT or CONCATENATE function. Here, I will use the CONCAT function.

The modified formula for Cell B1 is:

=CONCAT(INDIRECT("'" & A2 & "'!B2"),", ",INDIRECT("'" & A2 & "'!C2"))



One limitation of the CONCAT or CONCATENATE function is that these functions don't have a delimiter argument. So, we have to insert delimiters between each cell reference.

And the Sheet name doesn't have to be the same as the batch name. As you previously said each batch has its own worksheet, I assumed the worksheet names are equal to batch names. My "Batch Name" column header might have confused you as well. Sorry for that. In column A, you just have to enter the sheet name from which you want to extract data.​

The workbook with modified formulas is attached below.

Regards,
Seemanto Saha
ExcelDemy​