Forum Discussion
Excel Autofill
KurtHPickering Hyperlinks won't fill in a series the way you hoped, but you have 2 options. You can either add columns and create the link with a HYPERLINK formula as below, or you could write a VBA macro that would go through all the cells and create the link.
The formula in column I is =HYPERLINK("#"&CELL("address",XLOOKUP(1*(TEXTAFTER(G2,"#")),$E$2:$E$11,$E$2:$E$11)),G2).
Breaking it down:
- TEXTAFTER(G2,"#") will get the text after # from the value in G2.
- It multiplies by 1 to convert it to a number rather than text, since the next step will be looking up numbers in E.
- XLOOKUP looks for that value in column E and returns a reference to the matching cell. This is important so the CELL function can get the address of the cell. Make sure to use $ signs on the range reference so it doesn't change when you fill down.
- CELL("address",...) gets the address of the cell found by XLOOKUP.
- Put "#" in front of the address inside the HYPERLINK function.
- Refer to G2 as the friendly name for the hyperlink. This is optional. You could just type "link" so the column can be very narrow and you could put it next to G.
To use a macro, it would still be easier with a helper column to get the address of the cell to link to, then the macro could cycle through all the cells in G and H and create the hyperlinks. Once done, you could get rid of the helper column.
KurtHPickering Hyperlinks won't fill in a series the way you hoped, but you have 2 options. You can either add columns and create the link with a HYPERLINK formula as below, or you could write a VBA macro that would go through all the cells and create the link.
The formula in column I is =HYPERLINK("#"&CELL("address",XLOOKUP(1*(TEXTAFTER(G2,"#")),$E$2:$E$11,$E$2:$E$11)),G2).
Breaking it down:
- TEXTAFTER(G2,"#") will get the text after # from the value in G2.
- It multiplies by 1 to convert it to a number rather than text, since the next step will be looking up numbers in E.
- XLOOKUP looks for that value in column E and returns a reference to the matching cell. This is important so the CELL function can get the address of the cell. Make sure to use $ signs on the range reference so it doesn't change when you fill down.
- CELL("address",...) gets the address of the cell found by XLOOKUP.
- Put "#" in front of the address inside the HYPERLINK function.
- Refer to G2 as the friendly name for the hyperlink. This is optional. You could just type "link" so the column can be very narrow and you could put it next to G.
To use a macro, it would still be easier with a helper column to get the address of the cell to link to, then the macro could cycle through all the cells in G and H and create the hyperlinks. Once done, you could get rid of the helper column.
Steve_SumProductComWow! (In a good way.) Thank you!
