Replace command in formula

[Corleone]

Good morning all

I would like to create a replace all command where it replaces the first section of the formula which is quoted
with whatever is in Cell G1

so for example if the formula is

=VLOOKUP("CP6 Opportunities & CP7 Development",'Head Count - Out Turn'!$E$7:$K$1710,2,0)

and i then select "Remitted Schemes" in the drop down in cell G1 it then changes the formula to

=VLOOKUP("Remitted Schemes",'Head Count - Out Turn'!$E$7:$K$1710,2,0)

Thanks in advance

 

[RoryA]

Why can't you just use:

=VLOOKUP($G$1,'Head Count - Out Turn'!$E$7:$K$1710,2,0)

instead of changing a literal value in a formula string?

 

[Corleone]

Why can't you just use:

=VLOOKUP($G$1,'Head Count - Out Turn'!$E$7:$K$1710,2,0)

instead of changing a literal value in a formula string?

I think i was have whats known technically as a "Brain Fart" - you are quite correct - many thanks for your help

 

[RoryA]

(been there!)

Glad to help.