Posts by jdahnke

    Re: New Column With Unique Values From Adjacent Column


    royUK and jindon,


    Thank you for the responses.


    First I did not quite specify exactly how I wanted to perform the intended task. First I want the task to be automated. So based on input values it looks through the list of values and determines the first instance of each non-repeating value. The next step is it takes the first instance of each non-repeating value and copies to a new cell in an adjacent column.


    I would like to perform this process without the use of code and only using formulas, if that is possible. If it is not possible without code, how exactly to I get the code to execute when a cell value (input cell) is changed within a set of cells. Note the input cells are not adjacent to one another.


    Any further assistance is much appreciated.


    Jason

    I have a list of values that are not in order yet some repeat. I would like to create a column next to my current column of values that has each unique value from the orginal column. For example, say Column A has 20 values in Rows 1 to 20 and there are 7 unique values in Column A. I would like Column B to list the seven unique values within Column A in Rows 1 to 7 in the order they appear in Column A.

    I am wanting to make the source of a data validation list in Excel non-adjacent cells. Does anyone know if this is possible and if so how do I perform this task?


    When I try to do this I get a message telling me "You may not use unions, intersections, or array constants for Data Validation criteria." Is there a way around this?


    Any help is greatly appreciated!

    Will,


    I will try to explain it as best I can so here goes...


    When looking at the matchinglists example I want to insert a second object list, say objects2, between the objects list and the correspondinglist. Now on sheet1, move the list match cell to column f and create a second select object dropdown cell in column e. The select object drop cell in column d remains the same.


    The items listed in the Objects2 dropdown cell are dependent on the selected value from the Objects list. Lastly, the Correspondinglist is dependent on the values selected in the Objects and Objects2 dropdown lists.


    In other words, I want to make the correspondinglist dependent on two different cell values rather one. Is this possible?


    If you need further clarification, just let me know.


    Thanks again.


    Jason

    I am trying to modify the matchinglists example under the dynamic ranges link from the excel web page.


    Is it possible to modify the returned list formula to look at a second object list before entering the list of values in the matching list cell?


    I have modified the matchinglist example to try and explain what I want to do. It should be attached.


    Sorry can seem to figure out how to attach file!


    Any help would be greatly appreciated. I am stumped!


    Thanks,


    Jason

    Derk,


    Using the sumproduct function like you have it gives me all 0s. Any idea why that is?


    Shades,


    thanks for the help. That seems to work, it just takes an extra column or two.


    Jason

    Derk,


    Okay now what if each animal had an owner. I would want to count each different animal type that had a different owner. For example Sue might have a the first cat and dog and Bob would have the second dog (row 4). Bob's dog would have to register a 1 in column B because he is a different owner.


    Basically, I want to search a column for the same value, but that value might not be the same if a property in another column is different, in which case I would want to count them separately?


    I hope this makes sense, if not I can attach the spreadsheet I am working on.


    Thanks for the start.


    Jason

    I have list of values in a column and would like to determine how many different values there are in the list. Is there a formula or set of formulas that will allow me to do this?

    Andy,


    Thanks for the example. I believe it will help. The easiest looks to be using local objects withevents. I do have one question though. How would this work with more than one checkbox? Do I need to have one "Private WithEvents" statement for each checkbox? If this is the case it may not work for me because I do not know how many checkboxes the user will need. Let me know what you think.


    Egad,


    I haven't been able to look at the examples on the site yet, hopefully there will be some good info there.


    If anybody else has an opinion, let hear your two cents.


    Thanks for the help,


    Jason

    What I want to accomplish is to add a checkbox with all the correct properties, in the correct location and with the correct code to be executed when the checkbox is checked. Now, the first two are not a problem for me as I am fairly familiar with VBA (although it may not be the most efficient), but trying to add code to a checkbox (or any control for that matter) using code escapes me. If anyone knows how this might be accomplished I would be eternally greatful.


    One idea someone suggested was to create a template and just copy it. Sounds like a good idea, but does the code for a control event copy with the control itself?


    Many, many thanks


    Jason

    Thanks guys that does help with the differences between the two control types. I believe I will use the controls toolbox.


    Phil, I thought about just disabling and setting the view property to false whenever the user clicked the checkbox to delete the line. However, this provided a problem about keeping track of the checkboxes that are not currently viewable as a new checkbox would be created whenever the user clicked the Add button to another line of data. I thought it would just be easier to delete the checkbox and not have to worry about keeping track of all invisible checkboxes. That was my logic but if you believe it is flawed by all means let me your opinion.


    What I want to accomplish is to add a checkbox with all the correct properties, in the correct location and with the correct code to be executed when the checkbox is checked. Now, the first two are not a problem for me as I am fairly familiar with VBA (although it may not be the most efficient), but trying to add code to a checkbox (or any control for that matter) using code escapes me. If anyone knows how this might be accomplished I would be eternally greatful.


    One idea someone suggested was to create a template and just copy it. Sounds like a good idea, but does the code for a control event copy with the control itself?


    Many, many thanks


    Jason

    I am working on a spreadsheet and I would like it to create rows as needed by the user. The rows would be created when they click a button. This not really a problem, it is when the user decides a row is no longer needed that I am having trouble. I want the user to be able to click a checkbox and the row for which it is associated is deleted. My problem arises about creating a checkbox with every click of the button. How do I do it? And as I have found there are two different ways (at least that I know of) to do this. So which is the better option to use controls from the forms toolbar or from the control toolbox?


    Any help would be greatly appreciated.


    Jason