input box in formula

[kenpcli]

Hi Everyone

I need a script to replace the '08-05 TO 08-11' with an input box. I need the user to define which pay period they are wanting to lookup and then place that formula into cell D2.

=IFERROR(VLOOKUP(C2,'08-05 TO 08-11'!$C:$Q,2,0),"")

Thanks

 

[Fluff]

How about

   Dim Sht As String
   Sht = InputBox("Please enter the sheet name")
   Range("D2").Formula = "=IFERROR(VLOOKUP(C2,'" & Sht & "'!$C:$Q,2,0),"""")"

 

[kenpcli]

Thanks worked great.

 

[Fluff]

glad to help & thanks for the feedback

 

[Rick Rothstein]

I need a script to replace the '08-05 TO 08-11' with an input box. I need the user to define which pay period they are wanting to lookup and then place that formula into cell D2.

=IFERROR(VLOOKUP(C2,'08-05 TO 08-11'!$C:$Q,2,0),"")

Instead of an InputBox, you could devote one cell somewhere in your workbook to the sheet name and then bundle that into your formula via the INDIRECT function. For example, let's say you devoted cell B2 on the same sheet your formula is on (otherwise you would have to include a sheet name when referencing cell B2 on that sheet). Then your formula would become...

=IFERROR(VLOOKUP(C2,INDIRECT("'"&B2&"'!$C:$Q"),2,0),"")