Hi all,
Hoping you can help me. What I'm trying to achieve is when the 'meetup' is selected in D1 on tab 'overview' each column in rows 9 and 23 will autofill based on the title, pulling the info from tab 2 'expenses'. This will auto fill the totals in rows 4-7. Thanks in advance! Will post link in comments
Last edited by emalouise27; 05-25-2023 at 03:38 PM.
Not sure how to post a link to the sheet so you can see what I mean
Not sure that I understand the request as there were no manually placed results to serve as a guide.
1. Populate A10:A22 using: =IFERROR(INDEX(Expenses!C2:C1004,SMALL(IF((Expenses!A2:A1004=D$1)*(Expenses!B2:B1004=A$9),ROW(Expenses!A2:A1004)),ROW(1:1))-1),"")
2. Populate B10:B22 using: =IF(A10="","",INDEX(Table_1[Amount],MATCH(A10,Table_1[Description],0)))
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thanks this is great but how do I get it to add further results in a list?
I've attached another version with further results and an example page.
Thank youTeam Meetup Finances (1).xlsx
Hi emalouise27,
Just my opinion, but you could use a pivot table which would automatically update as new entries are added. Check out this link and see if it's something you could work with.
Thanks flyboy65 this is great, I would still like to set up the original sheet I had but will use your pivot table suggestion to create an overall view. Thank you
In columns A, D, G etc.**: =IFERROR(INDEX(Table_1[[Description]:[Description]],SMALL(IF((Table_1[[Meetup]:[Meetup]]=$D$1)*(Table_1[[Category]:[Category]]=A$9),ROW(Table_1[[Meetup]:[Meetup]])),ROW(1:1))-1),"")
In columns B, E, H etc.: =IF(A10="","",INDEX(Table_1[[Amount]:[Amount]],MATCH(A10,Table_1[[Description]:[Description]],0)))
**Denotes an array formula which is not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Note that there are some slight modifications to the formulas in row 24 and down.
Let us know if you have any questions.
Here's another Option using the QUERY and FILTER functions. Your setup sheet remains the same.
Thank you this is getting there! The only issue I'm having with this is when I choose 'All' it gives me more results in the pivot table than when I select 'Autumn meetup' however the only result currently included are the 'Autumn meetup' so there is an error here which I can't figure out. Can you help?
Entered in error.
Last edited by Flyboy65; 10-27-2023 at 09:58 AM.
I don't see a pivot table in the file, however perchance you are referring to the Overview sheet.
Using the drop down in cell B1, the grand total for either All or Autum 23 is £13,268.75
The total of the amount column on the Expenses sheet is 13,296.35
I believe that the reason is because cells B38 and B40 are blank so there is no category assigned.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks