Posts by nat_m

Re: Count Cells On The Sheet With A Specific Fill Color

Thanks a lot! Got it! Works perfectly

Have a wonderful day!

Nat

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!

Nat

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.

Thanks!

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!

Nat

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

Nat

Re: Importing More Than 65000 Records Into One Worksheet

Quote from Brandtrock

I'm not trying to step on toes here, but if your data already resides elsewhere, have you considered what you are going to do with it once it is in Excel?

In other words, are all 65,000+ records necessary? If so, is it necessary to place them on the worksheet? Could they be read each time the worksheet is used?

I'm not proposing any other solution per se, but frequently when the volume of data is this large, only a subset is being worked with. SQL may be an option that would work with your data as is and retrieve only what you really need.

Just stirring the pot,

Display More

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!

Nat

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

http://www.ozgrid.com/forum/sh…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!
Nat

Re: Compare Two Columns With Names

Quote from h1h

in C1 and fill down:
=IF(ISNA(VLOOKUP(B1;$A$1:$A$11;1;FALSE));"";VLOOKUP(B1;$A$1:$A$11;1;FALSE))

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!

Nat

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

Hi,

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!

Nat

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?

Thanks!

Nat

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!

Nat

Re: Workbooks.open 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!

Nat

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

Private Sub CmdBttn_ChooseProgram_Click()


Dim sFilename As String


On Error GoTo ErrorHandler


sFilename = "Approval_" & Application.InputBox("Input Program")
Workbooks.Open Filename:="C:\Documents and Settings\jsmith\Desktop" & sFilename & ".xls"


ErrorHandler:


'if file name does not exist error message should display. Not defined yet


End Sub

Re: Comparing Two columns

Quote from ByTheCringe2

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

=IF(ISERROR(MATCH(B2,Sheet1!A:A,0)),"",1)

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!

Nat

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

Nat

Re: Comparing Two columns

Quote from ByTheCringe2

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

=IF(AND(ISERROR(MATCH(A1,Sheet2!B:B,0)),ISERROR(MATCH(A1,Sheet3!B:B,0))),0,1)

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!

Nat

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!!!

Nat

Re: Add-In to work for different files

Thanks for the suggestions, guys!

I will try them and let you know if they worked out.

Thanks again! Have a great day!

Nat

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.

Thanks!

Nat