Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Making a list from specific cells from multiple sheets with a list of sheets.

Copper Contributor

I have a spread sheet that includes specific information that I need to make list for quick reference.

all 70+ sheets are the same for different vehicles by vehicle #. 

How do I set up a sheet to give me a list of Sheet names with the cell information. That will update when I add a new vehicle. Thank You for the help.

5 Replies
you cannot find the right answer if your query is too vague, we don't know what's the content of your sheet, such as:
1. which specific cell information from each sheet you want to include in your summary sheet?
2. do you want the summary sheet to include all the data from each vehicle sheet or just specific data?
3. what is the structure of the data in the vehicle sheets? Are they all the same or do they vary?
4. do you have a specific format in mind for the summary sheet?

or you could attach you sample file here, without/removing all the sensitive datas.
best response confirmed by Tralog58 (Copper Contributor)
Solution

You can use formulas similar to the following. Put the first formula in the same cell on each sheet. This will give the sheet name in a cell on each sheet. If you put that formula in A1 on each sheet, then use the 2nd formula to get a list of the values in A1 on all the sheets, assuming that Sheet1 is the first sheet, and LastSheetName is the last sheet. Just use the actual sheet names of your first and last sheet.

=TEXTAFTER(CELL("filename",A1),"]")

=TEXTSPLIT(TEXTJOIN(";",TRUE,'Sheet1:LastSheetName'!A1),,";")

 

You can add a conditional (IF) to the first formula so that it only gets the sheet name if the other cell information is what you're looking for.  For example, =IF(B1="Automatic steering", TEXTAFTER(CELL("filename",A1),"]"),"").

Thank You Steve I well give this a try.
Thank You for the advice this works great on my home computer with the Microsoft 365. But at work where it is Excel 2013, it doesn't want to work. Dose the textafter command work on 2013 excel? Thank you
TEXTAFTER, TEXTSPLIT and TEXTJOIN will not work in Excel 2013.

The formula to use instead of the TEXTAFTER example is possible in older versions of Excel like below:
=RIGHT(@CELL("filename",A1),LEN(@CELL("filename",A1))-FIND("]",@CELL("filename",A1)))

If you something like & ", " to the end of the formula above, then you can use =CONCAT('Sheet1:LastSheetName'!A1) to get your list, but it will be in a single cell.
1 best response

Accepted Solutions
best response confirmed by Tralog58 (Copper Contributor)
Solution

You can use formulas similar to the following. Put the first formula in the same cell on each sheet. This will give the sheet name in a cell on each sheet. If you put that formula in A1 on each sheet, then use the 2nd formula to get a list of the values in A1 on all the sheets, assuming that Sheet1 is the first sheet, and LastSheetName is the last sheet. Just use the actual sheet names of your first and last sheet.

=TEXTAFTER(CELL("filename",A1),"]")

=TEXTSPLIT(TEXTJOIN(";",TRUE,'Sheet1:LastSheetName'!A1),,";")

 

You can add a conditional (IF) to the first formula so that it only gets the sheet name if the other cell information is what you're looking for.  For example, =IF(B1="Automatic steering", TEXTAFTER(CELL("filename",A1),"]"),"").

View solution in original post