Copy data automatically from another workbook

[CACustom]

Invoices are saved as individual workbooks. Have a separate collections
workbook where user will enter the invoice number into cell in column B
(invoice number is same as filename of invoice, i.e. 08132R).
Need code to have invoice information automatically fill in across the row
where the invoice number was entered.

 

[J Sedoff comRemove>]

Check out the article here:
http://support.microsoft.com/kb/213933

It talks about the Indirect() function which allows you to use values within
a cell to create an address/file path. It should be right up your alley!

If you still have questions, I'd be glad to clarify and help.

Hope this helps, Jim

 

[CACustom]

Thank you for your response, however the problem with INDIRECT is the source
files would all have to be open and remain open anytime the collection
spreadsheet is opened. There will be times when there are over 100 source
files being listed on the collection spreadsheet.

 

[J Sedoff comRemove>]

In that case, use VBA macros. Something in the vein of:

'Contains the path "='T:\certifcation\Skimming Trans\2008\Jul 08\8456318R"
for the month of July 2008
file_path = "='" & the_path & this_year & "\" & this_month & " " & _
Format(yesterday, "yy") & "\" & cells(1,2).value
cells(1,1).formula = "=" & file_path

For example you'd like something like the following in a particular cell,
"='DriveLetter:\Folder1\Folder2\[FileName.xls]SheetName (2)'!$K$12"
which gives
=C:\My Documents\Music\[My Cds.xls]Favorite Country'!$K$12

Obviously that won't get you all that you need, but do you at least get the
general idea?

If you use macros, the values will still update without needing to have
those files open, and it creates a direct link.

Hope this helps, Jim

 

[CACustom]

Thank you. This put me in the right direction. I was able to get the macro to
do what I wanted it to using the input cell value as a variable in the code -
and used the offset function to enter the links into the related cells in the
row.
It's working! I will just need to tweak a few things to make it more user
friendly.
Thank you!

 

[J Sedoff comRemove>]

Great! Thanks for the feedback! Glad to help, Jim