AutoFill Hyperlinks

yorkbay

New Member
Joined
Feb 7, 2021
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
Hi - is there a way to drag and drop hyperlinks to incrementally add to each cell?

Sheets 1 thru 10 have sets of questions i.e. Sheet 1 has rows 1:120 questions, Sheet 2 has 1:60, Sheet 3 has 1:42 etc.
Sheet 2 is a master sheet with all of the questions
Both sheets have A, B, C and D with specific data that never changes
Both sheets have E, F, G etc which contain responses to the questions and each of these columns has a unique header

The Admin who reviews Sheet 2 (the consolidated view) needs to validate responses and use Excel comments to post a message for the person who input the data if something needs to be corrected or updated i.e. change this rate or change this time zone etc.
The next person who comes in and sees the note needs to find the header that the note applies to as well as the question and click a link to take them to the appropriate sheet and row # of the question (the rows are not 1:1 on the master due to the fact that it's a consolidation of the other sheets).
I was thinking I could paste a hyperlink on the actual question which would bring the user at least to the proper sheet and row. They would then have to manually find the correct header and make the change.

Thoughts? Maybe there is a better way or something that could bring them directly to the header and cell to make the change? The master columns are not in the same order as the individual sheets because not all headers are on all sheets etc. If there isn't a better way, can someone help me determine how to create this hyperlink for each cell without having to do them individually?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
It would be a challenge to come up with the formula without actually seeing your workbook. I have done similar formulas to auto generate hyperlinks based on the MATCH function...perhaps you could follow the lead and modify it for your purposes. In this case I have the formula in K47...it will look for a number in J47 and match that to a row on the 'test cases' tab in order to hyperlink to the correct spot. The hyperlink in this case will read "Link to test xxx". If there is no number in J47 then the text will read "No test available". The '+5' in the formula was to adjust the rows for the number of header lines I have...adjust yours to get the hyperlink to the expected line.

=IFERROR(HYPERLINK("#'Test Cases'!A"&MATCH(J47,'Test Cases'!A$6:A$5003,0)+5,"Link to test "&J47),"No Test Available")
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

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