Results 1 to 6 of 6

Thread: Headaches from multiple sheets

  1. #1

    Question Headaches from multiple sheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm not sure if this is even possible but I have been trying for the last 2 days to make something work.

    I have a workbook with 4 sheets all setup the same way and sheet number 4 is a compare sheet. so this is who it works in a nut shell.
    Sheet 1 - Walmart
    Sheet 2 - Target
    Sheet 3 - Kmart
    Sheet 4 - Compare

    Cells
    A1 - has the name of the store (Walmart)

    A3 - Name of Product E3 - Cost K3 - Winning Sheet (only on compare page)

    I have list a number of items from each store, all the same item, I have there corresponding prices list not on Sheet 4 the goal is to show me the store with the cheapest item and based on that show me what Sheet it came from by displaying what in A1 (name of store) in my K3 column
    So far I am getting the lowest price by =MIN(Sheet1:Sheet3!E4) but can't connect that with the sheet it came from???

    Any ideas?

  2. #2
    I'll make a little edit.

    My compare sheet need to show the lowest price and on what sheet that number came from.
    The lowest price comes from =MIN(Sheet1:Sheet3!E4) But not sure how to display were it got that price from.

    I know clear as mud.

  3. #3
    Excel 2010 with free Power Query Add-In.
    Compatible with Office 2013/2016 Pro Plus.
    No formulas, no VBA macro.
    There are oodles of other, simpler ways to do this.
    This is just the latest, most elegant way.
    http://www.mediafire.com/download/y6ul0kfczy37rbb/11_25_15a.xlsx


  4. #4
    Hi Huggyd,

    I would suggest a clear list similar to below, easily joining the product price to the supplier using vlookup from our individual Sheets.


    A1 Walmart Target Kmart Cheapest
    Product1 $1 $3 $2 Walmart
    Product2 $2 $1 $3 Target
    Product3 $3 $2 $1 Kmart
    Product4 $1 $2 $3 Walmart



    In he 'Cheapest an Product1 cell put in the function =IF(MIN(B77,1=B7),$B$6,IF(MIN(B77,1=C7),$C$6,IF(MIN(B77,1=D7),$D$6)))

    Use Vlookup again to suit in Sheet4 from this list as required to get what you are after.

  5. #5
    Hi Huggd,

    Maybe create a separate list from your sheets data like below using vlookup to populate price per product.


    A1 Walmart Target Kmart Cheapest
    Product1 $1 $3 $2 Walmart
    Product2 $2 $1 $3 Target
    Product3 $3 $2 $1 Kmart
    Product4 $1 $2 $3 Walmart

    In Cheapest/Product1 cell put n he below function
    =IF(MIN(B22,1=B2),$B$1,IF(MIN(B22,1=C2),$C$1,IF(MIN(B22,1=D2),$D$1)))

    and repeat down, Vlookup then in Sheet4 Cheapest supplier of product you want.

  6. #6
    =if(min(b2:d2,1=b2),$b$1,if(min(b2:d2,1=c2),$c$1,if(min(b2:d2,1=d2),$d$1)))


Posting Permissions

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