Posts by Michael Avidan

  • Data Validation - Dynamic Ranges

    Re: Data Validation - Dynamic Ranges


    Quote from danred99;562358


    The data validation formula is: =OFFSET(INDEX(INDIRECT(C1),2),0,0,COUNTA(INDIRECT(C1)),1)
    See my attached example


    Hi,


    Although this thread can be considered as "Archeology" I would warmly suggest to use the following formula in the Data Validation cell D1:


    [SIZE="3"]=OFFSET(INDEX(INDIRECT(C1),1),0,0,COUNTA(INDIRECT(C1)),1) [/SIZE]


    instead of what you have presented.


    I'm sure you will, easily, find the difference by carefully checking the values in the drop-down list D1 when rng1 is selected in cell C1.



    Now, let us take it one step farther.


    The main issue is, mainly, to keep dynamic ranges to allow the user to add items to rng1 and rng2.


    If you'll declare both of them as "LIST" they will, automatically, became dynamic and then the DV can simply refer to:


    =INDIRECT(D1)


    The user can add as many items he wants to both "Lists" to immediatelly be reflected in the drop-down list of DV cell D1.


    The picture will help you out.


    http://imageshack.us/photo/my-images/21/nonamesv.png


    Michael Avidan
    "Microsoft®" MVP

  • Sum As Per Multi-lookup

    Re: Sum As Per Multi-lookup


    Wooooooooo...!


    You cannot imagine how much, "Excel knowledge" I gained from all that.


    I even figured out [all by myself] that I don't need to use an Horizontal range [such as G1:I1] because I can convert the Vertical range into an Horizontal one with Transpose, YOU just used a few minutes ago in a previous formula.


    Even if some of the usages are not the perfect solutions - they still have the power of teaching and experiencing "Excel".


    Let me thank you, again, for all the above,


    Michael

  • Sum As Per Multi-lookup

    Re: Sum As Per Multi-lookup


    Thank you so much, Colin,


    Both formulas work perfect.


    I payed with your first formula and came up with something like that:
    =SUMPRODUCT(($D$16:$D$34=CHOOSE({1,2,3},205,210,510,124,128,150,505,600,620))*$C$16:$C$34)


    I numbered the cells C2:C10 as 1-10, and tried (with no success) to use C2:C4 instead of {1,2,3}.


    Something like that:
    =SUMPRODUCT(($D$16:$D$34=CHOOSE({C2:C4},205,210,510,124,128,150,505,600,620))*$C$16:$C$34)


    Do you think that idea can be achieved in some way !?


    Thanks, again, for your solutions so far,
    Michael

  • Sum As Per Multi-lookup

    Re: Sum As Per Multi-lookup


    Thanks, Wigi,


    As you can see I solved it with the Helper-Column.


    However, if you get bored during the coming weekend - I will be more than grateful to see a "MEGA Array Formula" as long as it will handle dynamic changes in categories & materials :flower:


    Michael

  • Sum As Per Multi-lookup

    Hi,


    In the attached file I managed to sum up the Quantities per Material & Category.


    The Original DATA is presented in columns A:E.


    The requested results/Formulas are at Cells B2:B4.


    My Question is very simple:


    Is there a way to sum up the Quantities WITHOUT the Help-Column G !?
    If so - how ?


    Thanks, Michael


    PS: to avoid discussions about the Data layout - let me point out, right now, that this is exactly what the students got from a local College.
    I do apologize if the Subject, of my post, is not correct.
    [English is not my mothers tongue]
    Thanks.

  • Save Images/Pictures In Workbook To PC Hard Drive

    Re: Save Images/Pictures In Workbook To PC Hard Drive


    I can only assume that Andy Pope visited the same school I did, - as he understood my question at the "first glance"...


    Although you know that English is not my mother tongue - I do think that the words "nice pictures" [in my question] are plural, - NOT single.

  • Save Images/Pictures In Workbook To PC Hard Drive

    Re: Saving Images/objects Disk


    Hi,


    Despite the fact that I'm such a fool for not observing the creation of the Folder that was created upon saving the WB as a WebPage, [which I did TWICE before posting my question] I'm glad I asked because that way I got your Add-in.


    I saved a lot of PowerPoint presentations, that way, in order to extract the pictures - however I had a strange feeling that it is somehow different with "Excel".


    Thanks a lot and please excuse me,


    Michael

  • Return Nth Value From Single Column Range

    Re: Return Nth Value From Single Column Range


    I'm not bent to anything.


    I'm studying the various functions and it seem ridiculous [to me] that I can't use a range and/or a named range instead of typing the various elements, one by one.


    =Choose(3,A1,A2,A3,A4,A5,A6,A7,A8,A9,A10) works fine so what is the logic behind the fact that it isn't working with: =Choose(3,A1:A10) ?


    Thanks.

  • Return Nth Value From Single Column Range

    Hi,


    Assume A1:A10 filled with the characters A to J.


    I'm looking for a way to return the letter "C" when 3 serves as the index_num BUT I do not want to present all 10 characters in the formula.


    I would like to use the range A1:A10 [SIZE="2"][COLOR="Blue"]=Choose(3,A1:A10)[/COLOR][/SIZE] or, if applicable, to name the range as DATA and then use: [SIZE="2"][COLOR="Blue"]=Choose(3,DATA)[/COLOR][/SIZE]


    I tried all I know, including INDIRECT - but found no solution [is there any ?]


    As I'm familiar with all workarounds - I will appreciate replies that use CHOOSE.


    Thanks in advance, Michael

  • Create Chart/Graph From Text Strings

    Re: Create Chart/Graph From Text Strings


    As I mentioned before [and more than once] ENGLISH is not my mothers tongue and when it comes to "Excel" expressions it gets only worse because we use, here, the Hebrew version of "Office".


    I need no body to do "THE WORK" for me but, as I stated earlier, if Dominic could post almost the third part of "War and Peace" - I would expect... etc, etc, etc...


    I can only assume that to put your suggestion into a workbook [only from the sound of it] would take 2-5 minutes, if not less.


    No hard feelings - OK !?

  • Create Chart/Graph From Text Strings

    Re: Create Chart/Graph From Text Strings


    You may call it that way [as it is your sanity - not mine].


    After Domenics and Wigis replies - unless someone will "pick up the glove" and show how your (!) suggestion can be put into reality - you will excuse me but I will not refer to it as a solution.


    If Domenic could spare more than 2 minutes, of his precious time, to present all he did in his first post - I would expect the same from you although I know that nobody, including you, owe me anything.

  • Create Chart/Graph From Text Strings

    Re: Create Chart/Graph From Text Strings


    Dave,


    I didn't say I don't know how to custom format a cell/range.


    What I meant is that your replies are too difficult for to understand from the English language point of view.


    I don't understand what the words:"where the underlying number represents "ABC" mean as I don't remember having any underlying numbers.


    nor did I understand the sentence: "...Custom Format a numeric cell" as I do not have any numeric cells.


    I presented the range of cells that I have in my first post.


    It would be nice if you could spare a moment and attach a workbook with my range of characters and a "pie" chart that is relyind directly on them.


    Thanks, Michael