Posts by nat_m

    Hello, Ozgridians!

    I need your expert help on the following issue:

    If I have cells in my worksheet filled with yellow color, is it possible to count them based only on this criterion (fill color). They might or might not be in the same column.

    Thank you in advance! Your help is greatly appreciated!


    Re: Dropdown List With User Response Type-in

    Quote from royUK

    I ddon't think you will be able to type into the cell if you are using Data validation, you have to select from the list.


    That's what I thought :(

    How about the second part of the question? Is that possible to have list based on another list entry?

    Good time of the day, ozgridians!

    I hope you will be able to help me out. I need an advice with the following question on the dropdown list.

    I know how to create a basic dropdown list, but what I don't know (I don't even know if it is possible to do at all) is how to allow user type-in response if the "Other (please specify)" option is used. I have never done that before so details will be appreciated (if it is complicated :) )

    Here is a small example:

    Column A
    Question 1. What type of report are you using?

    Column B will contain

    Type I Report
    Type II Report
    Other (please specify)

    If Type I or II is chosen from the list, I don't want user to be able to change anything. But If the Other (please specify) option is selected I would like to give user an ability to type in type of the report he/she is using.

    Does anybody know if/how it can be done.

    Also, is there a way to have list options chosen based on another list response?

    Let's say if user answers Type I Report

    Another Question appears with its own pull down list.

    Your help is always appreciated. Thank you in advance!


    Re: Importing More Than 65000 Records Into One Worksheet

    Guys, it took me longer than I thought to get back, but I would like to thank you all for your help. I tried both codes, they both worked fine, so I left it up to the user to choose which one is suitable in which situation :)

    Thanks a lot for your help and suggestions! I am so happy to be a part of the Ozgridland :)

    Have a wonderful weekend :)


    Re: Importing More Than 65000 Records Into One Worksheet

    Thanks for this idea, but I am not very familiar with SQL, and I think pretty much all data is used one ways or the other. So, SQL might sound good for future uses though.

    Thanks again!

    Re: Importing More Than 65000 Records Into One Worksheet

    Wow! Thanks guys for all the suggestions:)

    I will try all of the methods and see which one is working the best in my situation. The file extension is not txt, it is "lib". But I am sure I will be able to convert it to text file if needed.

    Thanks again! :)


    Hello, everyone :)

    It is so nice to check-in once in a while to see all these new ideas and solutions of people's problems. So, I would like to ask a question that really bothered me for some time, and it looks like I cannot find a full answer to it. So here it goes:

    How can I import data which is either more than 65000 records long or it will sum up to be more than 65000 when imported? I need all my data to be on one worksheet, and I don't care if these data will be imported let's say in columns A B C, then once it reaches the 65K, it will be imported to the neighbouring columns D E F, etc.?

    I searched the Ozgrid for an answer and I found a thread…p?t=11270&highlight=65000

    but the links to the answers were dead.

    So, I hope that someone can help me out, because I really would hate to use Access.

    Thanks for your help! :)

    Re: Compare Two Columns With Names

    Quote from h1h

    in C1 and fill down:

    ammend the range as needed and maybe replace semicolon with comma

    Worked wonderfully with the comma, but the result is shifted for some reason:

    Col A Col B Col C
    joe artur
    mary mark
    gordon joe joe
    phil gordon gordon

    Thanks for your help!


    P.S. While typing got the reason: changing the ranges A-->B gives me the right result :)


    Just a quick question.

    There a list of names in columns A & B. How one can compare the names in column A to column B and put the results in column C to show only the names that are the same between columns A & B?

    I know it should be easy, but my brain does not cooperate today :)

    Thanks for the advice!


    Re: Adding Line To Scatter Chart

    Quote from Andy Pope

    You will need to calculate the curve and plot it as a new series.

    Could you please give me more details on this?



    Hi, there.

    I was wondering if anybody would know if there is a way to add a line to a scatter graph without entering a new series of data. Let's say if I need to have a normal distribution added with the +/-3 sigma

    I can add a trendline but it does not have the "bell" shape option.

    Thanks for the help!


    Re: Does Not Open The File

    Quote from flee01

    This may not be it but looking at the code there is no \ at the end of "jsmith\Desktop". Removing the error handler allowed me to see it. Once I did that it opened just fine.

    I knew it was something small!!! It is exactly it!!! One little "\" and the whole code stopped working!!!!

    THANK YOU so much for catching it! :)

    Have a great day!


    Hi, there.

    I was wondering if anybody could catch an error in the following code. I have a file, let's call it a "Guide" which is opened by user and at this time this file has only one purpose - to help user choose the program. After clicking on the "Choose Program" command button, an Inputbox appears where the user types in a program. Then the code should open the file with this program and this is pretty much it. But something is not working with the code below, and I do not see anything wrong with it after looking at it for so long. It just does not do antthing.

    I would greatly appreciate if someone could help me out please, or if there are more effective solutions on how to handle the "Choosing" option

    Re: Comparing Two columns

    Quote from ByTheCringe2

    OK, on Sheet2 and Sheet 3, put this formula in cell C2 and fill down:


    This gives 1 or blank, rather than 1 or 0.

    Thanks! It is a very short, practical, and elegant solution :) I appreciate your help :) Have a great day!


    Re: Comparing Two columns

    Quote from ByTheCringe2

    Can you please attach a workbook showing exactly what result you are expecting.

    sure. This is a small example. Sheet 1 column A contains data that should be found (or not found in the Sheet 2 and Sheet 3 Column B's). If it is found I would like to put 1 in column C ("Match") on the respective row.

    Thanks for helping me out :)


    Re: Comparing Two columns

    Quote from ByTheCringe2

    Put this formula in Sheet 1, cell C1, and fill down:


    Thanks for your quick reply. I apologize but I forgot to mention that I want to mark up row where this entry belongs in column C on Sheet 2 and 3 to make sure that I know where it is located. Thanks for your help!


    Hi, there :) Long time did not do anything with Excel, and I am back to doing some project now, and of course got some questions :) so, hopefully someone will be able to help me out, please.

    I need to compare column A data (row by row) on Sheet 1 with the column B on sheet 2 and sheet 3. If data from column A is present in column B on the sheet 2 or 3, I need to mark it somehow (I thought to put 1 in column C if this entry exists, and 0 if it doesn't).

    Does anyone know what is the fastest way to do it? Thanks!!!


    Re: Add-In to work for different files

    Quote from royUK

    It will display the OpenFile Dialog for .xls

    OK. So that would be the first step of identifying which project user wants to work on. The next step would be to make sure that my Add-In understands the file name chosen, right? Meaning, that somehow I should switch from the hard-coded file name, to the name derived from this DialogBox.

    Any thoughts on this? One idea was from iwrk4dedpr to use ActiveWorkbook which I will try but, I not sure if it is as robust I think as I would like it to be.