# Search:

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

Page 1 of 6 1

1. ## Thread: If Chip Pearson's website ever helped you, please jot a note...

by circledchicken
Replies
67
Views
48,430

### Re: If Chip Pearson's website ever helped you, please jot a note...

My condolences to the family. As everyone has stated, Chip's contribution has been immense. For me his work was an amazing learning and practical resource that has been incredibly useful. The way he...
2. ## Thread: Insert Formula into Cell (VBA)

by circledchicken
Replies
8
Views
1,038

### Re: Insert Formula into Cell (VBA)

You're welcome, thanks for the feedback.
3. ## Thread: Insert Formula into Cell (VBA)

by circledchicken
Replies
8
Views
1,038

### Re: Insert Formula into Cell (VBA)

Yes, try setting the relevant parameters to False like this:

4. ## Thread: decreasing from a stable Quantity Macro

by circledchicken
Replies
20
Views
679

### Re: decreasing from a stable Quantity Macro

You're welcome, I'm glad it helped (by the way I forgot to say the above formula should be confirmed with CTRL-SHIFT-ENTER rather than just ENTER as it is an array formula, but it seems like you...
5. ## Thread: decreasing from a stable Quantity Macro

by circledchicken
Replies
20
Views
679

### Re: decreasing from a stable Quantity Macro

Hi,

Your example isn't completely clear to me (e.g. I don't know why you have -1 as the LookupValue for the Code 1 - shouldn't it be -20?).
If that was just a typo, then perhaps try something...

by circledchicken
Replies
3
Views
197

### Re: Formula woes

You're welcome - thanks for the feedback and have a great day too.

by circledchicken
Replies
2
Views
636

### Re: Super function

Hi,

Here's one way you can try:

I've assumed the column headers are in the range Sheet2!\$A\$2:\$AZ\$2
I've assumed the dates are in the range Sheet2!\$A\$3:\$A\$1000

by circledchicken
Replies
3
Views
197

### Re: Formula woes

Hi and welcome to the forum,

Perhaps try this in H2 and copy down:

=IF(
AND(D2 = F2, E2 = G2),
5,
IF(
9. ## Thread: decreasing from a stable Quantity Macro

by circledchicken
Replies
20
Views
679

### Re: decreasing from a stable Quantity Macro

=IF(
ISNUMBER(MATCH(B2, B\$1:B1, 0)),
LOOKUP(2, 1 / (B\$1:B1 = B2), E\$1:E1),
J2)
- C2

I'm not sure what you mean by 'your stock is not stable anymore'.

A more detailed example...
10. ## Thread: decreasing from a stable Quantity Macro

by circledchicken
Replies
20
Views
679

### Re: decreasing from a stable Quantity Macro

Sorry, I'm not sure I follow this.

Can you apply the original formula replacing the STOCK column with the new Total Stock column?

If not, then perhaps you can provide a more detailed example...
11. ## Thread: decreasing from a stable Quantity Macro

by circledchicken
Replies
20
Views
679

### Re: decreasing from a stable Quantity Macro

Hi,

Can you please provide a more detailed example, showing expected outputs?
In the existing example its not completely clear what you need the results to look like?
12. ## Thread: How to count unique alhpanum values in a filtered column ignoring blank cells

by circledchicken
Replies
3
Views
813

### Re: How to count unique alhpanum values in a filtered column ignoring blank cells

Hi,

That shouldn't happen. Make sure you press CTRL-SHIFT-ENTER rather than just ENTER to confirm the formulae.

If it still doesn't work please provide a small sample dataset showing the result...
13. ## Thread: How to count unique alhpanum values in a filtered column ignoring blank cells

by circledchicken
Replies
3
Views
813

### Re: How to count unique alhpanum values in a filtered column ignoring blank cells

Hi and welcome to the forum,

Perhaps try the following (using CTRL-SHIFT-ENTER rather than just ENTER as these are array formulae):

Unfiltered:

=SUM(
IF(
14. ## Thread: Lookup data in an inconsistent format

by circledchicken
Replies
2
Views
162

### Re: Lookup data in an inconsistent format

Hi,

Maybe you can try something like this in E2 and copy across and down:

=LOOKUP(
9.99999999999999E+307,
IF(
\$B\$2:\$B\$1000 = \$B2,
15. ## Thread: Basic Data Analysis

by circledchicken
Replies
2
Views
202

### Re: Basic Data Analysis

Hi,

Yes, PivotTables would be a good way to do this. Here are some resources that should help:

Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
...
16. ## Thread: Sumifs/contifs criteria across multiple columns

by circledchicken
Replies
1
Views
191

### Re: Sumifs/contifs criteria across multiple columns

Hi,

Perhaps something like this:

=SUMPRODUCT(
(dMod!\$K\$2:\$K\$675 = \$C5) + (dMod!\$L\$2:\$L\$675 = \$C5),
--(dMod!\$G\$2:\$G\$675 >= H\$3),
--(dMod!\$G\$2:\$G\$675 < I\$3))
17. ## Thread: Macro MsgBox Help

by circledchicken
Replies
6
Views
213

### Re: Macro MsgBox Help

Hi,

Another alternative might be something like this:

Sub example()

Dim vArr As Variant
Dim lInp As Long
18. ## Thread: Formula help needed with looking up and if condition

by circledchicken
Replies
3
Views
171

### Re: Formula help needed with looking up and if condition

You're welcome.
19. ## Thread: Formula help needed with looking up and if condition

by circledchicken
Replies
3
Views
171

### Re: Formula help needed with looking up and if condition

Hi abdulhaque and welcome to the forum,

You can try the SUMIF function, something like this:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px...
20. ## Thread: Array Formula Change

by circledchicken
Replies
7
Views
240

### Re: Array Formula Change

You're welcome.
21. ## Thread: Array Formula Change

by circledchicken
Replies
7
Views
240

### Re: Array Formula Change

Hi,

Perhaps you mean something like this (with CTRL-SHIFT-ENTER):

=IF(
OR(
\$R\$2 = "",
MOD(ROWS(B\$10:B10), 2) = 0),
22. ## Thread: Hlookup and index possibly?

by circledchicken
Replies
17
Views
594

### Re: Hlookup and index possibly?

You're welcome, thanks for the feedback.
23. ## Thread: Hlookup and index possibly?

by circledchicken
Replies
17
Views
594

### Re: Hlookup and index possibly?

I'm not sure how you are entering the formula provided.

This works for me in cell O2 (with CTRL-SHIFT-ENTER) and copied down:

=IFERROR(
LOOKUP(
9.99999999999999E+307,
...
24. ## Thread: Hlookup and index possibly?

by circledchicken
Replies
17
Views
594

### Re: Hlookup and index possibly?

I'm not sure I follow what you mean.

Yes, it might be easiest if you post a short spreadsheet with specific examples, the results you are getting and the results you expect.

You could create...
25. ## Thread: Hlookup and index possibly?

by circledchicken
Replies
17
Views
594

### Re: Hlookup and index possibly?

Yes, it will not work correctly if you don't use CTRL-SHIFT-ENTER.

You only need to press CTRL-SHIFT-ENTER in the first cell you put the formula in (don't select a range of cells and press...
Results 1 to 25 of 150
Page 1 of 6 1