Forum Discussion
Making a list from specific cells from multiple sheets with a list of sheets.
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),"]"),"").
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),"]"),"").
- 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.- Thank you for the help. I will try this at work tomorrow. Thank you again.
