# Search:

Type: Posts; User: Special-K99; Keyword(s):

Page 1 of 6 1

1. ## Thread: Count If Formula with a Defined Range

by Special-K99
Replies
10
Views
85

### Re: Count If Formula with a Defined Range

You need to define "it doesn't work" more specifically.

Is this the cause?
AFME!\$G\$18:\$G\$19,AFME!\$G\$25,AFME!\$G\$43,AFME!\$G\$45,AFME!\$G\$46:\$G\$48,AFME!\$G\$50:\$G\$51,AFME!\$G\$54:\$G\$58,AFME!\$G\$60,AFME!\$G...
2. ## Thread: Excel - Subtracting 2 dates/times (DD/MM/YY HH:MM:SS) to find the difference

by Special-K99
Replies
3
Views
101

### Re: Excel - Subtracting 2 dates/times (DD/MM/YY HH:MM:SS) to find the difference

Toadstool: Crikey, that's the clearest explanation of a solution I've ever seen on this board! Well done!
3. ## Thread: Indirect - Referencing tab

by Special-K99
Replies
10
Views
158

### Re: Indirect - Referencing tab

Why use two INDIRECT() when only one is required?

=INDIRECT("'"&B4&"'!G\$1")
4. ## Thread: Indirect - Referencing tab

by Special-K99
Replies
10
Views
158

### Re: Indirect - Referencing tab

Too many double quotes, try

=IFERROR(INDEX(INDIRECT("'"&\$B\$4&"'!\$B\$1:\$B\$58"),AGGREGATE(15,6,ROW(INDIRECT("'"&\$B\$4&"'!\$C\$1:\$C\$58"))/(INDIRECT("'"&\$B\$4&"'!\$C\$1:\$C\$58")<3),ROW(5:5))),"")
5. ## Thread: hyperlink using a cell with a date in it to complete the link url

by Special-K99
Replies
2
Views
52

### Re: hyperlink using a cell with a date in it to complete the link url

1. Is "Aug 2019" text or a PROPER Excel date?

2. What does the "08" signify in your URL, ie "08-Aug"? It looks like the date 8th Aug - or does the "08" signify the month number of the year, or...
6. ## Thread: Indirect - Referencing tab

by Special-K99
Replies
10
Views
158

### Re: Indirect - Referencing tab

Try

=IFERROR(INDEX(INDIRECT("'"&\$B\$4&"'!\$B\$1:\$B\$58"),AGGREGATE(15,6,ROW(INDIRECT("'"&\$B\$4&"'!\$C\$1:\$C\$58""))/(INDIRECT("'"&\$B\$4&"'!\$C\$1:\$C\$58")<3),ROW(5:5))),"")
7. ## Thread: Complex IF formula

by Special-K99
Replies
9
Views
190

### Re: Complex IF formula

jorismoerings:
This part doesn't look correct

=IF(OR(F2=0,AND(OR(D2=3,D2=4),OR(F2=-2,F2=-1))),0

OP said
IF D2 = 3 AND F2 = -1, G2 = 0

If D2 =3 and F2 = -2 your formula will return 0, thats...

by Special-K99
Replies
2
Views
57

### Re: Sum if?

FYI

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

9. ## Thread: Index match vlookup

by Special-K99
Replies
8
Views
97

### Re: Index match vlookup

In one cell? More than one cell?
Which cells?
10. ## Thread: How do I ignore hidden files?

by Special-K99
Replies
2
Views
49

### Re: How do I ignore hidden files?

Can't you just do this?

For Each objFile In objFolder.Files

If Left(objFile.Name,1)<>"~" then
Cells(i + 1, 62) = objFile.Name
i = i + 1
End If
11. ## Thread: INDEX MATCH Query

by Special-K99
Replies
5
Views
86

### Re: INDEX MATCH Query

Sheet1!A1 is "Hardware Category"
Sheet2!A1 is "Destination"

in Sheet2!C2
=INDEX(Sheet1!B2:I3,MATCH(A2,Sheet1!B\$1:I\$1,0),MATCH(B2,Sheet1!A\$2:A\$3,0))
12. ## Thread: Result based on criteria fro 2 lists

by Special-K99
Replies
12
Views
231

### Re: Result based on criteria fro 2 lists

I might have overdone this but try

in Sheet2!A1:C3 put

A B 5
A C 7
B C 10
13. ## Thread: Result based on criteria fro 2 lists

by Special-K99
Replies
12
Views
231

### Re: Result based on criteria fro 2 lists

Yes. Client A and B in different columns with a mileage, hence the 3 columns
Im just saying why have this

A to B 5 miles
A to C 7 miles
B to C 10 miles
B to A 5 miles (we know this already...
14. ## Thread: Result based on criteria fro 2 lists

by Special-K99
Replies
12
Views
231

### Re: Result based on criteria fro 2 lists

So 3 clients labelled A, B and C

Create a table consisting of 3 columns
Client From, Client To, and Distance

Then just do a VLOOKUP()/INDEX(MATCH(...)) based on the From and To
So
15. ## Thread: Unable to load .data file in excel using VBA

by Special-K99
Replies
7
Views
134

### Re: Unable to load .data file in excel using VBA

.CSV is a comma separated file
.TXT is a text file
.PDF is a Portable Document Format file

What's a .data file ?
Where did you get the file from?

Excel needs to know what type of file .data...
16. ## Thread: Dynamic Decimal Placement

by Special-K99
Replies
12
Views
198

### Re: Dynamic Decimal Placement

Am no VBA expert, does this work?
You want something along the lines of

tbOPrice = Format(CCur(v) / 100, "\$#,#0." & Right(10 ^ txtbox1,Len(10 ^ txtbox1)-1))

10^txtbox1 : If txtbox1 = 2,...

by Special-K99
Replies
14
Views
250

### Re: Complex lookup

=INDEX(B1:F1,MAX(IF(B3:F5="X",COLUMN(B1:F1)-COLUMN(B1)+1)))
Array formula, use Ctrl-Shift-Enter

...

by Special-K99
Replies
14
Views
250

### Re: Complex lookup

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

by Special-K99
Replies
1
Views
90

### Re: Nested IFs?

This is text, you cant multiply text as in J29*K29
You need to establish its not text FIRST.

so, maybe

=IFERROR(IF(J29="As Required","Confirm Qty",IF(K29="Item Not...
20. ## Thread: edit textfile error

by Special-K99
Replies
11
Views
206

### Re: edit textfile error

" the file should not have no more then two lines"

If you only want two lines to be output...

remove the loop, why read the entire file when youre only interested in the first two records?...
21. ## Thread: edit textfile error

by Special-K99
Replies
11
Views
206

### Re: edit textfile error

Oops I mistook & for +

Youre not adding time, you're concatenating a string of times separated by a newline

Its cos of this

ss = ss & s & vbNewLine

22. ## Thread: edit textfile error

by Special-K99
Replies
11
Views
206

### Re: edit textfile error

I'm no VBA expert but looking at that code it will...

1. Read each record in the file and produce an overall total of the amount of seconds in the file, replacing the number of seconds with the...

by Special-K99
Replies
4
Views
97

### Re: Linked cells not keeping conditional formatting

Custom formatting, not condtional formatting.

Excel shows "--"

24. ## Thread: INDEX formula to copy formula horizontally

by Special-K99
Replies
3
Views
67

### Re: INDEX formula to copy formula horizontally

Cant you just copy from top left to the right hand Q4 cell
and paste Special selecting Transpose thereby doing it in one movement?

Its just copied the order and cells perfectly for me.

by Special-K99
Replies
5
Views
50