Forum Discussion
I want to send data across two workbooks depending on the condition explained below.
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).
- Hi
Source workbook is required as it has lot of other information as well related to other stages.Patrick2788 has asked you a good question. I'm in the midst of looking at your two sample sheets and wonder the same thing.
Your answer, "Source workbook is required as it has lot of other information as well related to other stages," doesn't really fully answer his question. There's no reason why "a lot of other information" couldn't all reside in the same workbook as the summaries you're creating on the "Destination" sheet.
My further question would be why think you need--because you don't--a separate sheet/tab for every day. A well designed database could hold all those separate days' entries in a single (and easily summarizable) data table.
It's not uncommon--it's actually a frequent mistake--for people to create Excel using the mental paradigm of the paper-based tracking systems that preceded the use of the computer. It was easier for us as human beings to keep each day separate. But carrying that practice over to Excel actually makes it harder* for Excel to produce summaries; Excel excels at taking extensive databases and summarizing the data by means of such tools as the Pivot Table.
So although I could show you how INDIRECT** could serve to help you with your initial question, let me come back with a question too: are you in a position to consider a redesign of your overall procedure?
________________
* your desired goal of extracting the data from individual daily sheets is a great illustration of the added complexity
** INDIRECT also is what's called a "volatile" function, meaning in essence that it uses a lot of computer resources, or is inefficient. You'd need a lot of INDIRECT formulae, and that could significantly (and, as noted, unnecessarily) slow the process down.
