Linking to another worksheet cell content in a cell formuka

[mikjp]

Hoping that is not too confusing!!

Take a cell formula eg =if(VLOOKUP(A1,'C:\[Foo.xls]Sheet1'!R4C1="99","1","0")

I would like to store the worksheet reference - C:\[Foo.xls]Sheet1 - in another workbook. I cannot work out how to write the Vlookup using such a link.

 

[treaves04]

The easiest way to enter a reference to an external table, is to begin entering the VLOOKUP function normally. Then, when entering the table_array argument, browse to the the external workbook and select the range directly. Excel will construct the needed reference automatically.

Typically, In these situations, I will break the formula down. Try writing just the vlookup portion first, and selecting the data you want from the other workbook. That will mak Excel generate the links propery. Then use F2 to edit the formula to add your IF Statement.

 

[mikjp]

I think I was not clear in what I asked - apologies! I am ok with the concept of selecting the other table and having Excel place the reference.

It may be easier if I reproduce the actual formula, which is quite complicated, for which humble apologies!

=IF(AND(RC[-8]="",RC[-8]<300),"",VLOOKUP(RC[-8],'C:\[UserData.xls]Sheet1'!R4C1:R204C16,2,FALSE))

I am having brain-fade trying to work out how the various 'and !'s fit into a linked location.

This sort of formula, referencing a few other data sheets, occurs across several columns in different forms, and any move of the data files to a different location currently requires me to edit the "C:\UserData.xls" bit for each of a few different formulae and then copy the formula down several rows in each of the columns - and, bless me! - that is on two similar sheets into the bargain! A lot of work. I had hoped I could short-circuit a lot of the effort by storing the workbook locations in a cell somewhere to load into the formulae.

It may be that this is an ask too far and I will just have to bite the bullets!

When this bit of fun is over I need to convert the whole shooting match into LibreOffice Basic...................

Anyone for masochism...?

 

[treaves04]

I understand better what you are asking now, but it is out of my league. Have you considered a helper cell and using =CONCANATE to build the path?

 

[mikjp]

No, but I will look at that and thanks muchly for your help.

 

[mikjp]

No progress there, I fear.

To recap in case anyone can assist:
I need to replace C:\[UserData.xls]Sheet1 in the given code

=IF(AND(RC[-8]="",RC[-8]<300),"",VLOOKUP(RC[-8],'C:\[UserData.xls]Sheet1'!R4C1:R204C16,2,FALSE))

with the same text but taken taken from a cell in another workbook, let's say Cell 1,1 in "C:Foo.xls" I cannot find the syntax to place this cell content in my original formula.

I have the expression

=[Foo.xls]Sheet1!$A$1

as the text file location data (C:\[UserData.xls]Sheet1) recorded in Foo.xls

Try as I might I cannot find the right combinations of ' and \ and ! and [ ] etc to put it in the given code to replace the 'typed' file location. It has to be blindingly simple, but then so am I.......