Link cell from variable named sheet to cell on another sheet with variable rows

Hello all. I need to link cells in a sheet with a name provided by the user "shtName", to a cell in row Q on sheet "SNSs". I have used:

Range("Q9").Formula = "='" & shtName & "'!B60"

to link cells that are in static rows, but in this case the rows on the SNSs sheet will be variable. The process is that when the frmScoutEntry is run, the user enters scout information and the applicable sheet and entries are made on other sheets. When the new scout name is created on the SNSs sheet, I need the cell Q9 to be linked to B60 on the newly created sheet (the name is held in the variable shtName). The next time the code is run, a new scout will be added to the SNSs sheet so the previous scout linked cell is now Q11. The value in still needs to remain linked to the previously added scout.

I thought perhaps an IF statement in the code could match the scout names on the SNSs and scout-named sheets and link the applicable cell in Q to the scout sheet B60 but I have never been good with IF statements:

If Sheets("SNSs").Range("A:A") = shtName.Range("$A$2" & " " & "$A$3") Then
    shtName.Range("B60")=Sheets("SNSs").Range.offset()16

Any help would be appreciated.

Thanks,
Andrew

Thanks for the reply StephenR. I was thinking along the same lines and did the following:

On the SNSs sheet, I added a column B (shifted the others to the right) and use the following formula copied down:

=A7 & " " & C7

This combines the scout name with either Pack or Council so each row is identified seperately. After the next scout is added to the sheet the code creates a named range :

SNSNames

that includes all rows and columns needed for the vlookup.

The code then selects the new scout sheet and places the vlookup formula:

newSht.Select
    Range("B60") = "=VLOOKUP(B59,SNSNames,17,FALSE)"
    Range("B61") = "=VLOOKUP(D59,SNSNames,17,FALSE)"

All the code runs without an error but I get a #N/A in B60 and B61 instead of 0 (since no values have yet been added to the scout's SNSs rows).

Any idea how to correct this?

Thanks,
Andrew