Posts by ravenswood

Is there anyway I can do a Dcount on a part of a string in a column?

Example: in column A, I have the following data
RGGA
GGR
GAGA
RGA

I'd like to be able to do a count of all the rows where the last character in the cell = "A" (the result would be 3 in this case).

Re: Closing Workbook Causes VBE Password Prompt

Quote from rory

Do you have Google Desktop installed?
Regards,
Rory

Yes I do. That can cause this problem?

This is one of the strangest bugs I've ever seen.

I have a workbook that prompts me for a VBAProject Password when I attempt to close the workbook. I'm completely stumped. I have no idea what would trigger this prompt. Unfortunately, I can't attach the workbook because it's filled with confidential company data.

Has anyone experienced this before? For what it's worth, the workbook contains a spreadsheet control on a userform (not sure if this ActiveX control might have something to do with the problem).

I'm confused as to why I get the ActiveX Warning message when I use the Spreadsheet Control on a UserForm. The message implies that if I trust the source, then press "Yes". Isn't this control from the same source as all the other standard toolbox controls (Microsoft)?

This is kind of a bummer for me as I know this message will only confuse my users and my phone will ring off the hook with users claiming I gave them a virus.

Any advice for how do deal with this?

I have spreadsheet with client names. I'd like to build logic that will open a form with client information when a user double-clicks on the client name.

But I have a couple of problems.
- First, I'd like to protect the sheet and hide the formulas. But when I protect the sheet, I get the "the cell or chart you are trying to change is protected" error when I double click the protected cell.

- If I deselect the "Select Locked Cells" options when I protect the sheet, I no longer have the ability to double-click on the cell.

Does anyone have a suggestion on how I can double click on a cell yet not allow the user to edit the cell (or see the formula)?

Re: Min Function Ignoring Blanks

Quote from daddylonglegs

Try this formula in E2 copied down

=MIN(IF(A$2:A$8=D2,IF(B$2:B$8<>"",B$2:B$8)))

confirmed with CTRL+SHIFT+ENTER

Beautiful. Thanks!

Re: Min Function Ignoring Blanks

Edit: I'm having trouble attaching my file. Getting error message "Invalid File Type" from Ozgrid.

I'm having a tough time writing a formula to return the min value for a particular item while ignoring blanks.

In the attached example, I'd like the result of cell E3 = 1 (the minimum for Texas).
What the best formula to compute the min value for Texas (ignoring blanks)? Thank you.

Is there a function that subtracts one range from another?

Let's suppose I have 2 named ranges:
Range1 = A1:A10
Range2 = A7:A10

I want to set Range3 = to the part of Range1 NOT in Range2 (A1:A6)

Re: Countif With Multiple Criteria

sumproduct

Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square

Andy,

Your formula worked like a charm. In retrospect, it seems so simple. I should have thought of it. Keep up the good work.

Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square

Great idea. Thanks.

Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square

I noticed the two or more line feeds thing also. I also think the concatenating spaces contributes to the problem. I am stumped.

Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square

shg, try removing the last 3 cells from the concatenation formula. Does the square go away?

Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square

Here's a sample... Cell A1 has a square on my wife's PC, but no square appears on our home pc.

Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square

Quote from Dave Hawley

See Ger's post above.

I will post an example. I've got to change the data and get my company's name removed to protect the innocent.

In the meantime.... I've got two pc's on my desk right now. On my home pc, if I type =char(10), the cell is displayed as blank. On my wife's laptop, if I type =char(10), it displays as a square. Both are set to wrap text.

Re: CHAR(10) Linefeed / CHAR(13) CarriageReturn Displays Square

I tried using both char(10) and char(13). Both of them display a square (only on some people's machines). I also tried typing alt+enter in the formula bar. The result is always the same. The cell has line breaks just like I want but the square still appears at the end.

The Wrap Text property seems to erase the square but only at and individual cell level. When I concatenate several cells together, I still get the square at the end.

Re: Char(13) Displays Square

Dave, I'm looking to get rid of the square that appears at the end of my cell entry. Andy was correct in that the clean function gets rid of all the line feeds.
Also, I mispoke in my original post. I'm using =char(10) to force line breaks.

Any ideas as to why =char(10) displays as a square box on some people's machines?

I have a cell that is acutally a concatination of several other cells. The formula is something like this....

=A1 & B1 & A1 & C1 & A1 & D1 & A1

Cell A1 contains the formula =char(13). This forces a line break between each data element.

My problem is that on some peoples machines the cell displays with a square at the end. I'm not sure why it does this only on some machines. Is there a better way to do this or is there a way to suppress the square? Thanks.

Is it possible to use a non-contiguous range for the criteria parameter of the DSUM function?

I want to do something like this....
=DCOUNTA(wholesale_all,"Assignment Date",[COLOR="RoyalBlue"]D2:D3,G2:G3[/COLOR])

This formulas won't work the way I have it typed, but is there a workaround to achieve the same result?