Hello
I have the attached spreadsheet, what I need is for the 'print sales' worksheet to look in the 'print orders' and return sales per month per artist.
So column B7 needs to return the name of the artist that has sold, ie if 'Print Orders' column AA (date of sale) is populated I need it to look for the artist name (col C) and return the amount sold (column Z) for that month
Can anyone pls help me with this?! I have attahed my spreadsheet
thx
Does the attached help.
Click the 'List Artists' button on Print Sales if you add new artists to the orders tab.
Note. The dynamic range names I've added use the COUNTA() function to count the number of non blank cells in column C. However since there are sometimes blank cells in this column, and no other column seemed to contain a non blank cell in every row, I've multiplied the COUNTA() function by two on the basis that at least half the rows will have an entry.
Richard Buttrey
If this was useful then please rate it appropriately.
Click the small star iconat the bottom left of my post.
Hi Richard, this is fantastic and it works - my only problem is I don't see how!
How is this formula linking to the 'print orders' worksheet? As the data in the 'print orders' worksheet is actually in another xls file and I need to link the print sales to this sheet so it works as you have created.
=SUMIFS(Sales,ARTISTS,$B11,Date,">="&D$10,Date,"<="&EOMONTH(D$10,0))
Also, how did you get the artists names or do these need to be typed in manually as no formula. I see there is a macro 'list artists' but this doesn't work for me
thanks for your help!
VBA macros do not work on office 2008 for a MAC which will be why I cannot get this to work! Does aybody else have a solution for a Mac? Would appreciate some help with this. Thanks
Ahh,
That's why we encourage users to update their details with Location and Excel versions.
Excel versions for the very reason you've encountered. Had I know you were on a Mac with only 2008 then I wouldn't have used macros.
And location so that we can imply your system locale settings for date arithmetic and other peculiarities.
When you get a moment perhaps you would update them.
You can either
1 Change to Excel for Mac 2004 which did support macros, but in which case you'll need to add a helper column since there's only a SUMIF() function and not a SUMIFS()
2. Change to Excel for Mac 2011
3. Do the whole process manually
Here's the code
You can perhaps see that you need toDim llastrow As Long Sheet2.Range("ArtistOut").CurrentRegion.Offset(1, 0).ClearContents Sheet1.Range("artists").AdvancedFilter Action:=xlFilterCopy, copytorange:=Sheet2.Range("ArtistOut"), unique:=True llastrow = Sheet2.Range("B" & Rows.Count).End(xlUp).Row Range("Form1").Copy Destination:=Sheet2.Range("C11:C" & llastrow)
1. Use Data filter advanced to extract a unique list of artists to B10 on Print Sales (Sheet2)
2. Copy the formula held in C6:O6 of Print Sales down as far as necessary starting at C11
Richard Buttrey
If this was useful then please rate it appropriately.
Click the small star icon
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks