Search:

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

Page 1 of 6 1

Search: Search took 0.11 seconds.

by AlanY
Replies
102
Views
3,049

Re: excel filter - formula

i think i may know what happened, just to clear if you click the update arrow on that saved file above, will you still get 12 lines of result?

by AlanY
Replies
6
Views
106

Re: increment cell reference

=OFFSET(\$A\$1,ROUND((ROW(A3))/2,0),IF(MOD(ROW(A3),2)=0,1,2))

the offset() function is refer to a range (a cell in your requirement) from a reference cell (\$A\$1) by offsetting it with rows and...
3. Thread: LARGE Formula & Return value from MATCH

by AlanY
Replies
6
Views
263

Re: LARGE Formula & Return value from MATCH

see if this is what you're after, btw I've reduced the ranges for testing but try not to reference the whole column as if will slow thing down.

Ex1

by AlanY
Replies
1
Views
22

try

5. Thread: Miltiple if and roundup question

by AlanY
Replies
1
Views
58

Re: Miltiple if and roundup question

just add a max() like this

by AlanY
Replies
6
Views
106

Re: increment cell reference

you're welcome

the formula can be simplified as

=OFFSET(\$A\$1,ROUND((ROW(A3))/2,0),IF(MOD(ROW(A3),2)=0,1,2))

with same results
7. Thread: Two way lookup where row isn't know?

by AlanY
Replies
11
Views
171

Re: Two way lookup where row isn't know?

no no no, you were right to point it out.
i just couldn't see the typo in my original formula until mod Fluff pointed it out.

it's Friday afternoon after all.
8. Thread: Two way lookup where row isn't know?

by AlanY
Replies
11
Views
171

yes, got it.
thanks

by AlanY
Replies
6
Views
106

Re: increment cell reference

try this in H3, and copy down

=OFFSET(\$A\$1,ROUND((ROW(A3)+2)/2,0)-1,IF(MOD(ROW(A3),2)=0,1,2))
10. Thread: Two way lookup where row isn't know?

by AlanY
Replies
11
Views
171

Re: Two way lookup where row isn't know?

TBH, i'm not sure of Snakehips' suggestion or what it's trying to solve.

the match() in post#4 is looking for the exact match anyway
11. Thread: Formula to concatenate text based on criteria and < than 145 characters

by AlanY
Replies
3
Views
69

Re: Formula to concatenate text based on criteria and < than 145 characters

what I would do is to make use of helper columns to concatenate the cells based on the criteria and use the if() function to pick it out.
btw, what are you going to do if it's > 145 characters?
12. Thread: Formula to concatenate text based on criteria and < than 145 characters

by AlanY
Replies
3
Views
69

Re: Formula to concatenate text based on criteria and < than 145 characters

what's the criteria in simple English?
13. Thread: Two way lookup where row isn't know?

by AlanY
Replies
11
Views
171

Re: Two way lookup where row isn't know?

that's what (MATCH("Word1",A1:A10,0)) trying to do
14. Thread: Two way lookup where row isn't know?

by AlanY
Replies
11
Views
171

Re: Two way lookup where row isn't know?

something like this?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color:...

by AlanY
Replies
102
Views
3,049

Re: excel filter - formula

btw, have you turned off the auto calculation?

by AlanY
Replies
102
Views
3,049

Re: excel filter - formula

i don't understand it, at all.
from step 2 above, Cell F5 is 68 same as what I got.
but on the result page I got almost 600 lines of 2 matches or more, 11 from yours is just too small considering...
17. Thread: LARGE Formula & Return value from MATCH

by AlanY
Replies
6
Views
263

Re: LARGE Formula & Return value from MATCH

may be easier if you post a small sample of the data and desired result here.
18. Thread: LARGE Formula & Return value from MATCH

by AlanY
Replies
6
Views
263

Re: LARGE Formula & Return value from MATCH

something like this?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color:...

by AlanY
Replies
5
Views
79

Re: VLOOKUP Fails

no worry.
we all done it before

by AlanY
Replies
5
Views
79

Re: VLOOKUP Fails

the extra ,0 forced an exact match

=IFERROR(VLOOKUP(A2,Detail!\$D\$2:\$E\$166,2,0),"")

by AlanY
Replies
102
Views
3,049

Re: excel filter - formula

this is weird! I can't produce that 12 lines of results from the same date/draw as yours.

this is the file I'm testing, should be the same as yours, could you try to see if you can produce the...
22. Thread: INDIRECT formula cell reference

by AlanY
Replies
14
Views
377

Re: INDIRECT formula cell reference

this is an example to combine indirect() and offset(), see if you can adopt it for your problem

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px...
23. Thread: Combining IF(OR & IF(AND Statements

by AlanY
Replies
10
Views
157

Re: Combining IF(OR & IF(AND Statements

you're welcome
24. Thread: Index/Match - Search Table and then search column

by AlanY
Replies
3
Views
54

Re: Index/Match - Search Table and then search column

you're welcome
25. Thread: Combining IF(OR & IF(AND Statements

by AlanY
Replies
10
Views
157

Re: Combining IF(OR & IF(AND Statements

ok, try this

=IF(COUNTIF(AR29:AR33,"W")=2,0,if(or(T29="W",COUNTIF(T29:T33,"W")=2),1,""))
Results 1 to 25 of 150
Page 1 of 6 1