# Search:

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

Page 1 of 6 1

1. ## Thread: SUM i to n without Using Cell References

by pgc01
Replies
3
Views
109

### Re: SUM i to n without Using Cell References

You can use

=SUM(PV(3%, {3,6,9,12},, 100))

or, since you have an arithmetic progression with common difference of 3 also

=SUM(PV(3%, 3*{1,2,3,4},, 100))

which makes it easier to adapt if...
2. ## Thread: SUM i to n without Using Cell References

by pgc01
Replies
3
Views
109

### Re: SUM i to n without Using Cell References

Hi

Not clear to me

You mean you want add the PV values using a sequence of n's, something like

=PV(3%, 1,, 100)+PV(3%, 2,, 100)+PV(3%, 3,, 100)+ ... + PV(3%, 10,, 100)

?

by pgc01
Replies
6
Views
123

### Re: Function arguments

Hi

Not sure I understand correctly, check this that covers both cases

If Target.Row < 7 Or Target.Row > 37 Then Exit Sub
If Target.Column < 8 Or Target.Column > 1087 Then Exit Sub
If...

by pgc01
Replies
6
Views
123

### Re: Function arguments

You're welcome. Thanks for the feedback.

by pgc01
Replies
6
Views
123

### Re: Function arguments

Hi

If I unserstand correctly you have 180 groups of 6 columns for which you are intersted in the first 2 columns of each group.

The columns are 8:1087(13+179*6)
The rows you want are 7:37

...
6. ## Thread: Moving code to a function - not working

by pgc01
Replies
1
Views
91

### Re: Moving code to a function - not working

Hi

I don't have excel here but I don't remember a method IF in the WorksheetFunction object.

I've just checked the help here and I don't see it.
...
7. ## Thread: Splitting a cell by delimiter with formula. Warning: 312 splits needed for this.

by pgc01
Replies
10
Views
267

### Re: Splitting a cell by delimiter with formula. Warning: 312 splits needed for this.

Hi

The numbers you posted do not match with the ones in the image.

To be clear,

- you have all the numbers you posted in just 1 cell separated by spaces
- you want to...
8. ## Thread: MsoType for converted Smartshape Rectangles

by pgc01
Replies
3
Views
235

### Re: MsoType for converted Smartshape Rectangles

Oh! Well, the answer to the Recrangles question was easy. For the Rectangles question I'll need more time. :)
9. ## Thread: MsoType for converted Smartshape Rectangles

by pgc01
Replies
3
Views
235

### Re: MsoType for converted Smartshape Recrangles

Hi

They are MsoFreeform. You get them when you draw a polyline.
In the Ribbon->Insert->Shapes you can click on the symbol that has the tooltip Freeform.
You just click on vertices and the tool...
10. ## Thread: Conditional Formatting Referencing Color of Another Cell

by pgc01
Replies
6
Views
179

### Re: Conditional Formatting Referencing Color of Another Cell

In that case I'll repeat what Fluff said: Not with conditional formatting, I'm afraid. :(
11. ## Thread: Conditional Formatting Referencing Color of Another Cell

by pgc01
Replies
6
Views
179

### Re: Conditional Formatting Referencing Color of Another Cell

Is the background colour in A2 random or is there some logic to it? If the latter is true you can use the same logic.
12. ## Thread: Array formula with criteria

by pgc01
Replies
5
Views
116

### Re: Array formula with criteria

You're welcome. Thanks for the feedback.
13. ## Thread: Array formula with criteria

by pgc01
Replies
5
Views
116

### Re: Array formula with criteria

That would mean that the result of the product is zero, which is the case because you set the default value to zero. If you multiply by zero you get zero.

Try 1 or nothing,
...
14. ## Thread: Array formula with criteria

by pgc01
Replies
5
Views
116

### Re: Array formula with criteria

Hi

I think you misplaced a parenthesis.

I guess you meant

... ,YEAR(DateExample)=D8,
15. ## Thread: How to use Dsum with two criteria

by pgc01
Replies
12
Views
455

### Re: How to use Dsum with two criteria

Hi

There are many ways to do this
In this example I used 2 formulae for the criteria but you could also use just 1, I use 4 cells to write the start and end dates and time but you could use just...
16. ## Thread: Area Chart Problem

by pgc01
Replies
7
Views
177

### Re: Area Chart Problem

I'm glad it helped. Thanks for the feedback.
17. ## Thread: Area Chart Problem

by pgc01
Replies
7
Views
177

### Re: Area Chart Problem

Hi

The formula is not to be written in a cell. YOu should define a name with it.

In ribbon->formulas->Name Manager press New and enter

Name: Series1Y
Scope: Sheet1 (or other worksheet if...

by pgc01
Replies
6
Views
132

### Re: Issue scrolling on a spreadsheet

Sorry, I don't know what the problem may be.

You could have some vba that would restrict the scroll during a certain time when the workbook opens but you seem to know your way around vba and so...
19. ## Thread: Area Chart Problem

by pgc01
Replies
7
Views
177

### Re: Area Chart Problem

Hi

For ex. for the first series define the name

Name: Series1Y
Refers to: =OFFSET(Sheet1!\$B\$3,,,,COUNTIF(Sheet1!\$B\$3:\$F\$3,">0"))

and then in the chart use for the series values:
...
20. ## Thread: Excel VBA: Copy multiple Column of a Table

by pgc01
Replies
2
Views
84

### Re: Excel VBA: Copy multiple Column of a Table

Similar if you want to use .DataBodyRange

lstO.DataBodyRange.Offset(1, 0).Resize(18).Copy Destination:=Range("Z1")
21. ## Thread: Excel VBA: Copy multiple Column of a Table

by pgc01
Replies
2
Views
84

### Re: Excel VBA: Copy multiple Column of a Table

Hi
Welcome to the board

See if this helps:

Dim lstO As ListObject

Set lstO = ActiveSheet.ListObjects("MyTable")

by pgc01
Replies
6
Views
132

### Re: Issue scrolling on a spreadsheet

Hi

Mayb you have restricted the scroll area of the worksheet?

Try executing in vba:

ActiveSheet.ScrollArea = ""

or use the sheet properties dialog and clear the scrollarea (for ex. in...

by pgc01
Replies
4
Views
187

### Re: columns question

Hi

If you have a long list of columns, is there not some logic to it, like every 4th column, or columns that have a header starting with "MKT" or something that allows you to identify the ...
24. ## Thread: Filter only displays 10,000 unique items

by pgc01
Replies
1
Views
154

### Re: Filter only displays 10,000 unique items

Hi

You can't, not directly using the autofilter.

You can check MS support here:

https://support.microsoft.com/en-us/help/295971/not-all-items-are-displayed-in-the-autofilter-pivottable-list...
25. ## Thread: Search a string in one cell for an exact match of a value from another cell

by pgc01
Replies
3
Views
65

### Re: Search a string in one cell for an exact match of a value from another cell

You're welcome.
Results 1 to 25 of 150
Page 1 of 6 1