I am creating a budget spreadsheet and wish to have a macro run
automatically and navigate to a specific cell of a different worksheet
based on a determined value of a cell.

For example, Cell A5 in the MAIN worksheet uses a drop down list from a
named range (RFP, MOA, PSC ...) from another worksheet. If the value
is "RFP" then I wish to navigate automatically to cell A1 of the
REQUEST worksheet where additional information is completed related to
"RFP". I'd reverse the process on the last cell of the REQUEST
worksheet, sending the user back to the MAIN worksheet to continue on.
Any ideas??