# Search:

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

Page 1 of 6 1

1. ## Thread: Index match vlookup

by Special-K99
Replies
6
Views
43

### Re: Index match vlookup

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

by Special-K99
Replies
2
Views
38

### 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
3. ## Thread: INDEX MATCH Query

by Special-K99
Replies
4
Views
38

### 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))
4. ## Thread: Result based on criteria fro 2 lists

by Special-K99
Replies
12
Views
192

### 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
5. ## Thread: Result based on criteria fro 2 lists

by Special-K99
Replies
12
Views
192

### 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...
6. ## Thread: Result based on criteria fro 2 lists

by Special-K99
Replies
12
Views
192

### 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
7. ## Thread: Unable to load .data file in excel using VBA

by Special-K99
Replies
7
Views
89

### 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...
8. ## Thread: Dynamic Decimal Placement

by Special-K99
Replies
12
Views
191

### 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
243

### 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
243

### 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
82

### 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...
12. ## Thread: edit textfile error

by Special-K99
Replies
11
Views
192

### 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?...
13. ## Thread: edit textfile error

by Special-K99
Replies
11
Views
192

### 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

14. ## Thread: edit textfile error

by Special-K99
Replies
11
Views
192

### 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
91

### Re: Linked cells not keeping conditional formatting

Custom formatting, not condtional formatting.

Excel shows "--"

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

by Special-K99
Replies
3
Views
66

### 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
48

Looks odd to me.

Does it contain < > characters?
The forum often displays posts with < > characters incorrectly.
To get round this places spaces around the < >...
18. ## Thread: Using an "indirect" formula without a cell reference

by Special-K99
Replies
12
Views
435

### Re: Using an "indirect" formula without a cell reference

Looks like the forum has incorrectly displayed your post (that IF statement is incomplete), probably due to < > characters, place spaces before and after the < > characters.
There is another way to...
19. ## Thread: IFs, ANDs, and BUTs

by Special-K99
Replies
8
Views
113

### Re: Need Help with IFs, ANDs, and BUTs

Nope, that's impossible.
If you look at the second IF condition, the ISNUMBER() condition, the only possible results for TRUE and FALSE are AV76 or "Hold".
The only way for the result to be N/A is...
20. ## Thread: Using an "indirect" formula without a cell reference

by Special-K99
Replies
12
Views
435

### Re: Using an "indirect" formula without a cell reference

Can you describe in words what youre trying to do?
21. ## Thread: IFs, ANDs, and BUTs

by Special-K99
Replies
8
Views
113

### Re: Need Help with IFs, ANDs, and BUTs

This should work

=IF(AV14="Yes",IF(ISNUMBER(AV76),AV76,"Hold"),"N/A")
22. ## Thread: Countif doesnt work across sheets

by Special-K99
Replies
1
Views
76

### Re: Countif doesnt work across sheets

That's called a 3D Reference and though SUM works, COUNTIF does not.

There's a solution here

https://www.mrexcel.com/forum/excel-questions/224433-why-am-i-getting-error-countif-3d-reference.html
23. ## Thread: Using an "indirect" formula without a cell reference

by Special-K99
Replies
12
Views
435

### re: Using an "indirect" formula without a cell reference

This

=T:T&" "&IF(AA:AA<16,1,IF(AND(AA:AA>15,AA:AA<31),2,3))

is not using the correct logic

IF(AA:AA<16 is FALSE then it MUST be > 15 so your formula should say

=T:T&"...
24. ## Thread: Drop Down depencies

by Special-K99
Replies
4
Views
72

### Re: Drop Down depencies

Cross-posted here

https://www.excelforum.com/excel-formulas-and-functions/1285767-drop-down-depencies.html#post5172418
25. ## Thread: Get value if "1"

by Special-K99
Replies
8
Views
76

### Re: Get value if "1"

Formulas are not good for this, suggest you use VBA, I'm not an expert in that I'm afraid
Results 1 to 25 of 150
Page 1 of 6 1