Please see attached file.
Weekly Bank Bal E39 = Monthly Invoices D7
because
Weekly Bank Bal A39 = Monthly Invoices A5
AND
Weekly Bank Bal E1 = Monthly Invoices D5
I'm looking for a formula for Weekly Bank Bal E39, that I can use for the entire row hitting matches as both criteria hit. I think its probably an easy formula, but I just can't figure it out.
Last edited by narrowgate88; 04-03-2025 at 06:17 PM. Reason: Missing attachment
Nothing attached. Double check the file size. If it's over 1MB you'll have to zip it and attach the zip file.
narrowgate88
Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.
Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.
Dave
Attachment added
I think, this:
PHP Code:=INDEX(FILTER('Monthly Invoices'!D4:O7,('Monthly Invoices'!A4:A7=A39)*('Monthly Invoices'!B4:B7="Actual")),MATCH(E1,'Monthly Invoices'!D2:O2,1))
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I'm a little confused. You say,but it also equals Monthly Invoices A4, A6 and A7. Also, you say,Weekly Bank Bal A39 = Monthly Invoices A5but it also equals D4.Weekly Bank Bal E1 = Monthly Invoices D5
Does it have to equal one of the dates in rows 4 and 5, or can it just match the same month as what's in row 2?
And is what you want to show in row 39 (of Weekly Bank Bal) the matching value in row 7 of Monthly Invoices because it says "Actual" in Col B?
Thank you for the formula, but it throws back a "function not valid" error.
It needs to match the date in Row 5 since those are the dates on the Weekly Bank Bal tab. It can't just match the month in row 2 because I need to know the specific week that it will be paid. Correct on the last question. Basically, if the week due matches for the vendor, then put the amount due. I would have just used the due date, but I didn't know how to get the system to know which week on the weekly bank bal that the due date hit. So week due is my helper row of sorts.
Please provide an updated workbook showing the problem.
There should be no issue with functions if you are using 365 as your profile says.
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.
Here's the file with the formula in it.
Ok, the Citrix box I'm working on is apparently only Excel 2010. So I'll throw this onto another box that's got 365 and check it since Filter wasn't available in 2010.
It was new in Excel 2021.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks