+ Reply to Thread
Results 1 to 9 of 9

Formula to Validate Invoiced Rates Against a Rate Card

  1. #1
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Formula to Validate Invoiced Rates Against a Rate Card

    Hi,

    We have agreed maximum rates with suppliers for certain services and I'd like to check that the rates they have invoiced fall below the agreed maximum.

    I've attached an example of the data I'm working with. What I'm after is a formula that will cross check the details and rate charged on the 'Invoiced' tab against the three 'rate card' tabs and generate an output that flags any discrepancies. I've added a column called 'Validate' on the 'Invoiced' tab where I'd like this formula to go.

    All the data should match with the exception of the shift which is listed as a description on the rate card but is a concatenation of the job role and an abbreviation of the shift (D = Days, N = Nights/ Saturday and O = Sunday/ Bank Hols).

    If you have any queries just give me a shout.

    Thanks in advance,

    Snook
    Attached Files Attached Files
    Last edited by The_Snook; 03-30-2014 at 01:55 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Formula to Validate Invoiced Rates Against a Rate Card

    I dont understand quite... Is this it?
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But I got different results.

  3. #3
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Formula to Validate Invoiced Rates Against a Rate Card

    as per of your attached sheet you dont have any duplicates. I think you could use below (In R1, R2, R3 of invoiced sheet write down the name of all three sheets )
    then in O2 of invoiced sheet copy paste below
    =sumproduct(SUMIFS(INDIRECT("'"&$R$1:$R$3&"'!"&"H2:H30"),INDIRECT("'"&$R$1:$R$3&"'!"&"A2:A30"),C2,INDIRECT("'"&$R$1:$R$3&"'!"&"C2:C30"),G2))


    I too got different Results.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  4. #4
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Re: Formula to Validate Invoiced Rates Against a Rate Card

    Hi,

    Apologies I'm not sure I've explained myself correctly, I'm just after a validation output like TRUE/FALSE that lets me know if the rate the supplier has invoiced us falls below the maximum rate they are permitted to bill?

    You don't need to worry about duplicates I'm just checking the validity of the rate charged on each transaction.

    Hope this helps.

    Thanks,

    Snook
    Last edited by The_Snook; 03-23-2014 at 03:33 PM.

  5. #5
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Formula to Validate Invoiced Rates Against a Rate Card

    Like this ?
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Re: Formula to Validate Invoiced Rates Against a Rate Card

    Thanks hemesh, almost there! I need to factor in the 'shift' as one of the criteria as well because, although my example data doesn't show it, each job role has three different shift options. This is the tricky part because unlike the other two criteria the data isn't an exact match.

  7. #7
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Formula to Validate Invoiced Rates Against a Rate Card

    Hello Snook ! Try below in P2
    =SUMPRODUCT(SUMIFS(INDIRECT("'"&$R$1:$R$3&"'!"&"H2:H30"),INDIRECT("'"&$R$1:$R$3&"'!"&"A2:A30"),C2,INDIRECT("'"&$R$1:$R$3&"'!"&"C2:C30"),G2,INDIRECT("'"&$R$1:$R$3&"'!"&"E2:E30"),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(K2,1),"D","days"),"N","Night/Saturday"),"O","Sunday/Bank Hols")))

    In o2 keep formula as M2<p2 for true and false

    Hope this helps

  8. #8
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Re: Formula to Validate Invoiced Rates Against a Rate Card

    Boom, I think you've nailed it sir!

    Massive thanks,

    Snook

  9. #9
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Formula to Validate Invoiced Rates Against a Rate Card

    You are welcome and Thanks for the Feedback !

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 05-28-2013, 05:53 AM
  2. How can I calculate various rates of pay on a time card
    By jpmanfredo in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-30-2013, 03:03 PM
  3. Validate two fields when one field you need a formula to validate
    By cmwilbur in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-29-2010, 11:32 AM
  4. [SOLVED]Invoiced days per month
    By txbullets in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-03-2009, 04:23 PM
  5. Calculating Month Average Exch Rate from Exchange Rates Calendar!!!
    By StanUkr in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-14-2005, 06:12 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1