+ Reply to Thread
Results 1 to 6 of 6

Formula to check pre-defined list and show relevant info

  1. #1
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    846

    Question Formula to check pre-defined list and show relevant info

    Morning happy campers,

    Workbook attached

    Blue cells - desired result from formula
    Green cells - user input to feed formulae

    As in the example, i would like the user to enter the full qty (K14) and the item (N13)
    Cell H2 would then show how many full totes
    Cell H18 would show how many part totes (always 1, otherwise its a full tote lol)
    Cell K16 would show the full tote qty (how many can fit in a tote as per data range A2:C13) (already added formula)
    Cell K18 would show the part tote qty
    Cell L20 would show total numbe of totes (already added formula)

    Would really appreciate some help on what would normally be a fairly simple task for me, but my daughter is teething so sleep is a rare commodity!!

    very kind regards, galvinpaddy
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    06-20-2012
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Formula to check pre-defined list and show relevant info

    Hello Galvin,

    Try:
    H16 =ROUNDDOWN(K14/VLOOKUP(N14,data,2,FALSE),0)
    H18 =K14-(H16*K16)
    L20 =IF(H18>0,H16+1,H16)

    See attached workbook.

    Cheers,
    Diana
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    846

    Re: Formula to check pre-defined list and show relevant info

    Hello and thanks for your response, my last question would be what formula will go into K18? would =K14-(H16*K16) work accurately?

  4. #4
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    846

    Re: Formula to check pre-defined list and show relevant info

    Sorry, ignore that, i am starting to wake up!!
    Thanks very much!

  5. #5
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    846

    Re: Formula to check pre-defined list and show relevant info

    solved rep added

  6. #6
    Registered User
    Join Date
    06-20-2012
    Location
    Melbourne Australia
    MS-Off Ver
    Excel 2010
    Posts
    75

    Re: Formula to check pre-defined list and show relevant info

    Thanks Galvin.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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