List of all worksheets where a cell contents appears

[Not-Smart-Enough]

UPDATE,
The attached workbook is a sample of what I have created so far.
Cell P8 on the 'Master Parts List' sheet is where I want to input a specific part number.
Cell Q8..Q9..onwards, is where I wish to list all the worksheets where the part number appears (eg. BOM_1...BOM_2...etc).
Cell R8..R9..onwards, is where I wish to list the row number in which the part number appears on a particular worksheet.
---------------------------------

As my nickname indicates I'm clearly not smart enough, so i'm calling out for your assistance dear forum.

I have an excel workbook containing a number of growing worksheets, each representing a Bill of Materials.

Each worksheet will contain a list of 'part numbers'. the same 'part number' can appear on any worksheet (but not all worksheets).

What I would like to do (on worksheet 1, say) is have a cell where I can input a specific 'part number' and generate a list of all worksheets that 'part number' appears.

Also, if possible to list the worksheet row number for that 'part number' location.

Finally, it needs to be done as a formula and NOT any VB macro.

I've look through the various posts but couldn't see anything along these lines. If there is I apologies.

Any assistance will be very greatly received.

many thanks
Not-smart-Enough.

[AliGW]

Welcome! Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jason.b75]

Finally, it needs to be done as a formula and NOT any VB macro.

Not VB macro, or not VBA of an kind?

Possibly it could be done with a UDF, which is a formula that uses vba to create new functions, but without any vba use, you are asking the impossible.

[Tony Valko]

I didn't download your file.

Technically, you can generate a list of sheet names without VBA. The technique uses a macro function which is what Excel used before it changed over to VBA. So, it's not VBA but it does require you to save the file as a macro enabled file.

If you can't/don't want to use either VBA or the macro function then your options are limited.

You'll have to manually generate the sheet names. You can either do that by hardcoding the sheet names directly in the formula or you can list the sheet names in a range of cells.

How many sheets are there? What are their names?

[Not-Smart-Enough]

Hi Jason,

Apart from never usng macros I'd prefer not to rely on them due to distribution requirements of the final spreadsheet.

That being said, in the short term any solution at the moment would be very usefull.

As I refer to earlier I'm not a wizz so any solution using a macro I would need some clear guidlines on how to impliment it.


many thanks in advance.

[Not-Smart-Enough]

Hi Tony,

I shall be manually adding and renaming new sheets.

What I need to do is use say cell [A1 - sheet 1] to enter a part number '123' and then have cell B1, B2, B3 etc (sheet 1) list all the other sheets names (sheet 2, sheet 3 etc) where that part number appears.

The sample file is set up to give you an idea of what I'm looking for. The input filed there is P8

Thanks,
Mark.

[JohnTopley]

VBA solution:

See here how to Start VBA editor

http://www.techonthenet.com/excel/ma...editor2010.php

Please Login or Register  to view this content.

Click RUN on "MAster Parts List"

[jason.b75]

A variation on John's suggestion, this one runs automatically when you change the part number in P8.

Right click the sheet name tab for 'Master Parts List' then click 'View code'

Copy the code below and paste into the editor.

Please Login or Register  to view this content.

Close the editor and save excel. When you see a warning message about macro free / macro enabled workbooks, click 'No' and change the file extension type to .xlsm

Based on your original post and the format of your sample file, I've assumed only 1 part number at any time on the master part list, also that each part number will only appear once on any of the other sheets.

The code can be modified to allow multiple occurences if needed.

[JohnTopley]

@Jason,
..like it!

You might like to add message if part not found

[Tony Valko]

Here's a small sample file that demonstrates this.

[Not-Smart-Enough]

Good afternoon forum

Smiling today as the mcros from Jason and John work great. A big thank you for your help guys.
I'm macro challenged but I managed to work my way through your suggestions.

Also a big thank you to Tony for his formua based solution. This is more ideally suited to my limited skill set.
I have tweaked the formula slightly to more suit my tab nomenclature and the only thing I could possibly ask for (and I know I'm pushing my luck here) is to modify the formula to refer to a sheet name list rather than adding any new sheet names to the formula each time.

My slight mod.
=IFERROR(INDEX({"Q331","Q441","Q551","Q661"},SMALL(IF(COUNTIF(INDIRECT("Q"&{331;441;551;661}&"!A2:A10"),$A$5),{1;2;3;4}),ROWS(B$5:B5))),"")

The more sheets I have the longer the formula becomes. So referring to a sheet name list (if possible) would eliminate this issue.

Again to ALL thank you very much and take heart in helping out an enthusiastic if Not-Smart-Enough excel user.

Mark.

[Tony Valko]

Using a range of cells to hold the sheet names...

Data Range

	A	B	C
1	Sheet Names	Lookup	Sheets
2	Q331	X	Q331
3	Q441		Q551
4	Q551		Q661
5	Q661

This array formula** entered in C2:

=IFERROR(INDEX(A$2:A$5,SMALL(IF(COUNTIF(INDIRECT(A$2:A$5&"!A2:A10"),B$2),{1;2;3;4}),ROWS(C$2:C2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[Tony Valko]

Related...

List sheet names using formulas

[JohnTopley]

Try

=IFERROR(INDEX(Sheets,SMALL(IF(COUNTIF(INDIRECT(Sheets&"!$B$6:$B$500"),$P$8),ROWS($1:1)),ROWS(Q$8:Q8))),"")

"Sheets" is named range of tab names

What about ROW number?

[Not-Smart-Enough]

Hi Tony,

Thats the kind of thing I was thinking of. I notice that when I add to column A. I need to add the next sequential number to the {1;2;3;4} sequence.
This isn't a problem, I'm just checking as it seems to work ok when I do.

I've also tried to do a =match(B2,C2,A:A,0) to obtain the row number but it obviously doesn't recognise the cell result (Q331).
If I can get the row number of B2 that appears in the worksheet listed in C2, C3 etc then that should be the final piece.

many many thanks
Mark.

[JohnTopley]

If you use formula in post #14, no change is required: just drag in down from Q8.


To get ROW ..

in R8

=IFERROR(MATCH($P$8,INDIRECT("'" &Q8& "'!B1:B500"),0),"")

Copy down

[JohnTopley]

To get ROW ..

in R8

=IFERROR(MATCH($P$8,INDIRECT("'" &Q8& "'!B1:B500"),0),"")

Copy down

[Tony Valko]

Here's an updated sample file that demonstrates this.

[Not-Smart-Enough]

As we say here in England.....Nailed It!

That all seems to work great.

I shall try it on my full spreadsheet tomorrow and let you know how I get on.

If you're a beer drinker you've earned it, thank you.
Mark

[Not-Smart-Enough]

Hi John,

I'm still working through your suggestions but I'll definitely persevere.
I think it's just me whos failing at the moment.

I will let you know how I get on.

Thank you so much for your help, I'm learning a lot.
Mark.

[Tony Valko]

If you're a beer drinker you've earned it

St. Pauli Girl Dark!