How to "get" a worksheet name based on a common value found in two different worksheets

[jael36]

Hi Everyone,

I know a little about the INDIRECT function and am using it to pull data and it works like a charm!

Context:

• There's a "Summary" worksheet pulling data from "Detailed Info" worksheets in the same workbook using the INDIRECT function
• The "Summary" worksheet has an "ID" column (Column A).
• Each "Detailed Info" worksheet is named in this pattern: [StreetName-StreetType-City]
• Each "Detailed Info" worksheet has a field with an ID that is found in the "Summary" worksheet "ID" Column A
• Each "Detailed Info" worksheet has its worksheet name in cell A1 using this formula:
  • =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

I hope that makes sense so far.

Question:
Is it possible, without a macro, to get the worksheet name based on matching ID values from the "Summary" and "Detailed Info" worksheets?

What I am trying to do is cross-reference the Summary and Detailed worksheets using IDs and supplementing the current use of the INDIRECT implementation already in place.

So, my INDIRECT formula goes from looking like this:

=INDIRECT("'"&$H4&"'!"&"e27")

to looking something like this:

=INDIRECT([this is where the dynamic ID based worksheet reference would be]!"&"a1")

Man.. re-reading this.. sorry if its confusing..

John

 

[FDibbins]

Sounds like you want to get a list of WS names? (could be wrong, Im a bit tired lol)

 

[jael36]

Hi @FDibbins,

I thought about going down that road, but thought it might be overkill.


I've attached the link to my google drive folder where you can download the example file... i tried the Mr.ExcelHtml generator... without success.
https://drive.google.com/open?id=0B_ypyEjzfat2ZUNpcVJydWlWM3c


I'd like to enter an "Evaluation ID" not in use in the SummarySheet and enter it in the DetailSheet3.
This would then take the worksheet name "DetailSheet3" and place it in the SummarySheet.

If you type "3" into DetailSheet3!B4 "Evaluation ID" (in yellow), you'll see it populates the "URL" in cell DetailSheet3!B5

What I'd like to happen now is that in the SummarySheet!C4, the worksheet name "DetailSheet3" populates.

Is this possible?

 

[FDibbins]

I am at work atm and am not allowed to access file hosting sites

Its not a big deal to generate a list of sheet names.
1st create a range name (I called mine Sheetnanes
Then put this in the Refers To box: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

To get a list of sheet names, put this in a cell and copy down (started in A2)...
=IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")

If you want to be able to click the cell and go to that worksheet, change that formula to this...
=IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")

 

[jael36]

Hi,
I implemented your suggestion and it works great to get the clickable list of sheet names. Thanks!
What I've noticed as a behavior is if I change the sheet name or move the sheet order, the list does not auto-recalculate (the option to do so is "on").

Although a GREAT solution to get a list of sheet names, I was really hoping to do this via Index number referencing via the column/field "Eval Id".

Are you able to get the file from the file share?
I've tried the Mr.Excel html generator again so you can reference my previous post explanations a little easier.


Excel 2012

	A	B	C
www.google.com
www.yahoo.com
www.gmail.com
www.hotmail.com
www.aol.com
www.cnn.com
www.fox.com
www.cbc.ca
www.ctv.ca
www.tinyurl.com

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Eval. ID[/TD]
[TD="align: center"]URL[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]Worksheet[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]DetailSheet1[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]DetailSheet2[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"][/TD]

</tbody>

SummarySheet​

Excel 2012

	A	B
DetailSheet1
Evaluation ID
URL

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #CCFFCC"]www.google.com[/TD]

</tbody>

DetailSheet1​

Excel 2012

	A	B
DetailSheet2
Evaluation ID
URL

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #CCFFCC"]www.yahoo.com[/TD]

</tbody>

DetailSheet2​

Excel 2012

	A	B
DetailSheet3
Evaluation ID
URL

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF99]#FFFF99[/URL] , align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #CCFFCC"]#N/A[/TD]

</tbody>

DetailSheet3​

(hope all that worked)

 

[FDibbins]

Yes, 1 of the shortcomings of that method is that it does not auto-update. A quick work-around is to F2 Enter on any of the formulas there

 

[jael36]

Yes, and ctrl+shift+alth+f9 also works as a quick google search said!

I dont mean to be a bother, but is the original request simply not possible in excel?

or simply too complicated without VBA?
If so, then how would I implement VBA if that is the only method that is feasible?

Regards,
John