Yes/No Result Based On Text Length & Other Criteria

  • I need help on a lookup and not sure which route to use. I want to aggregate the availability of products to the related product heading. If all products are unavailable I want the heading to show no. If one or more of the product types are available I want the heading to show yes. If it is possible to manage this I would then be able to automatically delete products and their related heading when unavailable.


    I've tried to solve it with a lookup formula


    =LOOKUP("Yes";A5:A8;A5:A8)


    (see cell A4 in sample file) but this means that I have to type in the related cells for every heading which is unfeasible due to the amount of changes. Preferably the formula for every heading should run until a blank row (end of particular products) which would mean that I could enter new products without changing formula).


    Thanks
    Frank

  • Re: Run Lookup Formula Until Blank Row


    Not quite sure what you want here.


    When you say "headings" do you mean A1:C1 or do you mean A4:C4, A10:C10 etc?


    I suspect the latter from your description, so you should seriously consider changing your data layout!!! However...


    How do you want Excel to know that a heading has changed? To me, the values A, Aa, Aa1, Ab, Ab1 are all different so what's the trigger for a new header?


    edit: have made some assumptions about what you want. I've added a helper column which identifies the change in header, which I assume to be when it sees a single-character description. You can hide this of course.


    I've added a formula in green which counts if it it sees at least 1 "Yes" in the section ABOVE and tells you Yes or No accordingly. I found putting this formula at the top of each section much more difficult to satisfy your dynamically-sized data, so I've kept it at the bottom of each section.


    Any good for you?

  • Re: Run Lookup Formula Until Blank Row


    I appreciate the help and sorry about the confusion.


    When I say 'heading' I mean, as you assumed, A4:C4, A5:C5, A9:C9 and so fort.


    Your idea is good but doesn't fully do what I need, due to my fuzzy description. What happens is that new products (e.g. insertion of product Aa2 in row 7) and, at times, new 'headings' (A4:A4) or 'sub headings' (A5:A5) are added to the sheet if radically new products are added. I then want the counter or lookup formula to recognize any additions.


    E.g. if Aa2 is added but is out of stock as well as Aa1 (No and No in column A cell) then 'sub heading' A5:C:5 should show No but A4:C4 should show yes since product Ab1 still is in stock (Yes in column A cell).

  • Re: Yes/No Result Based On Text Length & Other Criteria


    I fear your description has not yet left the realms of fuzziness!


    FWIW, your data appears to be laid out in an unstructured and dynamic way, which means we have to work very hard to pick out the values you need.


    I'm afraid I can't follow what you want.


    The easiest layout to work with is a table with 1 set of column headings and rows of data below it.


    Wish I could help more.

Participate now!

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