Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data automatically from another workbook
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. -- CA Custom Payroll |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data automatically from another workbook
Check out the article he
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 -- I appreciate any feedback. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data automatically from another workbook
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. -- CA Custom Payroll "J Sedoff" wrote: Check out the article he 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 -- I appreciate any feedback. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data automatically from another workbook
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 -- I appreciate any feedback. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data automatically from another workbook
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! -- CA Custom Payroll "J Sedoff" wrote: 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 -- I appreciate any feedback. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data automatically from another workbook
Great! Thanks for the feedback! Glad to help, Jim
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enter in a workbook and copy to another automatically | Excel Programming | |||
How do I automatically copy data from one workbook to another? | Excel Worksheet Functions | |||
Copy A Workbook Automatically | Excel Programming | |||
Copy a Workbook Automatically | Excel Worksheet Functions | |||
loop through a column on a workbook copying data on each row to another workbook, then copy data back to the original workbook | Excel Programming |