# Search:

Type: Posts; User: sdckapr; Keyword(s):

Page 1 of 20 1

1. ## Thread: Help Me Figure Out a Formula?

by sdckapr
Replies
6
Views
72

### An alternative which only requires entering the...

An alternative which only requires entering the values in 1 place is to use match/index.

Option Explicit
Public Function Grade(rng As Range) As String
'DECLARE AND SET VARIABLES
Dim rScore...
2. ## Thread: Calculating Days In Cumulative Between Categories.

by sdckapr
Replies
2
Views
33

### Will 2 pivot tables work like in the attached? ...

Will 2 pivot tables work like in the attached?

Steve
3. ## Thread: Plug date into cell

by sdckapr
Replies
6
Views
91

### If it is more than one cell, you can also use in...

If it is more than one cell, you can also use in VBA something like:
range("A1:A15")=date

If using the shortcut key, you can select the cells you want to change the date of:
ctrl-;
Then use:...
4. ## Thread: Help Me Figure Out a Formula?

by sdckapr
Replies
6
Views
72

### Perhaps this example will work. Steve

Perhaps this example will work.

Steve
5. ## Thread: Round times to the NEAREST hour

by sdckapr
Replies
2
Views
31

### You can essentially accomplish what you have with...

You can essentially accomplish what you have with your formula with:
=INT(J2*24+0.5)

Steve
6. ## Thread: Plug date into cell

by sdckapr
Replies
6
Views
91

### You can use the line of code: ActiveCell = Date...

You can use the line of code:
ActiveCell = Date

You don't need a button, you can just use the shortcut key:
ctrl-;

to add the date to the current cell. [ctrl-shift-; (= ctrl-: ) adds the...

by sdckapr
Replies
4
Views
42

Steve
8. ## Thread: Formula to calculate values > 0 on row 5 on Pivot Table

by sdckapr
Replies
12
Views
131

### I don't understand your use of the Grand_Total...

I don't understand your use of the Grand_Total named range. In the file in Aug 14, it was in cell FW\$ so I assumed it served some purpose unrelated to the pivot table. But in this one the name is...
9. ## Thread: Formula to calculate values > 0 on row 5 on Pivot Table

by sdckapr
Replies
12
Views
131

### Change the ">60" to the respective "60-90",...

Change the ">60" to the respective "60-90", "90-120", and ">120" as listed in the Pivot table col a.

Steve
10. ## Thread: Formula to calculate values > 0 on row 5 on Pivot Table

by sdckapr
Replies
12
Views
131

It is your formula, with my addition to give you the range desired. That part is:

OFFSET('Pivot Table'!\$B\$1,MATCH(">60",'Pivot Table'!\$A:\$A,0)-1,0,1,MATCH("Grand Total",'Pivot Table'!\$4:\$4,0)-2)
...
11. ## Thread: Formula to calculate values > 0 on row 5 on Pivot Table

by sdckapr
Replies
12
Views
131

=IF(ISBLANK(A4),"",IF(A4="Grand Total",MAX(A4),SUMIF(OFFSET('Pivot Table'!\$B\$1,MATCH(">60",'Pivot Table'!\$A:\$A,0)-1,0,1,MATCH("Grand Total",'Pivot...
12. ## Thread: Formula to calculate values > 0 on row 5 on Pivot Table

by sdckapr
Replies
12
Views
131

### Do you have a range named "Grand_Total"? That is...

Do you have a range named "Grand_Total"? That is probably what gives the #name error.

[Your formulas don't seem appropriate to your example. There is nothing in Cols A-K yet your formulas refer...
13. ## Thread: Page x Watermark

by sdckapr
Replies
2
Views
62

### It is not really a watermark since it will not...

It is not really a watermark since it will not print. It is only there for reference. If you don't like it I think you will have to leave page preview mode ...

Steve

by sdckapr
Replies
5
Views
114

### How about a line of code like:...

How about a line of code like:
Anchor:=Range("A15"), _

by sdckapr
Replies
5
Views
114

### Does it work with only 1 substitute:...

Does it work with only 1 substitute:

Or you could create in a cell the concatenated cells and refer to that cell

Steve
16. ## Thread: Rounding Difference CsV File

by sdckapr
Replies
2
Views
61

### Typically these errors involve conversion of...

Typically these errors involve conversion of Base2 values from 15 digits to base 10 equivalents.

A solution is to round to some significant figure value.

Steve
17. ## Thread: Vlookup with Offset

by sdckapr
Replies
2
Views
81

### In your specific example you could uses something...

In your specific example you could uses something like
=VLOOKUP("Dept1",OFFSET(B1,0,0,5,5),5,0)

=VLOOKUP("Dept1",OFFSET(B10,0,0,5,5),5,0)

To be more general, you would need a way for the code...
18. ## Thread: Copy excel files and folder structure from subfolders with vba

by sdckapr
Replies
13
Views
202

### Does it work if you change to: FromPath =...

Does it work if you change to:
FromPath = "C:\Users\John\Working Files\"
ToPath = "D:\Full Backup\"

Steve
19. ## Thread: Excel Web Browser - Plotting Multiple Markers on Google Maps using UK Postal Code

by sdckapr
Replies
8
Views
171

### I have not used Google Maps to change options for...

I have not used Google Maps to change options for its output. The question seems to me to be more about Google Map than Excel, so you may need to look at a Google Map forum. Once you know the form...
20. ## Thread: Excel Web Browser - Plotting Multiple Markers on Google Maps using UK Postal Code

by sdckapr
Replies
8
Views
171

### Does the code at...

Does the code at http://ramblings.mcpher.com/Home/excelquirks/getmaps/mapmarkers answer your questions? in the section marked "The generated html file" it describes the default centering and how to...
21. ## Thread: VBA to select files from a list

by sdckapr
Replies
3
Views
167

### Are you looking for something like? Option...

Are you looking for something like?

Option Explicit
Sub Sub_ArraySelect()
Dim TBArray As Variant, Response As Integer, iOptions As Integer
Dim TB As String, x As Integer, sPrompt As String...
22. ## Thread: Using If,And,Or statements

by sdckapr
Replies
15
Views
1,011

### As I understand it, If year 2 was 4 and year 3...

As I understand it, If year 2 was 4 and year 3 was 5 they would get a bonus since both are >=4. They would not in Year2 if year 1 was 3 and year2 was 4.. nor would they if year1 was 3 and year2 was...
23. ## Thread: Order of events for opening a workbook

by sdckapr
Replies
3
Views
85

### Here are some references....

Here are some references.
http://www.ozgrid.com/Excel/free-training/ExcelVBA1/excelvba1lesson15.htm
http://pixcels.nl/events-in-workbooks/

Here is a Chip Pearson one (and may be the one you...
24. ## Thread: Using If,And,Or statements

by sdckapr
Replies
15
Views
1,011

### The attachment is an invalid link... What is...

The attachment is an invalid link...

What is the error message?

Steve
25. ## Thread: Conditionally Void a Cumulative Sum

by sdckapr
Replies
5
Views
124

### You could use:...

You could use:
=IF(OR(A2=0,B2=1,AND(A1>0,D1=0)),0,IF(SUM(OFFSET(A2,0,1,MATCH(0,OFFSET(A2,0,0,COUNT(A:A)-COUNT(\$A1:A1),1),0)-1,1))=0,1+D1,0))

I reduce the count by the number of entries before. I...
Results 1 to 25 of 500
Page 1 of 20 1