Vlookup - Update Path

[Clyde Dickson]

I have a spreadsheet that looks up certain daily production figures. I would
like to update the path or create a path linked to certain cells. This is the
path \Factory Figures\Material\Daily Yield\YieldWK20\24AUG
How can I easily change THE 2 VARIABLES YIELDWK20 to 21 after 7 Days and
24AUG to 25AUG ?

 

[Sam Wilson]

You can use =INDIRECT()

Look at the help for this function, but as an example if I had a function

=VLOOKUP(A1,B1:C10,2,false) and the string "B1:C10" in cell D1 I could use:

=VLOOKUP(A1,INDIRECT(D1),2,false) instead.

You will need something like

=VLOOKUP(A1,INDIRECT("\Factory Figures\Material\Daily Yield\YieldWK" & X1 &
"\" & Y1),2,false)

where X1 is your 20 or 21, and Y1 your 24AUG etc.

Sam

 

[Dave Peterson]

I would just use Edit|Replace

But be careful--you want to give it a unique string to change.

Edit|replace
what: \YieldWK20\24AUG
with: \YieldWK21\25AUG
replace all

 

[Dave Peterson]

If that sending file is not open, then =indirect() returns an error.

(saved from a previous post)

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.