Conditional HYPERLINKS to worksheet tab based on a cell value

[MarkCrad]

I've tried VLOOKUP, nested IF statements, and VBA code without luck. (I'm not well-versed in VBA!)
Here's what I'm trying to do:
- I want a user to enter their User ID into worksheet "Input", cell A1 as shown in Sample1 attached.
- In "worksheet "Input", cell B1, I want a formula or function that searches cell A1 in the worksheets "eb6731, "nb4523", "sb2521", or "bc3866" and when it finds the match, then do one of the following:
1. Create a clickable link in worksheet "Input", cell B1 to the corresponding Employee tab ("eb6731, "nb4523", "sb2521", or "bc3866") allowing the employee to click the link to their timesheet to enter their time
OR
2. Use the existing Link in in worksheet "User_Links" column B
OR
3. Suggest another method that allows a user to input a User ID that will take them to their respective Employee tab.

- If no match is found, return message "Invalid User ID" in cell C1 and clear the contents in cell A1, allowing the employee to try again.

When employee finishes entering time and clicks the link to return to the "Input" worksheet, cell A1 in the "Input" worksheet is cleared for the next employee.
Any help is greatly appreciated!

 

[felixstraube]

How about this?:

Hyperlink.xlsx
	A	B
1	eb6731	Go to employee page…
Input

Cell Formulas
Range	Formula
B1	B1	=IF(ISNUMBER(ROW(INDIRECT("'"&A1&"'!A1"))),HYPERLINK("["&TEXTBEFORE(TEXTAFTER(CELL("filename"),"["),"]")&"]'"&A1&"'!A1","Go to employee page…"),"Invalid user ID")

I have to warn you. This will only work on desktop version of excel for windows. No Web or MAC version will work.

And to return to the input page from every employee page you can create a macro like this:

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Input").Select
    Range("A1").Select
    Selection.ClearContents
End Sub

And asigne it to a rectangle or button in every employee page.

 

[MarkCrad]

The hyperlink works like a champ! Thank you for that.
When I try to run the macro, I get a VBA error 400.

 

[felixstraube]

Try recording the macro yourself.
1. Go to on of the employee sheets.
2. Start recording the macro.
3. Click on "Input" sheet.
4. Click on A1 cell.
5. Pres Del on the keyboard.
6. Stop recording macro.

 

[MarkCrad]

Worked great! Thank you for your expertise!! Certainly saved me a lot of of time and trial and error.

 

[felixstraube]

Happy to help. Thanks for the feedback

 

[smozgur]

Worked great! Thank you for your expertise!! Certainly saved me a lot of of time and trial and error.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.