MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 334
- Office Version
- 2013
- Platform
- Windows
What to include in a hyperlink formula to goto a specific sheet and cell in lieu of the worksheet name.
I have 12 workbooks, each representing the month of the year. I use these workbooks to accumulate daily website visits.
Each day’s entire history is copied out and pasted into the relevant day number with the relevant month’s workbook. This allows me to regularly delete all browser history from my C drive keeping my C drive lean and mean. Obviously, I do many other things along with this to keep my C drive healthy.
Then these entries are edited, meaning I delete duplicates, and any unwanted sites are deleted. Then I sort all and apply color to all entries which helps to divide the sites into categories for any future searching activity.
The formula I am using to link to the sheet called UniqueList is as follows:
=HYPERLINK("[02-February.xlsm]'UniqueList'!a"&(COUNTA(AS:AS)+R1),"Hyperlink & Paste in UniqueList")
The problem I will have is every month’s workbook would not work with the inclusion of the portion of this formula of [02-FebruaryTest.xlsm].
Each month-specific workbook would be different in this portion of the formula, e.g.: [03-March.xlsm]
Is there a method to have this formula work regardless of which workbook it is in such that running the formula would goto the UniqueList sheet in that workbook? In other words is there something to use in lieu of the specific workbook name that would work regardless of which workbook it resides in?
The two attached Xl2bb Mini Sheets show Sheet _01 and Sheet UniqueList.
I have 12 workbooks, each representing the month of the year. I use these workbooks to accumulate daily website visits.
Each day’s entire history is copied out and pasted into the relevant day number with the relevant month’s workbook. This allows me to regularly delete all browser history from my C drive keeping my C drive lean and mean. Obviously, I do many other things along with this to keep my C drive healthy.
Then these entries are edited, meaning I delete duplicates, and any unwanted sites are deleted. Then I sort all and apply color to all entries which helps to divide the sites into categories for any future searching activity.
The formula I am using to link to the sheet called UniqueList is as follows:
=HYPERLINK("[02-February.xlsm]'UniqueList'!a"&(COUNTA(AS:AS)+R1),"Hyperlink & Paste in UniqueList")
The problem I will have is every month’s workbook would not work with the inclusion of the portion of this formula of [02-FebruaryTest.xlsm].
Each month-specific workbook would be different in this portion of the formula, e.g.: [03-March.xlsm]
Is there a method to have this formula work regardless of which workbook it is in such that running the formula would goto the UniqueList sheet in that workbook? In other words is there something to use in lieu of the specific workbook name that would work regardless of which workbook it resides in?
The two attached Xl2bb Mini Sheets show Sheet _01 and Sheet UniqueList.
| 02-February.xlsm | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
| 1 | 26 | Site Title | URL | Link Category Per Color | Visit Time | Hyperlink & Paste in UniqueList | 26 | 26 | 26 | 26 | $B$26 | 26 | ï E Column Count | 7 | ||||||
| 2 | A | Bee Gees - Massachusetts (One For All Tour Live In Australia 1989) - YouTube | https://www.youtube.com/watch?v=FuoWykVNwyI | Music | 2/1/2024 18:53 | https://www.youtube.com/watch?v=FuoWykVNwyI | A | 0 | Dexc | Sort | ||||||||||
| 3 | A | Don Williams-You're My Best Friend (Lyrics) - YouTube | https://www.youtube.com/watch?v=noboitrMunE | Music | 2/1/2024 19:19 | https://www.youtube.com/watch?v=noboitrMunE | B | 1 | HFTV | |||||||||||
| 4 | A | Have I Told You Lately (Lyrics) - ROD STEWART - YouTube | https://www.youtube.com/watch?v=tKOkUWjC0JU | Music | 2/1/2024 21:26 | https://www.youtube.com/watch?v=tKOkUWjC0JU | C | 0 | Icarn | |||||||||||
| 5 | A | HEY PAULA Paul and Paula 1963 - HQ STEREO - YouTube | https://www.youtube.com/watch?v=6vOo-3XWxpo | Music | 2/1/2024 19:34 | https://www.youtube.com/watch?v=6vOo-3XWxpo | D | 0 | Iexc | |||||||||||
| 6 | A | I fell asleep recording this fan noise! 😴 Sleep Sounds 10 Hours - YouTube | https://www.youtube.com/watch?v=gNYIG1GfgI8 | Music | 2/1/2024 22:32 | https://www.youtube.com/watch?v=gNYIG1GfgI8 | DLR | 0 | IFTV | |||||||||||
| 7 | A | sweet little sixteen - chuck berry 1958 - YouTube | https://www.youtube.com/watch?v=q_Eb5Fzjpeo | Music | 2/1/2024 19:58 | https://www.youtube.com/watch?v=q_Eb5Fzjpeo | DMC | 0 | IRecip | |||||||||||
| 8 | A | The Shirelles - Will You Still Love Me Tomorrow (1961) 4K - YouTube | https://www.youtube.com/watch?v=PAxb1vnb520 | Music | 2/1/2024 19:48 | https://www.youtube.com/watch?v=PAxb1vnb520 | Dexc | 0 | Jcarn | |||||||||||
| 9 | E | Off Grid w/ Jake & Nicolle - YouTube | https://www.youtube.com/@OffGridLife | DIY-Handyman-Misc | 2/1/2024 20:02 | https://www.youtube.com/@OffGridLife | E | 4 | JRecip | |||||||||||
| 10 | E | STARTING OVER - MY FAMILY GROWS, AS I LEAVE | NEW LIFE OFF GRID in the WILDERNESS - Ep. 180 - YouTube | https://www.youtube.com/watch?v=SRIyBP4vrnY | DIY-Handyman-Misc | 2/1/2024 20:01 | https://www.youtube.com/watch?v=SRIyBP4vrnY | F | |||||||||||||
| 11 | E | What is Cold Welding? (Advantages, Disadvantages and Applications) - TWI | https://www.twi-global.com/technical-knowledge/faqs/what-is-cold-welding | DIY-Handyman-Misc | 2/1/2024 21:47 | https://www.twi-global.com/technical-knowledge/faqs/what-is-cold-welding | FP | |||||||||||||
| 12 | E | Why don't people use these for floating shelves? - YouTube | https://www.youtube.com/watch?v=IJ1aRAK5KiM | DIY-Handyman-Misc | 2/1/2024 19:37 | https://www.youtube.com/watch?v=IJ1aRAK5KiM | G | |||||||||||||
| 13 | G | A BOY CRIED AT HIS MOTHER'S GRAVE, BEGGING HER TO TAKE HIM WITH HER, AND A MIRACLE HAPPENED... - YouTube | https://www.youtube.com/watch?v=u2Kl9rW9byM | Humor and Real Life, Humanity's Best | 2/1/2024 22:15 | https://www.youtube.com/watch?v=u2Kl9rW9byM | Hmot | |||||||||||||
| 14 | HFTV | Watch Elementary TV Online| FMovies | https://fmoviesz.to/tv/elementary-5k06j/2-1 | Movies-Films | 2/1/2024 21:29 | https://fmoviesz.to/tv/elementary-5k06j/2-1 | HFTV | |||||||||||||
| 15 | I | cold welding machine at DuckDuckGo | https://duckduckgo.com/?hps=1&q=cold+welding+machine&atb=v319-1 | DuckDuckGo | 2/1/2024 21:47 | https://duckduckgo.com/?hps=1&q=cold+welding+machine&atb=v319-1 | I | |||||||||||||
| 16 | I | how to clear your mind of thoughts so you can sleep at DuckDuckGo | https://duckduckgo.com/?hps=1&q=how+to+clear+your+mind+of+thoughts+so+you+can+sleep&atb=v319-1 | DuckDuckGo | 2/1/2024 22:03 | https://duckduckgo.com/?hps=1&q=how+to+clear+your+mind+of+thoughts+so+you+can+sleep&atb=v319-1 | Icarn | |||||||||||||
| 17 | I | how to get all surfaces of sausages to fry evenly at DuckDuckGo | https://duckduckgo.com/?q=how+to+get+all+surfaces+of+sausages+to+fry+evenly&atb=v319-1 | DuckDuckGo | 2/1/2024 19:26 | https://duckduckgo.com/?q=how+to+get+all+surfaces+of+sausages+to+fry+evenly&atb=v319-1 | Iexc | |||||||||||||
| 18 | I | how to get the curved portions of sausages to fry at DuckDuckGo | https://duckduckgo.com/?q=how+to+get+the+curved+portions+of+sausages+to+fry&atb=v319-1 | DuckDuckGo | 2/1/2024 19:25 | https://duckduckgo.com/?q=how+to+get+the+curved+portions+of+sausages+to+fry&atb=v319-1 | IFTV | |||||||||||||
| 19 | I | hxd hex editor at DuckDuckGo | https://duckduckgo.com/?t=crhs&hps=1&q=hxd+hex+editor&atb=v319-1 | DuckDuckGo | 2/1/2024 18:11 | https://duckduckgo.com/?t=crhs&hps=1&q=hxd+hex+editor&atb=v319-1 | IRecip | |||||||||||||
| 20 | I | k-lite codec windows 10 at DuckDuckGo | https://duckduckgo.com/?t=crhs&hps=1&q=k-lite+codec+windows+10&atb=v319-1 | DuckDuckGo | 2/1/2024 18:13 | https://duckduckgo.com/?t=crhs&hps=1&q=k-lite+codec+windows+10&atb=v319-1 | J | |||||||||||||
| 21 | I | sausages at DuckDuckGo | https://duckduckgo.com/?q=sausages | DuckDuckGo | 2/1/2024 19:25 | https://duckduckgo.com/?q=sausages | Jcarn | |||||||||||||
| 22 | J | 6 Ways to Shut Off Your Brain and Get Some Sleep I Psych Central | https://psychcentral.com/blog/why-cant-i-shut-off-my-mind | Health-Life-Homecare | 2/1/2024 22:04 | https://psychcentral.com/blog/why-cant-i-shut-off-my-mind | JRecip | |||||||||||||
| 23 | JRecip | How do I get sausages to cook evenly in a pan? - Seasoned Advice | https://cooking.stackexchange.com/questions/14434/how-do-i-get-sausages-to-cook-evenly-in-a-pan | Health-Recipes | 2/1/2024 19:26 | https://cooking.stackexchange.com/questions/14434/how-do-i-get-sausages-to-cook-evenly-in-a-pan | K | |||||||||||||
| 24 | JRecip | How to fry sausages properly in a pan I'll show you how I fry sausages in the pan to perfection - YouTube | https://www.youtube.com/watch?v=GJ6nE2Sg2iE | Health-Recipes | 2/1/2024 19:22 | https://www.youtube.com/watch?v=GJ6nE2Sg2iE | L | |||||||||||||
| 25 | JRecip | My go-to camp night Bic Mac Taco dinner #outdoorfood #campfood #outdoorcooking - YouTube | https://www.youtube.com/shorts/wK5McTWFHGg | Health-Recipes | 2/1/2024 21:05 | https://www.youtube.com/shorts/wK5McTWFHGg | M | |||||||||||||
| 26 | JRecip | My grandma cooks this dinner every 3 days! I've been making this pasta recipe for 20 years. - YouTube | https://www.youtube.com/watch?v=njxXM4mTMK8 | Health-Recipes | 2/1/2024 19:32 | https://www.youtube.com/watch?v=njxXM4mTMK8 | NMF | |||||||||||||
| 27 | NS | |||||||||||||||||||
| 28 | N | Get J2toP28 | ||||||||||||||||||
_01 | ||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A1 | A1 | =L1 |
| J1 | J1 | =COUNTA(A:A) |
| L1 | L1 | =EXTRACTNUMBERS(P1,TRUE) |
| M1 | M1 | =SUM(L1+0) |
| O1 | O1 | =ADDRESS(N1,2) |
| P1 | P1 | =COUNTA(E:E) |
| R1 | R1 | =UniqueList!$G$1 |
| N1 | N1 | =EXTRACTNUMBERS(M1) |
| N2 | N2 | =COUNTIF($A:$A,$O$2) |
| N3 | N3 | =COUNTIF($A:$A,$O$3) |
| N4 | N4 | =COUNTIF($A:$A,$O$4) |
| N5 | N5 | =COUNTIF($A:$A,$O$5) |
| N6 | N6 | =COUNTIF($A:$A,$O$6) |
| N7 | N7 | =COUNTIF($A:$A,$O$7) |
| N8 | N8 | =COUNTIF($A:$A,$O$8) |
| N9 | N9 | =COUNTIF($A:$A,$O$9) |
| D2:D26 | D2 | =IF((ISERROR(XLOOKUP(A2,'01Sort-Types'!$R$47:$R$71,'01Sort-Types'!$S$47:$S$71,0))),"",(XLOOKUP(A2,'01Sort-Types'!$R$47:$R$71,'01Sort-Types'!$S$47:$S$71,0))) |
| G1 | G1 | =HYPERLINK("[02-FebruaryTest.xlsm]'UniqueList'!a"&(COUNTA(AS:AS)+R1),"Hyperlink & Paste in UniqueList") |
| G2:G26 | G2 | =HYPERLINK(C2) |
| 02-February.xlsm | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 1 | Type | Site Title | URL | Link Category Per Color | Visit Time | 6 | 7 | 7 | $B$7 | 6 | ï A Column Count | ||
| 2 | HFTV | Watch Elementary TV Online| FMovies | https://fmoviesz.to/tv/elementary-5k06j/2-1 | Movies-Films | 1/31/2024 21:29 | ||||||||
| 3 | JRecip | How do I get sausages to cook evenly in a pan? - Seasoned Advice | https://cooking.stackexchange.com/questions/14434/how-do-i-get-sausages-to-cook-evenly-in-a-pan | Health-Recipes | 1/31/2024 19:26 | ||||||||
| 4 | JRecip | How to fry sausages properly in a pan I'll show you how I fry sausages in the pan to perfection - YouTube | https://www.youtube.com/watch?v=GJ6nE2Sg2iE | Health-Recipes | 1/31/2024 19:22 | ||||||||
| 5 | JRecip | My go-to camp night Bic Mac Taco dinner #outdoorfood #campfood #outdoorcooking - YouTube | https://www.youtube.com/shorts/wK5McTWFHGg | Health-Recipes | 1/31/2024 21:05 | ||||||||
| 6 | JRecip | My grandma cooks this dinner every 3 days! I've been making this pasta recipe for 20 years. - YouTube | https://www.youtube.com/watch?v=njxXM4mTMK8 | Health-Recipes | 1/31/2024 19:32 | ||||||||
UniqueList | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F1 | F1 | =EXTRACTNUMBERS(J1,TRUE) |
| G1 | G1 | =SUM(F1+1) |
| H1 | H1 | =EXTRACTNUMBERS(G1) |
| I1 | I1 | =ADDRESS(H1,2) |
| J1 | J1 | =COUNTA(A:A) |
- just in case it's not clear, the use of the # symbol in this way is very specific to the HYPERLINK() function.
