Forum Discussion
Copying data from a master sheet to other sheets based on a piece of data
OK, now, unless I'm missing something, I want to propose a simpler way to see only the Jan (or Feb, or Mar, etc) records. That is that you take advantage of Excel's TABLE capability. In the attached revised version of your sample file, I've converted the array of data you had into a table. [That was a simple matter of placing my cursor in the array at any point and clicking on the Insert tool bar on "Table" and agreeing with Excel's having already identified the appropriate range.
Once it's formatted as a Table, you see in each heading, at the right end of that cell, a little caret (downward pointed diamond) and if you click on it, you are given the ability to Filter your data.
Just click on that small diamond in either of your Month column headings to see your choices of what to display. Try it out with more rows in the table.
Why am I suggesting this rather than coming up with a method to copy the entire record to January? Because you indicated you're planning to do all your editing in the "master" schedule anyway. You can still do that for any and all records, and display only a given month by using the Filter capability.
This also avoids inadvertently making a change in the monthly page and by that getting the two out of sync--what's known as a "data integrity" problem. A serious issue when one has essentially redundant data...and that's what you were wanting to create.
Now, I may be missing something--maybe there's another reason for moving it over--but here's where I'd start were I sitting down with you in person.
Feel free to challenge what I've proposed.
P.S. Having month as "January" and as "1" is itself redundant. If I were you, I'd use the number (easier entry), and have a Function insert the name. There are various ways to do that; I've gone ahead and put one option in that first column, which uses your number to generate the month name, to show you.
You could also--if you really want the word to display--use DataValidation to create a drop down menu that prompts for and only accepts the month names.
Is it possible to upload a sample of what you have, without disclosing any confidential data?
mathetesThanks.
I'm hoping to bring the whole record from the schedule worksheet to appropriate month worksheet. And for the monthly worksheets to update the moment that I add a record to or edit a record in the schedule worksheet.
Thanks
OK, now, unless I'm missing something, I want to propose a simpler way to see only the Jan (or Feb, or Mar, etc) records. That is that you take advantage of Excel's TABLE capability. In the attached revised version of your sample file, I've converted the array of data you had into a table. [That was a simple matter of placing my cursor in the array at any point and clicking on the Insert tool bar on "Table" and agreeing with Excel's having already identified the appropriate range.
Once it's formatted as a Table, you see in each heading, at the right end of that cell, a little caret (downward pointed diamond) and if you click on it, you are given the ability to Filter your data.
Just click on that small diamond in either of your Month column headings to see your choices of what to display. Try it out with more rows in the table.
Why am I suggesting this rather than coming up with a method to copy the entire record to January? Because you indicated you're planning to do all your editing in the "master" schedule anyway. You can still do that for any and all records, and display only a given month by using the Filter capability.
This also avoids inadvertently making a change in the monthly page and by that getting the two out of sync--what's known as a "data integrity" problem. A serious issue when one has essentially redundant data...and that's what you were wanting to create.
Now, I may be missing something--maybe there's another reason for moving it over--but here's where I'd start were I sitting down with you in person.
Feel free to challenge what I've proposed.
P.S. Having month as "January" and as "1" is itself redundant. If I were you, I'd use the number (easier entry), and have a Function insert the name. There are various ways to do that; I've gone ahead and put one option in that first column, which uses your number to generate the month name, to show you.
You could also--if you really want the word to display--use DataValidation to create a drop down menu that prompts for and only accepts the month names.
- Thanks - I'll discuss with my colleagues and may come back to you if they continue to want this.
- Thanks.
