Forum Discussion
Solved
Excel Autofill
Windows 11 v 23H2 Excel v 18.2407.1241.0 Can Autofill make series in both the text AND hyperlinks? Three columns of 32,767 lines: Column F has numeral-free copy with non-sequential hyperlinks to...
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 I don't know if I'm understanding your situation correctly, but you may be able to do what you need by using the HYPERLINK function for F and G. For example, in H, you can use a formula like =HYPERLINK("#" & G1,F1). Then you can fill down F, G, and H. The # in the formula is a little trick to make the HYPERLINK go to a place in the document, rather than expecting a link to a web URL, for example. In this example, if you click a link in H, it will take you to the corresponding cell in F.
Thanx. I think I got about half of it across. What I'm looking for is a way to Autofill both text and links into the same cells, not necessarily at the same time but in a way that the second Autofill doesn't wipe out the first. The cells' texts also have a sequence to them, and doing whichever function I do first gets wiped out when I do the second. Best workaround I can figure is in progress: I've Autofilled the texts and am now doing the links individually - but with nearly 100,000 cells it'll take weeks if not months.
KurtHPickering - it would be great if you could share a snippet of the workbook or some screen shots to make it clear how your data is set up and what you need to do.
