Help me with Hyperlinks

[UMF_DAN]

Hey guys,

I just can't seem to figure this out without using macros, but I am not allowed to use it at work. Basically what I need to figure out is

Workbook with 3 sheets - Sheet1 (Links), Sheet2 (Database) , Sheet3 (Output)

Sheet1 has item groups, i.e Cell A1(Shirts), A2(Pants), A3(Socks) etc..
Sheet2 has the database A1(Group), B1(Size), C1(Color)
A2 Shirts ...........................
A3 Shirts............................
A4 Pants............................
A5 Pants............................
Sheet3 I need to have it display all rows that have for example "Pants" in Column A when I click the link in Sheet1, A2 (Pants)

I can't wrap my head around doing this without VBA...

Does anybody have an idea and can lead me to the right direction?

Thanks in advance,

Dan

 

[Eric W]

Seems a little awkward. Are you saying that you click a hyperlink on Sheet1 that says "Shirts", which takes you to Sheet3, and you want a list of shirts from sheet2 to be listed? Is there a hyperlink on Sheet1 for every group?

You could put a Data Validation dropdown list on Sheet3, with the group names in it. Then when someone chooses a group from it, Sheet3 could populate the list.

On sheet 2, are are the groups together, you're not going to have Shirts, Pants, Shirts, Pants, etc.? (Either way's possible, just might affect how the formulas are created.)

Let us know.

 

[UMF_DAN]

yes, exactly what you said is what I need to figure out. All the groups are sorted in sheet 2, and all groups have a link on sheet one! when I click on shirts in sheet1, I need to have all items that have "shirts" written in the A column of sheet2to be displayed in sheet3

Dan

 

[Eric W]

I'm at a bit of a loss how to do that. When clicked, a hyperlink doesn't trigger anything that I can use to update the formulas. Conceivably, I could use a Selection_Change event to trigger it, but you don't want to use VBA. This leaves me with 3 possibilities.

1) Have one hyperlink on Sheet1, which points to a location on Sheet3. At that location is a dropdown box with all the groups. The user selects a group, and the list will automatically populate.

2) Have one hyperlink for each group, but each hyperlink points to a different sheet ("Shirts", "Pants", etc.) and each of those sheets has unique formulas that lists the items. Not recommended, because it means adding sheets, plus a lot of formulas will slow your workbook down a lot.

3) Use Filtering on Sheet2. Just select Filter from the ribbon, then click the down arrow in column A and select the group you want to show.

Let me know if any of those will work for you. I'll try to think of any other options, or maybe someone else will have an idea.

 

[UMF_DAN]

I'm at a bit of a loss how to do that. When clicked, a hyperlink doesn't trigger anything that I can use to update the formulas. Conceivably, I could use a Selection_Change event to trigger it, but you don't want to use VBA. This leaves me with 3 possibilities.

1) Have one hyperlink on Sheet1, which points to a location on Sheet3. At that location is a dropdown box with all the groups. The user selects a group, and the list will automatically populate.

2) Have one hyperlink for each group, but each hyperlink points to a different sheet ("Shirts", "Pants", etc.) and each of those sheets has unique formulas that lists the items. Not recommended, because it means adding sheets, plus a lot of formulas will slow your workbook down a lot.

3) Use Filtering on Sheet2. Just select Filter from the ribbon, then click the down arrow in column A and select the group you want to show.

Let me know if any of those will work for you. I'll try to think of any other options, or maybe someone else will have an idea.

I think option 1 is the best solution for what I actually need! How can i achieve this?

 

[Eric W]

Let's say your sheet2 looks like:

Excel 2010

	A	B
1
2	Shirts	T-shirt
3	Shirts	Polo
4	Shirts	Collared
5	Pants	Khakis
6	Pants	Jeans
7	Shoes	Sneakers
8	Shoes	Nikes
9	Shoes	Crocs
10	Belts	Leather
11	Hats	Baseball Cap
12	Hats	Fedora
13

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Then sheet3 could be:

Excel 2010

	A	B
