Retrieve data in a cell from value in another cell

    Hi,


    I have an inventory list in one column and an "x" in the next column if the item needs to be purchased. I'd like to create a shopping list from these 2 columns. What formula do I use to create the list. I'm trying to retrieve the item that has an "x" in the next column and would like to create a column of all those that have the "x". Example: I know I can use: =if(isblank(E4),"",D4), however I'll have multiple blank cells that don't contain the "x". I'm pretty sure there's a formula using INDEX with SMALL(SMALLIF) enclosed with the { } brackets. forum.ozgrid.com/index.php?attachment/55607/



    Thanks!

    Re: Retrieve data in a cell from value in another cell


    forum.ozgrid.com/index.php?attachment/55608/

    Re: Retrieve data in a cell from value in another cell


    First, if you want, go into options, Advanced, display options for this worksheet, and deselect "show a zero in cells that have a zero value". That way you don't have to have "" to show blank cells, you can leave them as zeroes, which in another sheet could cause problems. Not a must for this specific sheet, but good in general imo. Then, under e5 of your shopping list, enter: =IF('Inventory (3)'!E4="x",'Inventory (3)'!D4,0) Copy this to the remaining cells. That way, it will recognize the "x" and post the information in the slot before it, which I think is what you want, correct? Hope this helps.

    Re: Retrieve data in a cell from value in another cell


    After that, if you wanted to sort the list, you could either manually sort it by selecting each column and sorting z-a (that way spaces are at the bottom), or you could even set something up in vba to do it for you with one click of a button. Can go that way if you want, just don't know how complex you want it.

    Re: Retrieve data in a cell from value in another cell


    Thank you! It'll work, however I was trying to get the list without any blanks without having to sort. The thing is, I will be updating the list hourly or daily and was trying to avoid sorting everytime I add to the list (or filter). I think theres a way using the INDEX formula with the { } brackets at each end but not sure how to use it. Thanks Rowddawg for your help, its greatly appreciated.

    Re: Retrieve data in a cell from value in another cell


    Alright. I did more research and found the following equation, which is entered into cell E5 from Shop List worksheet:
    {=IF(ISERROR(INDEX(Inventory!$C$4:$D$33,SMALL(IF(Inventory!$C$4:$C$33="x",ROW(Inventory!$C$4:$C$33)),ROW(Inventory!1:1)),2)),"",INDEX(Inventory!$C$4:$D$33,SMALL(IF(Inventory!$C$4:$C$33="x",ROW(Inventory!$C$4:$C$33)),ROW(Inventory!1:1)),2))}


    However, the 1st item is spam when it should be eggs. Also, there are items that dont have an "x" in the column next to it that appear in the Shop List (That is not needed to purchase). FYI - I entered "x" into the column to the left of the inventory list, such that the above formula would carry out.


    Please Advise on a solution.
    THANKS!!

    Re: Retrieve data in a cell from value in another cell


    Dewilk


    Here's an updated version with the INDEX formula. FYI - again, there are items that shouldnt be in the shop list and the 1st item should be eggs, rather than spam. A correction to this formula would be greatly appreciated. This corrected formula would also serve for other purposes too, not just my shopping list.


    Thanks again!
    forum.ozgrid.com/index.php?attachment/55638/

    Re: Retrieve data in a cell from value in another cell


    Dewilk,
    Thank you, Thank you!! That is much better. Your help is greatly appreciated.


    Best Regards,
    Tikigaq75

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login