Moving from google sheets to Excel, need help with a formula that didn't go well with Excel.

[Procol]

Hi everyone!
I created a spreadsheet on Google Sheets and decided to move to Excel. Long story short, my spreadsheet is for sales listings, all the items for sale are in the Listings sheet, whenever an item sells, I enter the date in the Sold column and that item will automatically populate a separate sheet (monthly sheets at the bottom) based on the month that it's sold.
The formula that I was using with Google sheets is as follows: =filter(Listings!A2:M,month(Listings!B2:B)=1, Year(Listings!B2:B)=2020) I have this formula on the A2 cell of every monthly sheet (January, February etc...)
When I downloaded the file to Excel, that formula changed to this: =IFERROR(__xludf.DUMMYFUNCTION("filter(Listings!A2:M999,month(Listings!B2:B999)=1, Year(Listings!B2:B999)=2020)"),43842)
If I manually replace that formula with the first one, I get an error message "that function is not valid".
Would somebody be able to help me fix that formula or maybe change it to something else that can work?
I am using Excel 2007 version.
Thank you in advance and thanks for reading.

 

[RoryA]

Excel 2007 doesn’t have FILTER or anything comparable, sadly. You’d need code to achieve that, though I’m not really sure what benefit you get from duplicating your data like that?

 

[Procol]

Excel 2007 doesn’t have FILTER or anything comparable, sadly. You’d need code to achieve that, though I’m not really sure what benefit you get from duplicating your data like that?

Thank you for your reply Rory.
That explains the issue I'm getting.
The picture is a simplified version of what I have but the reason I'm doing this is I want to have a listing sheet with all my listed items for sale and I would like to move every item that sells to it's own sheet based on when it's sold. Currently it just copies it so I have duplicates., but ideally I would like to have it moved completely.
I started from scratch building it and with the help of other experts I was able to make it into a functional spreadsheet.
If you think you can point me to a specific formula or anything else that may work I would be really grateful.
I appreciate your reply.

 

[RoryA]

If you can give a better idea of the actual layout, I can probably come up with some code for it.

 

[Procol]

If you can give a better idea of the actual layout, I can probably come up with some code for it.

Hey Rory,
I'm uploading two pictures, the first one is the main Listings with an example of some items listed and some of them sold.
The second one is of the June sheet (on the bottom of the first picture you can see the monthly tabs).
Columns K and L have a formula: =IF(ISBLANK(B3),"",SUM(E3+I3+J3)) and : =IF(ISBLANK(B3),"",SUM(E3+I3+J3)) so when I enter a date on the Sold column, it will automatically populate the cells for cost and profit.
What I'm trying to do is have the item row that sells to go automatically to its own sheet, for example row #3 would go to the June sheet, and it's working fine but honestly I would like it more if it gets moved there instead of copied.
I don't mind having one spreadsheet that has only listings that didn't sell and all the other months in a completely separate spreadsheet or file.
I hope that helps explaining what I'm trying to do.
I really appreciate you working with me.

 

[RoryA]

Personally, I would not recommend breaking the data out into separate sheets as it will just make analysing your sales harder. I'd just use filters on the first sheet to hide anything you aren't interested in at any given time. You can set up any common filters as custom views, or use a bit of code to apply specific filters - eg Sold or Unsold, Sold in a particular month, and so on.

If you do still want the separate sheets, let me know, and also let me know what you want to trigger the moving of the data - eg do you want a button to move all the currently sold rows in one go, or would you prefer having them go as soon as you enter a sold date? (I'd suggest the former as it gives you a chance to verify the data and correct any mistakes)

 

[Procol]

Personally, I would not recommend breaking the data out into separate sheets as it will just make analysing your sales harder. I'd just use filters on the first sheet to hide anything you aren't interested in at any given time. You can set up any common filters as custom views, or use a bit of code to apply specific filters - eg Sold or Unsold, Sold in a particular month, and so on.

If you do still want the separate sheets, let me know, and also let me know what you want to trigger the moving of the data - eg do you want a button to move all the currently sold rows in one go, or would you prefer having them go as soon as you enter a sold date? (I'd suggest the former as it gives you a chance to verify the data and correct any mistakes)

No problem having them all in the same sheet instead of separate ones.
I don't mind having a button that sends all the sold items to their specific months, actually that sounds like a great idea.
If you think that it's a good idea to share my Google Sheets file link here just let me know.
Thank you!

 

[RoryA]

No problem having them all in the same sheet instead of separate ones.
I don't mind having a button that sends all the sold items to their specific months, actually that sounds like a great idea.
If you think that it's a good idea to share my Google Sheets file link here just let me know.
Thank you!

Those first two sentences are mutually exclusive. You either keep it all on one sheet, or you send the sold data off somewhere else. I would recommend the former, but can help with the latter.

If you can share your file, it would make life easier.

 

[Procol]

Those first two sentences are mutually exclusive. You either keep it all on one sheet, or you send the sold data off somewhere else. I would recommend the former, but can help with the latter.

If you can share your file, it would make life easier.

 

[Procol]

I think I will go with the first option and eventually what I will do is make a copy of the whole thing for the year 2020 then empty the data for 2021 so that months won't overlap from year to year, but that can be done manually with no problem.
that's the link for my Google sheets and it's all working fine but if that can work on my Excel 2007 that will be great.

Copy of Copy of Amazon Listing

Listings Listed,Sold,Item,SKU,Cost,Selling Price,Shipping credit,Platform,Shipping,Selling Fee,Total Cost,Profit,Source,Location 6/20/2020,A,$1.00,$10.00,$2.00,$2.00,$2.00,, 6/16/2020,6/16/2020,B,$1.00,$10.00,$2.00,$2.00,$2.00,$5.00,$5.00 6/16/2020,C,$1.00,$10.00,$2.00,$2.00,$2.00,, 6/16/2020,D,...

docs.google.com

Thank you!