# Posts by yjoshi

I could get across avoiding blanks inbetween the list.

Now you get only the UNUSED numbers in the list.

Hope this helps now.

See the sheet attached.

## Files

Hi,

I am using Excel 2000, so no problem for this as well.

As this quote has gone in a deadly loop.... it is easy to miss out on the final puroose of the formula for a person reading later.

I am attaching a sheet which explains step by step what is done, and tries to tally the result with the desired one.

Hope this helps.

## Files

Whats more .... found another shorter way to do this.....

"=SUM((ISNUMBER(SEARCH(B2&"*"&C2,LOWER(A2:A5),1)))*1)"

Using search function using * operator

It tries to search "pa*1" in the range....

You never know what you can find in excel See attachment

## Files

Hi,

I think the use of If is not really required,

What you can do is.... multiple the two series of true & false and you will get the numbers...

"=SUMPRODUCT(ISNUMBER(FIND(B2,LOWER(A2:A5),1))*ISNUMBER(FIND(C2,A2:A5,1)))"

"=SUMPRODUCT(IF(ISNUMBER(FIND(B2,LOWER(A2:A5),1)),1,0),IF(ISNUMBER(FIND(C2,A2:A5,1)),1,0))"

This will certainly reduce the code, and also use internal features of excel (i.e. TRUE = 1 & False = 0) and their multipliaction...

I tried it in your sheet and it worked well.

What do you think???

Thanks for your input on this ....

Also thanks for your insight into the OL object issues

Interesting question ...

I will do it this way, instead of changing it in page, I change the value in a cell, say E1

and then change all the page values.... by this code....

Code
``````Private Sub Worksheet_Change(ByVal Target As Range)
For Each pvt In ActiveSheet.PivotTables
pvt.PivotFields("City").CurrentPage = Range("E1").Value
Next
End If
End Sub``````

It is working for me... .let me know if it does not.... I will attach a file.

HTH

Your query was not irrelevant Put this code so that when you start the form for the first time, even if the caption is
something different, it will be set right.

Code
``````Private Sub UserForm_Initialize()
CommandButton1.Caption = "Start"
End Sub``````

Following code works with MS Outlook.

Hope this helps ... Interesting requirement BTW ...

Also if A1 and A2 are calculated fields, please put proper range you want to compare, or if you want to alert / send mail every time there is a change, you may choose to remove the criteria.

Hi,

You can do it in two ways. Simple formula and array formula.

If you want a simple formula, : enter following in any cell other than A1 to A9
=SUMPRODUCT((LEFT(UPPER(A1:A9),3)="APP")*1)

What it does is....
1. Takes values in A1 to A9 and makes it CAPITALIZED
2. Takes first 3 chars and compates with APPS
3. if this is true, it generates an array of TRUE & FALSE
4. TRUE = 1 and FALSE = 0
5. when you multiply it by one, it creates series of 1s and 0s
6. when you do a sumproduct, it totals all 1s and gives you the result.

IF you want to use Array Formula for this : use following
{=SUM((LEFT(UPPER(A1:A9),3)="APP")*1)}

Note that, you have to enter =SUM((LEFT(UPPER(A1:A9),3)="APP")*1) and press
"Ctrl + Shift +Enter" to get the { } s and not enter manually.

You can see following links for getting more info on WHAT CAN BE ACHIEVED WITH ARRAY FORMULAE :: enjoy
http://www.emailoffice.com/excel/arrays-bobumlas.html

Also see array formulas link on OZGRID.
http://www.ozgrid.com/Excel/arrays.htm

I suggest it is better to use NA() function instead of entering #NA..

I will suggest =IF(A10="N/A",N/A(),A10/B10)...

Hope this helps...
http://peltiertech.com/Excel/Charts/index.html

Take it from me.... If you have not seen this site, PLEASE DO.

You can see LOT of these chart related examples at

I have done this ample time, but never faced an issue.

Can you try this?

Create a new file, put many controls on that page then try to print prieview....

If the problem persists, then this can be a BUG with Excel 2002 ... otherwise looks like a file corruption.

If you can tell, what do you mean by, "File gets corrupted" will really help By the way, "HOW LARGE" is the file?

~Yogendra

Hi Admin, (Quite sure this is not Dave's Update)

The issue is, Daniel wants to have the list and also avoid duplicates.
So just avoiding duplicates does not solve the issue.

Just a clarification Here is how you can start....

1. Create a List of numbers a person can use
2. Besides the list create another list, which will exclude those items which
You can do this by formula :
=IF(ISNA(MATCH(A1,Sheet1!A:A,FALSE)),A1,"")
Where, A1 is from the original list, Sheet1!A:A is the range where you want
to have the validation, i.e. entry row....
what it means is, if this number is already used, it shows "" otherwise the
number.
3. Create a named range for this new range, say unused_nums
4. Now create the validation with this named range.

You will see blanks in place of numbers that are already used.

I know this is not the best solution, if you have many numbers that can be blank but it does work.

HTH

I will suggest a simpler solution

Just use one button with Caption "Start" and in the run time change it
to "Stop" once it is clicked.

If the caption is Start when clicked, it will enter date and time in Cols A & B respectively
If the caption is Stop when clicked, it will enter date and time in Cols C & D respectively, in the same row it started, thats why i have used (1,1) in first part and (0,3) in second part

Let me know if this does not solve your problem

Hi Will,

You can access properties of a particular condition using

Code
``? ActiveCell.FormatConditions.Item(1).Interior.ColorIndex``

But the question that WHICH item is currently activated is still not known...

Looks to be an interesting problem, but I think this can be a good starting point

I will suggest following code

Code
``````Private Sub CommandButton1_Click()
Range("A65536").End(xlUp).Offset(1, 0).Value = Now
End Sub

Private Sub CommandButton2_Click()
Range("A65536").End(xlUp).Offset(0, 1).Value = Now
End Sub``````

The Reason for replacing now with Date & time is that its combination
of both and you can take out all the information you need with this.

Following code can help you to get a complete list of all the links on a particular sheets shown on a new sheet
You can then go through the list and determine if there is any unwanted link.

Here goes the code

Code
``````Sub page_of()
Dim sht As Worksheet
For Each sht In Sheets
sht.Range("A20").Value = "Page " & sht.Index & " of " & Sheets.Count
Next
End Sub``````

If you want this for printing purpose... i will suggest another way to do this.
On each sheet setup appropriate header or footer (Page # of #). then select all the sheets you want to print and see print priview. You will see that appropriate page numbers are set and works fine even if one sheet has more than one pages.

Hope this Helps

Do you want Paste Transpose?

Code
``````Sub CopyTest1()
Range("A1:H18").Copy
Range("A65536").End(xlUp).Offset(7, 0).PasteSpecial Transpose:=True
Range("A65536").End(xlUp).Select
End Sub``````

My sincere apologies if i am still unable to understand.... If you can describe your problem better, it can help