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
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.
- 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.
- Hi Mathetes
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.
Also the Source workbook which i have provided shows only the UI part, but in actual the values are being automatically populated on the basis of formulas applied on source workbook.