Hi,
I have seperate sheets for each month, April, May, June, July etc. In there, there is a column "Invoice Paid". If this says no, is it possible to get a summary of these on the first sheet, e.g. unpaid in April, unpaid in May all in one list?
See attached :D
Why do you need separate tabs for each month? Normalise your data and then do all your filtering and subtotalling on one table. KISS!!!
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thanks for the reply Ali, but I don't know what you meanCan you give me an example?
Sorry - I forgot to attach the workbook.
Put all your invoice data on one sheet:
Excel 2016 (Windows) 32 bit
Date School Hours Total Amount Invoice Number Invoice Generated Invoice Paid School 1 Yes No School 2 Yes Yes School 1 Yes Yes School 2 Yes No
Sheet: Invoice Data
Use filters and subtotals to manage that table.
In terms of the summary table, there is no way in the sample sheet to link Andy Walker to the invoices listed - what is the link?
Ok, great. Thanks. I'll have a play with that.
The teacher name will appear on a "mail merge" type thing which I haven't created yet so that's where the teacher name comes in. Am I able to summarise unpaid invoices on the Dashboard page?
Yes, but have a look at the attached first, in which I have added slicers to the invoice tab. Dashboard pages often are not necessary with such simple datasets. I have filtered the table using the slicers.
Last edited by AliGW; 07-25-2018 at 02:37 AM.
Thank you! I had got the advanced filter to copy the data but it wasn't looking at my criteria
You don't really need advanced filters - you are overcomplicating things for yourself (see my last post).
It's for a friend so I just want the Dashboard screen to show anything that hasn't been paid if that makes sense. That's why I was trying to use a filter because it will always just be the unpaid invoices, nothing else but I can't get it to work - although I've specified teh criteria as ="=No" - does that make sense?
i've attached how far I've got...
I think though a slicer might be the way forward and I'll stick it on the dashboard.
You can try.Originally Posted by CraigMcKee
OK - forget about advanced filters.
Excel 2016 (Windows) 32 bit
School Hours Total Amount Invoice Number Date School 1 School 2
Sheet: Dashboard
Excel 2016 (Windows) 32 bit
=IFNA(INDEX(Table1[School],MATCH(Dashboard!E14,Table1[Invoice Number],0)),"")
Sheet: Dashboard
It's always a friend who gets the blame.
Last edited by AliGW; 07-25-2018 at 02:54 AM.
Did you look at the last version of the file I attached?
Also try with this.
Look sheet "DashBoard_AVK"
In above in "A2"Formula:
=IFERROR(INDEX('Invoice Data'!A:A,SMALL(IF($K$1='Invoice Data'!$G$2:$G$3000,ROW('Invoice Data'!$G$2:$G$3000)),ROWS(A$1:A1))),"")
Ensure SHIFT+CTRL+ENTER
Copy paste across. File attach.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
It would be appreciated if you'd do the same for all of your threads where appropriate. Thanks.
Ali - as usual you've nailed it! How did you set up that filter? I couldn't get it to work.
The formulae are at the foot of post #13 along with a sample file. In what way could you not "get it to work"?
There is one formula to extract just the unpaid invoices, and the rest are simple INDEX MATCH statements. No array formulae required.
I went to data and advanced filter and put ="=No" for the criteria for the Invoice Paid column but it just showed all the data - thank you. This is perfect. :D
Get rid of the advanced filter - not needed. My solution is formula-driven.
Will do - could you tell me why your formula is different in the Invoice number column?
I explained that in post #19: that's the filtering formula. The others are just lookups.
Yes, it should be. Try this as the filter formula:is it possible to combine criteria with your formula? E.g. Invoice Paid = "No" and School Name = x?
=IFERROR(INDEX(Table1[Invoice Number],AGGREGATE(15,6,ROW($1:$500)/((Table1[Invoice Paid]="No")*(Table1[School]="School 1")),ROW(1:1))),"")
Last edited by AliGW; 07-25-2018 at 05:52 AM.
@AliGW is it possible to combine criteria with your formula? E.g. Invoice Paid = "No" and School Name = x?
Perfect, perfect, perfect - I think an Excel for Dummies book is next on my shopping list!
This isn't Excel for Dummies - it's more advanced, so give yourself a pat on the back!!!
Ha thanks! It's difficult to learn because you don't know what to Google and I feel bad asking for help all the time when I just want to learn how it works so I can do it myself.
You are a teacher (or at least you work with teachers), so you must know that this is part and parcel of the process. Those of us who help here do so because we want to teach others what we ourselves have learnt. As long as you gradually accumulate the knowledge, then you are on the right track. No need to worry, therefore, about asking.
Thank you so much, really appreciate it! Nearly done once I get my invoice number working :D
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
CraigMcKee 
Register To Reply
Bookmarks