Help me with Hyperlinks

UMF_DAN

New Member
Joined
Jul 23, 2014
Messages
18
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,619
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

New Member
Joined
Jul 23, 2014
Messages
18
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,619
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

New Member
Joined
Jul 23, 2014
Messages
18

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,619
Let's say your sheet2 looks like:

Excel 2010
AB
1
2ShirtsT-shirt
3ShirtsPolo
4ShirtsCollared
5PantsKhakis
6PantsJeans
7ShoesSneakers
8ShoesNikes
9ShoesCrocs
10BeltsLeather
11HatsBaseball Cap
12HatsFedora
13

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



Then sheet3 could be:

Excel 2010
AB
1HatsList
2Baseball Cap
3Fedora
4
5
6
7

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

Array Formulas
CellFormula
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

New Member
Joined
Jul 23, 2014
Messages
18

ADVERTISEMENT

Let's say your sheet2 looks like:

Excel 2010
AB
1
2ShirtsT-shirt
3ShirtsPolo
4ShirtsCollared
5PantsKhakis
6PantsJeans
7ShoesSneakers
8ShoesNikes
9ShoesCrocs
10BeltsLeather
11HatsBaseball Cap
12HatsFedora
13

<tbody>
</tbody>
Sheet2



Then sheet3 could be:

Excel 2010
AB
1HatsList
2Baseball Cap
3Fedora
4
5
6
7

<tbody>
</tbody>
Sheet3

Array Formulas
CellFormula
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,619
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

New Member
Joined
Jul 23, 2014
Messages
18
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,664
Messages
5,597,450
Members
414,145
Latest member
lonnie451

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top