Transferring cell content between workbooks using cell references

[Kiwi Mike]

Hi group

I want to transfer cell content onto a spreadsheet that has a list of
cell references pertaining to a series of other workbooks. The cell
references are in the A1 reference style and there are about 800 from
8 different workbooks. Is there an efficient way (macro, formulas?)
that you can do this – I can't seem to see the wood for the trees. If
anyone has any ideas it would be appreciated.

Cheers,
Mike

 

[Frank Kabel]

Hi
not really sure what you want to transfer. Do you want to transfer the
values returned from these formulas?

 

[Kiwi Mike]

Hi Frank

It's probably easier if I give an example. The list has about 800 cell
references from different workbooks.

Eg.
A325
B24
A43
D421
A152
Etc, etc…


In another workbook these cells have a numerical value in them eg.
FN00235. It is my objective to transfer these values to the one column
if possible. It is easy enough to paste the workbook reference eg.
‘[workbook.xls]worksheet!A325 which will return the value however this
will take me forever. Can you see what I'm getting at? Cheers for
replying.

Mike

 

[Gromit]

Hi

Assuming your list is in column A, type "[workbook.xls]worksheet!" i
cell b1 and =INDIRECT(b$1 & a1) in cell c1.

Then drag down cell c1 to fill in the correct references.

 

[Kiwi Mike]

Hi

Assuming your list is in column A, type "[workbook.xls]worksheet!" in
cell b1 and =INDIRECT(b$1 & a1) in cell c1.

Then drag down cell c1 to fill in the correct references.

G

Gromit

I see where you're coming from and I've tried it but its returned the
#REF error. Not sure why as the formula seems to be the one I'm
looking for. Any thoughts?

Cheers,
Mike

 

[Gord Dibben]

Do you have both workbooks open?

If the referenced workbook is closed, then =INDIRECT() won't work.

Harlan Grove wrote a UDF that allows you to retrieve values from a closed
workbook when you build that string that points at the
drive/folder/filename/sheetname/range address.

http://www.google.com/[email protected]

And more here for alternatives from Harlan and Frank Kabel.........

http://snipurl.com/awej


Gord Dibben Excel MVP

Hi

Assuming your list is in column A, type "[workbook.xls]worksheet!" in
cell b1 and =INDIRECT(b$1 & a1) in cell c1.

Then drag down cell c1 to fill in the correct references.

G

Gromit

I see where you're coming from and I've tried it but its returned the
#REF error. Not sure why as the formula seems to be the one I'm
looking for. Any thoughts?

Cheers,
Mike

 

[Kiwi Mike]

Hi Gord

I do have the referenced workbook open but I still seem to get that
error. The referenced workbook is shared - I'm not sure if that would
complicate matters. Thanks for the effort.

Mike

Do you have both workbooks open?

If the referenced workbook is closed, then =INDIRECT() won't work.

Harlan Grove wrote a UDF that allows you to retrieve values from a closed
workbook when you build that string that points at the
drive/folder/filename/sheetname/range address.

http://www.google.com/[email protected]

And more here for alternatives from Harlan and Frank Kabel.........

http://snipurl.com/awej


Gord Dibben Excel MVP

Hi

Assuming your list is in column A, type "[workbook.xls]worksheet!" in
cell b1 and =INDIRECT(b$1 & a1) in cell c1.

Then drag down cell c1 to fill in the correct references.

G

Gromit

I see where you're coming from and I've tried it but its returned the
#REF error. Not sure why as the formula seems to be the one I'm
looking for. Any thoughts?

Cheers,
Mike