Forum Discussion
Solved
I want to send data across two workbooks depending on the condition explained below.
I want to link two workbooks in such a way that....
In source workbook, I have date entered as a sheet name and in this sheet, there are some values which needs to be copied to another destination workbook.
In destination workbook, all year dates are available in one of the column. Now, I want to copy value from source workbook to destination workbook only when it matches the date . In source workbook, date is available as sheet name and destination workbook, date is available in column.
Sorry but its the business requirement that it has to be like this.
Source workbook is available for all the members of team but Destination workbook is accessible to higher management only.So THAT is the answer to the question Patrick2788 asked; not the bit about "a lot of other data." Still doesn't really explain a separate tab for each day, but if that's the way it is (inefficient) that's the way it is.
I've attached samples of how INDIRECT can work with your situation. As noted above, however, be forewarned that INDIRECT uses a lot of resource as a "volatile" function
It would help us help you if you would be so kind as to post either the actual workbooks OR a mockup that faithfully recreates the conditions of your real ones. Post them on OneDrive or GoogleDrive with a link pasted here that grants access.
That said, if you would prefer to continue to try to work it out on your own, let me offer the following based on my own experience of drawing from two workbooks to populate data in a third.
- The INDIRECT function will enable you to do this. The highlighted word there is a link to a good resource in explaining that function. You would also be able to find quite a few YouTube videos explaining it.
- You'd put the INDIRECT function in the destination sheet, so that it "pulls" data from the sources. This instead of pushing it from the source to the destination. You'd use INDIRECT to create the full reference, including workbook name and cell references needed.
As noted above, if you desire more help, it really would be easier if we could work with either your actual workbooks or a mockup of them.
mathetes Hi, thank you for the reply. I have created two workbooks.
Source workbook, which will keep on changing after every month.
Destination workbook, where all the record will be kept for whole year. Destination workbook is like a master sheet which will contain all the records of more than a year.
Source workbook : https://1drv.ms/x/s!AnGq9zKTIjcagx8qEGiX7gBnrdlQ?e=B9e1a2
Destination workbook : https://1drv.ms/x/s!AnGq9zKTIjcagx1qUFh7tRrHaQze?e=7AuUQc
I am looking for some kind of code or way which i can reuse for every new workbook that's created every month and sends the required data to destination workbook in just single click or some form of easy way.
- After reviewing your two workbooks, it's not clear why the Source workbook is needed. The destination is arranged better and more suitable for data analysis. Any time you're linking workbooks there's a dependency created and it takes some of the newer features off the table (e.g. dynamic arrays) and would take a legacy function like INDIRECT off the table (INDIRECT requires the the linked workbook to be open to calculate).
