Driving an address

[LiAD]

Hi,

If I have the address, file name, sheet name and cell ref of a source file
typed into cell A1 is there a way in which I can use the text in cell A1 as
an address to drive an = formula?

I only want to copy the data in one cell, the same cell ref in every folder.
In the results file all I need is an =(reference) formula.

The reason I would like this is to save me having to recreate a lot a lot of
references (one for every day of every year).

It will always be the same cell ref in every single file and sheet. The
address type is in the form of:
Network/Directory/Sub directory 1/Sub directory 2/ Sub directory 3/Sub Dir 4
(Year)/Sub Dir 5 (Month)/File name/Sheet

Example addresses contained within col A are; (to find data in G12, on 3 Mar
et 15 Apr 2009 from the plastic cartons from UK)

C:/UK/Cartons/Plastic/Results/2009/3/ Production results Mar/3/G12
C:/UK/Cartons/Plastic/Results/2009/3/ Production results Apr/15/G12

In col B I would like something very simple, just an = before the address
above that will return the data within the cell.

The idea is then if want to change the address I change UK fro France and it
automatically searches a different folder.

Is it possible to drive an address from the contents of a cell?

Thanks
LiAD

 

[Pete_UK]

It is possible, and you would normally use the INDIRECT function to do
this. However, that function will only work if the "sending" workbook
is open at the same time as the "receiving" workbook. Is this
something that you can arrange to happen?

If not, then there is a free add-in called morefunc (do a Google
search to find sites where you can download it from). This has the
function INDIRECT.EXT, and this will work if the sending workbook is
closed. Are you allowed to install add-ins onto your computer?

Hope this helps.

Pete

 

[LiAD]

Hi,

Thanks for helping.

Yes it is possible to have sending and recieving workbooks open at the same
time - HOWEVER not all of the sending workbooks. I have 12 sending files per
year (one/month) each with 31 sheets (one for each day of the month). So
every day (except weekends) some-one will open A sheet within A relevant
sending file (the relevant one for the day & month they are in). They will
not open every sending file.

In the same way within the recieving workbooks I will have maybe 10 sheets,
each sheet will be for a different machine. Machine 1 will get the data from
G12, machine 2 from F18 etc etc.

I assume the file being open is enough? Or does the user need to activiate
every sheet?

I guess the best way to deal with it is
- have the INDIRECT formula you described for every day for every machine
- the user opens both files say once per week and the data gets copied
- have a macro that once the data is copied it does a copy/paste values each
to get rid of the 'used' formulas.

What do you think?

This is what I have tried but it doesnt work. I think there is a problem
with the cell ref. Do you know what I need to do to get it to work?

Ref made from =$C$2&"\"&$C$3&"\"&$C317&"\"&$D317&"\"&$C$4
Ref address - C:\dddd\Test\UK\Plastic\2009\11\1\M20
Formula - =INDIRECT(E317)

Thanks

 

[Pete_UK]

I presume M20 is the cell reference, but I'm not sure which is the
sheet name and which is the filename in your expression. The filename
needs the .xls added and needs to be enclosed in square brackets. You
also have to include the sheet name. It is also a good idea to enclose
the whole string with apostrophes, to account for any spaces in the
path or filename or sheetname. So, if you have the full path in A1,
the filename in B1 and the sheetname in C1, for example, your ref
formula may look like this:

="'"&$A$1&"\["&$B$1&".xls]"&C1&"'!"

If this formula is in D1, and you want to return data from cell M20 of
that workbook/sheet, then you would have:

=INDIRECT(D1&"M20")

or you could include the M20 at the end of the formula in D1 and just
have:

=INDIRECT(D1)

But, that workbook will have to be open for this to work - if it is
not, you will get an error.

Hope this helps.

Pete

 

[LiAD]

Yeah works perfect now.

Thanks a lot

I presume M20 is the cell reference, but I'm not sure which is the
sheet name and which is the filename in your expression. The filename
needs the .xls added and needs to be enclosed in square brackets. You
also have to include the sheet name. It is also a good idea to enclose
the whole string with apostrophes, to account for any spaces in the
path or filename or sheetname. So, if you have the full path in A1,
the filename in B1 and the sheetname in C1, for example, your ref
formula may look like this:

="'"&$A$1&"\["&$B$1&".xls]"&C1&"'!"

If this formula is in D1, and you want to return data from cell M20 of
that workbook/sheet, then you would have:

=INDIRECT(D1&"M20")

or you could include the M20 at the end of the formula in D1 and just
have:

=INDIRECT(D1)

But, that workbook will have to be open for this to work - if it is
not, you will get an error.

Hope this helps.

Pete

Hi,

Thanks for helping.

Yes it is possible to have sending and recieving workbooks open at the same
time - HOWEVER not all of the sending workbooks. I have 12 sending files per
year (one/month) each with 31 sheets (one for each day of the month). So
every day (except weekends) some-one will open A sheet within A relevant
sending file (the relevant one for the day & month they are in). They will
not open every sending file.

In the same way within the recieving workbooks I will have maybe 10 sheets,
each sheet will be for a different machine. Machine 1 will get the data from
G12, machine 2 from F18 etc etc.

I assume the file being open is enough? Or does the user need to activiate
every sheet?

I guess the best way to deal with it is
- have the INDIRECT formula you described for every day for every machine
- the user opens both files say once per week and the data gets copied
- have a macro that once the data is copied it does a copy/paste values each
to get rid of the 'used' formulas.

What do you think?

This is what I have tried but it doesnt work. I think there is a problem
with the cell ref. Do you know what I need to do to get it to work?

Ref made from =$C$2&"\"&$C$3&"\"&$C317&"\"&$D317&"\"&$C$4
Ref address - C:\dddd\Test\UK\Plastic\2009\11\1\M20
Formula - =INDIRECT(E317)

Thanks
















- Show quoted text -

.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Yeah works perfect now.

Thanks a lot

I presume M20 is the cell reference, but I'm not sure which is the
sheet name and which is the filename in your expression. The filename
needs the .xls added and needs to be enclosed in square brackets. You
also have to include the sheet name. It is also a good idea to enclose
the whole string with apostrophes, to account for any spaces in the
path or filename or sheetname. So, if you have the full path in A1,
the filename in B1 and the sheetname in C1, for example, your ref
formula may look like this:

="'"&$A$1&"\["&$B$1&".xls]"&C1&"'!"

If this formula is in D1, and you want to return data from cell M20 of
that workbook/sheet, then you would have:

=INDIRECT(D1&"M20")

or you could include the M20 at the end of the formula in D1 and just
have:

=INDIRECT(D1)

But, that workbook will have to be open for this to work - if it is
not, you will get an error.

Hope this helps.

.- Hide quoted text -

- Show quoted text -