Creating a reference using text in another cell...

[drawkcaB]

Hi everyone,

I'm trying to create a formula that will return values from another workbook and that formula would use a text string for the file location. I'm not sure if I'm explaining it right, so it's probably best to describe what I'm hoping to achieve.

I'm working on a purchase order for roughly 200 transmitters for my engineering company on behalf of our client. Even though there's 200 transmitters, the nature of the project means that in reality there are only 8 "groups" of transmitters, with each group having the exact same information and requirements. So, I had this idea of creating 8 "master files" which I can change and in doing so, change the information of every data sheet associated to that master file. This lets me vastly shorten the amount of time needed to change these datasheets.

That part I got working by using simple references like ='(file location) '!$A$1 for instance.

It dawned on me that everytime the datasheets would go through a revision, the user would have to go into every single data sheet and change the location or name of the "master file" in order to properly link these new revised data sheets with the correct new, revised master file.

So I got the idea of using the data sheet file names as a way to link the proper data sheet revision to the proper master file revision. So if the original files were Datasheet Rev. A and Master Rev. A and now we moved onto revision B, then Datasheet Rev. B would link automatically to Master Rev. B.

What I've got so far is using =CELL("filename",A1) in my test data sheet to gets the data sheets filename, using =RIGHT(cell with the filename, 3) to extract the revision number, then using concatenate to create the location of the revised master file.

Ex.)

=CELL("filename",A1) returns N:\...\[test data sheet Rev.B.xls] in cell W5
=RIGHT(W5, 5) returns B.xls] in cell W6

Cell W14 = N:\...\[test master file Rev.

then concatenate to create the desired file location

=CONCATENATE (W14, W6) returns N:\...\[test master file Rev. B.xls] in cell W15

and this is where I get stuck. I want to use this new location of the master file in the reference cells on the data sheet. I tried doing ='W15 '!$A$1 but that did nothing. I also named W15 "Master" and used ='Master '!$A$1 but doesn't work either. I have a feeling the answer isn't that complicated, but I'm stumped.

Please help!

[NBVC]

You need to use INDIRECT function I think,

=INDIRECT("'"&W15&"'!A1")

but that only works if the reference workbook is opened.

An alternative is to download a free addin from here: Morefunc.xll

and use
=INDIRECT.EXT("'"&W15&"'!A1")

[drawkcaB]

So my understanding of using the indirect function above is that it'll reference the master file (&W15&) and return the desired cell (!A1) and return as a string (hence why it's in quotation marks " "). If the reference cell is $K22:$O22 then the formula should be =INDIRECT.EXT("'"&W15&" '!$K22:O22"), yes?

At any rate, didn't work. It says missing reference #REF!. I checked and double checked the location of the master file and the location listed in W15 and they're the same...

[NBVC]

There is no space between the " and '

e.g

=INDIRECT.EXT("'"&W15&"'!$K22:O22")

but not sure what you are trying to do in the end. That formula as is won't work and may give you the #VALUE error.

[6StringJazzer]

Late post, already addressed above

[drawkcaB]

There is no space between the " and '

e.g

=INDIRECT.EXT("'"&W15&"'!$K22:O22")

but not sure what you are trying to do in the end. That formula as is won't work and may give you the #VALUE error.

Still gives me a #REF! error. I'd be more enthusiastic about a #VALUE error to be honest

The goal is to make a data sheet reference the appropriate master file to populate its fields. If a datasheet is named datasheet Rev. A.xls, it should automatically search for masterFile Rev. A.xls to populate its fields (almost all of which will refer to the appropriate cells in the masterFile). If its called datasheet Rev. B.xls, then it should use masfterFile Rev. B.xls, and so on.

I'm trying to make it so that the data sheet can be updated in numerous revisions without ever having to change the formulas in its cells.

[NBVC]

The REF error means it can't find a workbook or worksheet within the workbook of that name.

Are you sure it is all correctly referenced? The entire path.

Go to Formula tab and click Evaluate formula and evaluate it... to see how it is interpreting the W15 entry.

[drawkcaB]

I took the cell above the one I'm trying to change and made a reference using = and just clicking on the cell in the masterfile, that worked fine and it displays the desired value "anything!". The file paths look the same on both the working cell and the broken one, but the broken one (the one using the INDIRECT function) has this as well at the end:

  =INDIRECT.EXT("'"&W15&"'!$K22:O22")

Other than that, there's more quotation marks than when I do an evaluate on a working cell that uses a formula such as ='[Master File Rev. A.xls] '!$K22:$O22.

[drawkcaB]

Got it working, thanks for all the help NBVC!