Forum Discussion
Dividing cells into two lists and keeping lists constant
Hello.
I’m using Excel 2016 for PC, and I’m trying to create a guide to putting up posters at the cinema I’m working at.
I have a long list of coming premieres and have filtered it to only show films that we have the poster for.
The cinema is divided into an upstairs and a downstairs, with 9 spots for posters upstairs and 6 spots downstairs. We place our posters in order of release date, with it divided between the floors.
Now, dividing the list into two separate lists with the release date in mind shouldn’t be much of a problem. However, I want a film to be removed from the list if the release date has passed, move the others up one spot, and the next unused poster from the main list to fill the empty spot at the bottom.
Again, not too difficult, but I want the two lists to be separate, so once a movie has been placed in one list, it keeps moving up in that same list. It can’t jump to the other list.
Any tips on the best way to do this?
Thanks.
I’m using Excel 2016 for PC, and I’m trying to create a guide to putting up posters at the cinema I’m working at.
I have a long list of coming premieres and have filtered it to only show films that we have the poster for.
The cinema is divided into an upstairs and a downstairs, with 9 spots for posters upstairs and 6 spots downstairs. We place our posters in order of release date, with it divided between the floors.
Now, dividing the list into two separate lists with the release date in mind shouldn’t be much of a problem. However, I want a film to be removed from the list if the release date has passed, move the others up one spot, and the next unused poster from the main list to fill the empty spot at the bottom.
Again, not too difficult, but I want the two lists to be separate, so once a movie has been placed in one list, it keeps moving up in that same list. It can’t jump to the other list.
Any tips on the best way to do this?
Thanks.
5 Replies
your go to solution would be power query with release date as the parameter. Please share your workbook so we can find out where it needs fine tuning and create queries for each list.
cheers
- Hi! Thanks for the response.
I have already used power query to filter out any movie we don’t have a poster for and sorted them in the correct order.
The only thing left to do is to divide them into the separate lists. I don’t know how to do it since if I reference a cell in the list, once a release date is passed, all the cells will change.
I’m afraid I can’t attach the file since it’s for internal use only and therefore confidential.
But basically, I reference a large .xls file that shows every movie for every cinema. I filter out any passed premiere, and any premiere more than six months in the future. It shows that in a sheet where I’ve added another column where us who work here can add a check to any poster we have, and then using another power query I filter out any row without that check.
Now in a second sheet I have a list of 25 films in order of release date, automatically set up to remove any movie that passes their release date.
Hopefully that’s enough information to guide me in the correct direction.
If not, thanks for your time.I dont need the movie titles. you can anonymize the structure of the spreadsheet to show it you can use movie1 movie2 movie3..etc
for the locations u can use location1 location2 etc
In excel the structure of geography of the data dictates what function one can use.
without that intel your guess is as good as mine
you know the structure
and i'm familiar with the excel functions.
so what will it be, your choice.
cheers