1	Hats	List
2		Baseball Cap
3		Fedora
4
5
6
7

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula B2 {=IF(B1="","",IFERROR(INDEX(Sheet2!B:B,SMALL(IF(Sheet2!$A$2:$A$12=$A$1,ROW($A$2:$A$12)),ROWS($B$2:$B2))),""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put a Data Validation in A1 which has a drop down box for the groups. (Let me know if you need help setting that up.) Then put the header in B1. Then put the array formula in B2, confirmed with Control-Shift-Enter. Then copy B2 and paste it down the column as far as needed. Then whenever someone selects a group from A1, the list in B2:B99 will automatically update with the matching items from sheet2. Your hyperlink on sheet1 can point to Sheet3!A1.

Let me know how this works.

 

[UMF_DAN]

Let's say your sheet2 looks like:

Excel 2010

	A	B
1
2	Shirts	T-shirt
3	Shirts	Polo
4	Shirts	Collared
5	Pants	Khakis
6	Pants	Jeans
7	Shoes	Sneakers
8	Shoes	Nikes
9	Shoes	Crocs
10	Belts	Leather
11	Hats	Baseball Cap
12	Hats	Fedora
13

<tbody>
</tbody>

Sheet2​

Then sheet3 could be:

Excel 2010

	A	B
1	Hats	List
2		Baseball Cap
3		Fedora
4
5
6
7

<tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula B2 {=IF(B1="","",IFERROR(INDEX(Sheet2!B:B,SMALL(IF(Sheet2!$A$2:$A$12=$A$1,ROW($A$2:$A$12)),ROWS($B$2:$B2))),""))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put a Data Validation in A1 which has a drop down box for the groups. (Let me know if you need help setting that up.) Then put the header in B1. Then put the array formula in B2, confirmed with Control-Shift-Enter. Then copy B2 and paste it down the column as far as needed. Then whenever someone selects a group from A1, the list in B2:B99 will automatically update with the matching items from sheet2. Your hyperlink on sheet1 can point to Sheet3!A1.

Let me know how this works.

Thanks for all the help,

but I am, a little lost right now since I can't get the formula to work. It gives me an error everytime I enter it. would you be able to send the example spreadsheet you did? Maybe I can compare to mine and see what is wrong. I am pretty much self taught and I am learning everyday.

 

[Eric W]

The most likely problem with the formula is that it's an array formula and you're treating it as a normal formula. Understandable, since most people don't even know array formulas exist. What you need to do is select cell B2, copy this formula (notice that there are no brackets on the ends):

=IF(B1="","",IFERROR(INDEX(Sheet2!B:B,SMALL(IF(Sheet2!$A$2:$A$12=$A$1,ROW($A$2:$A$12)),ROWS($B$2:$B2))),""))

and paste it into the formula bar. Change the Sheet2!$A$2:$A$12 to match your actual range on Sheet2. Then when you've done that, press Control-Shift-Enter, instead of just Enter. If you've done everything correctly, Excel will put the {} on the ends. Once you've entered that formula, then you can copy the B2 cell, and paste it down the column like a regular formula.

Let me know how that works.

 

[UMF_DAN]

The most likely problem with the formula is that it's an array formula and you're treating it as a normal formula. Understandable, since most people don't even know array formulas exist. What you need to do is select cell B2, copy this formula (notice that there are no brackets on the ends):

=IF(B1="","",IFERROR(INDEX(Sheet2!B:B,SMALL(IF(Sheet2!$A$2:$A$12=$A$1,ROW($A$2:$A$12)),ROWS($B$2:$B2))),""))

and paste it into the formula bar. Change the Sheet2!$A$2:$A$12 to match your actual range on Sheet2. Then when you've done that, press Control-Shift-Enter, instead of just Enter. If you've done everything correctly, Excel will put the {} on the ends. Once you've entered that formula, then you can copy the B2 cell, and paste it down the column like a regular formula.

Let me know how that works.

Ok, inputing the formula worked. I set up the exact same sheets like in your example, but everything stays blank! For the validation in A1(sheet3) I chose Data- Data Validation- List- and then selected A1 through A12 on sheet2. When I now go to sheet3 and select hats for example in the drop down list, B2 and the other I copied stays blank. I put my validation box on sheet 3 and select the grous on sheet 2 right?

Thanks for all the help