How to create a formula to combine IF and HYPERLINK in Excel

[Excel_apprentice]

I have a spreadsheet generated from MS Forms and there are a lot of columns, to make it easier to use for my team I want to add in some hyperlinks where depending on the text in a column it will take them to another column in the document. As I understand this would need to combine the IF and Hyperlink formulas but I can't get it to work. Here's an example spreadsheet (attached).

Would really appreciate any thoughts.

[Pete_UK]

So, if you clicked on Red (in A3) would you want the cursor to jump to C3 (i.e. on the same row), or to C2 ?

Do you already have those colours in column A, and does that mean that you want the HYPERLINK formula in a separate column?

Pete

[Excel_apprentice]

So, if you clicked on Red (in A3) would you want the cursor to jump to C3 (i.e. on the same row), or to C2 ?

Do you already have those colours in column A, and does that mean that you want the HYPERLINK formula in a separate column?

Pete

Hi Pete, thanks for the quick reply. Yes that's exactly what I want.

I want the formula to set specific conditions depending on what colour has been selected in column A and then depending on what is put to jump to the relevant colour column. The hyperlink can be added to another column, that's no problem. So could add a column to the right with the relevant hyperlink.

[Pete_UK]

In the sample file that you submitted, insert a new column B, then copy A1 into B1. Put this formula in B2:

=IF(A2="","",HYPERLINK("#Sheet1!R"&ROW()&"C"&MATCH(A2,$A$1:$F$1,0),A2))

and copy this down as far as you like. You can then hide column A.

Then just click on any colour in column B and the cursor will move across on the same row to the appropriate column, based on the headings in C1:F1.

Note that the formula uses R1C1 notation for the cell reference, so it might look a bit different to what you are used to.

Hope this helps.

Pete

[Excel_apprentice]

In the sample file that you submitted, insert a new column B, then copy A1 into B1. Put this formula in B2:

=IF(A2="","",HYPERLINK("#Sheet1!R"&ROW()&"C"&MATCH(A2,$A$1:$F$1,0),A2))

and copy this down as far as you like. You can then hide column A.

Then just click on any colour in column B and the cursor will move across on the same row to the appropriate column, based on the headings in C1:F1.

Note that the formula uses R1C1 notation for the cell reference, so it might look a bit different to what you are used to.

Hope this helps.

Pete

Thanks Pete. That worked perfectly on the test sheet, however on the sheet I'm going to be using it's a response sheet for MS Forms and will be automatically populating. Is there anyway to use this formula without having to copy over the text manually - for it to reference A2 rather than B2 when applying the formula?

[Pete_UK]

I've never used MS Forms, so I can't advise on how to interface with that. The formula (in B2) does reference A2, so I'm not sure what you mean by that comment. You can't have both a formula and a value in the same cell.

Pete