Hi,
We have a number of contracts with suppliers to provide services at agreed rates. What i'd like to do is check that when they submit their invoices the rate they've charged is either acceptable (i.e. less than the agreed rate) or matches the agreed rate. The criteria that i'd like to check against the rate are:
- Contract Ref
- Supplier Name
- Grade of labour
I hope this makes sense? Example attached.
Thanks in advance,
Snook
what is the answer for 1 row in acceptable column & in match column should come
and how that came let us know so that i can try
Thanks - Naveed
-----------------------------
If the suggestion helps you, then Click * to Add Reputation
To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
In [Invoice Log]!G3, enter:
and copy down.Formula:
Please Login or Register to view this content.
In [Invoice Log]!I3, enter:
and copy down.Formula:Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
hi Snook. but what do you want to show up at Column G & I? typing that out will help us figure out what you need more easily. this below will help you get the rates based on the 3 criteria.
=SUMIFS(INDEX(Rate!$D$4:$H$23,,MATCH(D3,Rate!$D$3:$H$3,0)),Rate!$B$4:$B$23,B3,Rate!$C$4:$C$23,C3)
so if you need to compare with Column E & simply return TRUE/FALSE, use:
=SUMIFS(INDEX(Rate!$D$4:$H$23,,MATCH(D3,Rate!$D$3:$H$3,0)),Rate!$B$4:$B$23,B3,Rate!$C$4:$C$23,C3)<E3
if you want a Y/N, then:
=IF(SUMIFS(INDEX(Rate!$D$4:$H$23,,MATCH(D3,Rate!$D$3:$H$3,0)),Rate!$B$4:$B$23,B3,Rate!$C$4:$C$23,C3)<E3,"y","n")
it's similar for Column I
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Based on my understanding I perform the attached file, Check it and let me know
look at the returned sample
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks