5

I hope someone can assist me in looking in the right direction. I have a sheet (Master) which has rows of data regarding different types of projects and deliverables.

One column in this sheet has a date value under column header "closingDate". The deliverable dates extend through the year. For example. we may create an entry today in the Master, for a project that is due to close in September of next year.

We need to be able to see what are the deliverables for a particular month. The years don't matter. All projects closing in Jan 2016, 2017, 2018, etc.

For reasons that I can not state we can't use filter or filter views in the Master Sheet.

An ideal solution for us would be to have sheets named Jan, Feb, March, April, etc.

Each time a record is added to the Master, depending upon the month of the closingDate a copy of the record is made in the sheet named after the month. Each time the record is edited in the master sheet the corresponding record is also updated.

I know I need a script, but being new to Sheets, I would greatly appreciate someone who could point me in the right direction.

Improve this question

1 Answer 1

Reset to default
3

You don't need a script: the Filter command (which is different from filters and filter views) does this easily.

Suppose your master sheet is named Master and the closing date is in column B. Then in Sheet "Jan" you would enter 

=filter(Master!A:Z, month(Master!B:B) = 1)

Here A:Z are the columns of Master sheet (of course there may be more, you can use A:ZZ, etc). The condition that month of the date in column B is equal to 1 means it's January.

In the sheet named "Feb" you would put

=filter(Master!A:Z, month(Master!B:B) = 2)

and so on. The edits to Master sheet will be immediately reflected in the month sheets.

Improve this answer
2
  • Thank you Norma, this worked Exactly as I had hope it would !. This is acceptable answer to the question I had posed. However after i imlemented the solution another "I wish I could do this" emerged. I wonder if it was possible to make changes IN the Filtered Results and have them Cascade into the Master. I know that was not my original request but life evolves :-P Thank you for your time.
    – Randeep Singh
    Jan 25, 2016 at 1:47
  • 1
    Please ask as a new question, since the solution would be quite different from this one.
    – user79865
    Jan 25, 2016 at 1:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.