# Search:

Type: Posts; User: steve the fish; Keyword(s):

Page 1 of 6 1

1. ## Thread: VLOOKUP not finding data that is present

by steve the fish
Replies
8
Views
103

### Re: VLOOKUP not finding data that is present

Your formula is wrong but if its showing na error then its not finding the lookup value in column D of the sheet. Use CTRL-F to confirm the value is there. Make sure entire cell contents is checked.
2. ## Thread: IF Statement - brain fog!

by steve the fish
Replies
5
Views
69

### Re: IF Statement - brain fog!

There is a principle of mathematics that multiplication and division are done before addition and subtraction so maybe put some brackets around this part:

C4-D4*G4

to become

(C4-D4)*G4
...
3. ## Thread: Array Formula Issue

by steve the fish
Replies
6
Views
92

### Re: Array Formula Issue

Its because AND doesnt produce an array. If you need AND use multiplication.
4. ## Thread: converting a 6 digit string into a 3 character string

by steve the fish
Replies
4
Views
103

### Re: converting a 6 digit string into a 3 character string(HELP)

Hi. Test this:

=CHAR(MID(A1,LEN(A1)-4,1)+IF(MID(A1,LEN(A1)-5,1)="3",48,64))&CHAR(MID(A1,LEN(A1)-2,1)+IF(MID(A1,LEN(A1)-3,1)="3",48,64))&CHAR(MID(A1,LEN(A1),1)+IF(MID(A1,LEN(A1)-1,1)="3",48,64))
5. ## Thread: SUMIF - dates, ignore times?

by steve the fish
Replies
4
Views
57

### Re: SUMIF - dates, ignore times?

You can using SUMIFS. Greater than or equal to the date and less than the date +1.
6. ## Thread: Lookup and Populate cell

by steve the fish
Replies
5
Views
91

### Re: Lookup and Populate cell

Put this in A3 and copy down 31 cells. You may need to change the year if it happens you need a leap year February. Is that what you mean?

=IFERROR(DAY(ROWS(\$A\$1:A1)&LEFT(\$A\$2,3)&2019),"")

May...
7. ## Thread: VBA using a set range inside an indirect formula

by steve the fish
Replies
2
Views
44

### Re: VBA using a set range inside an indirect formula

You could do this. YearRng then becomes a string which you can concatenate into the formula string:

YearRng = Application.InputBox("Please select the year range for the first location in your...
8. ## Thread: slow formula. Is there a faster way?

by steve the fish
Replies
6
Views
118

### Re: slow formula. Is there a faster way?

You still didnt really answer what i was thinking as if you delete row 84 your formula then has a different lookup value. It may be best to give a small sample of your sheet so we could take a look.
9. ## Thread: slow formula. Is there a faster way?

by steve the fish
Replies
6
Views
118

### Re: slow formula. Is there a faster way?

Cant say as i really understand that. If you delete row 84 then your vlookup formula, for example, has a different lookup value. Hows does that all work? Id suggest avoiding the deleting. Why is that...
10. ## Thread: slow formula. Is there a faster way?

by steve the fish
Replies
6
Views
118

### Re: slow formula. Is there a faster way?

The 2nd one can be simplified to:

=SUM(\$G\$84:G84)

assuming that your lookup value is within the lookup array. If it isnt use what i gave before.
11. ## Thread: slow formula. Is there a faster way?

by steve the fish
Replies
6
Views
118

### Re: slow formula. Is there a faster way?

Do these work the same way?

=VLOOKUP(A84,CAPACITY!\$AM:\$BL,26,FALSE)
=SUM(INDEX(\$G:\$G:G:G,MATCH(\$A84,\$A:\$A,0),0))
12. ## Thread: IF(ISNUMBER(SEARCH formula considering 2.5 and 5 as same. How to check for exact match?

by steve the fish
Replies
5
Views
54

### Re: IF(ISNUMBER(SEARCH formula considering 2.5 and 5 as same. How to check for exact match?

You wouldnt normally do a lookup type of formula as you have tried there. Id be creating a lookup table. This is how it would look hardcoded into the formula:
...
13. ## Thread: INDEX-MATCH with LEFT 10 digits

by steve the fish
Replies
3
Views
118

### Re: INDEX-MATCH with LEFT 10 digits

Put =LEFT(E2,10) in a cell. Copy it. Press CTRL-F. Paste it into the find what box. Click optionsand make sure match entire cell contents is checked. Highlight column AQ. Press find. Does if find...
14. ## Thread: How to leave blank if no data entered

by steve the fish
Replies
2
Views
65

### Re: How to leave blank if no data entered

Hi. Just use an IF test at the start:

=IF(C2="","",IF(DATEDIF(C2,TODAY(),"y")=0,"",DATEDIF(C2,TODAY(),"y")&" years, ")&IF(DATEDIF(C2,TODAY(),"ym")=0,"",DATEDIF(C2,TODAY(),"ym")&" months,...
15. ## Thread: How to change vb script to use active row

by steve the fish
Replies
4
Views
104

### Re: How to change vb script to use active row

This would do that:

Dim str_find As String, c As Range

str_find = "whatever"
Set c = Columns("A").Find(What:=str_find, LookAt:=xlWhole)
If Not c Is Nothing Then c.Offset(0, 3).Copy
16. ## Thread: Alingment of text in one cell

by steve the fish
Replies
8
Views
112

### Re: Alingment of text in one cell

Your options ive outlined above. If it moves to a new line its because there isnt enough space even if you think there is.
17. ## Thread: Argument not optional message

by steve the fish
Replies
16
Views
168

### Re: Arguemnt not optional message

Thats because it needs worksheet change as i said. With selection change nothing happens until you change the selection. With worksheet change its when you change the cell contents.
18. ## Thread: Argument not optional message

by steve the fish
Replies
16
Views
168

### Re: Arguemnt not optional message

Maybe try this in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range, c As Range

Set rng = Intersect(Target, Rows(6))

If Not rng Is Nothing Then
19. ## Thread: Argument not optional message

by steve the fish
Replies
16
Views
168

### Re: Arguemnt not optional message

If there was something in the row you can convert it to uppercase but there isnt an uppercase format as such.
20. ## Thread: Alingment of text in one cell

by steve the fish
Replies
8
Views
112

### Re: Alingment of text in one cell

Those are not 'big spaces'. A space is a space. They are spaces that you have asked to justify across the cell width. If your 'big space' became small then you just end up with left alignment.
21. ## Thread: Argument not optional message

by steve the fish
Replies
16
Views
168

### Re: Arguemnt not optional message

What is it you are trying to do? There wont be anything in those cells.
22. ## Thread: Alingment of text in one cell

by steve the fish
Replies
8
Views
112

### Re: Alingment of text in one cell

Hi. Im afraid those are your choices. Left align, right align, centre or justify. If you dont like the look of it your other options are to resize the cell or alter the text size until you do like it...
23. ## Thread: How to get the address of the formula's result from 2D array (Matrix)

by steve the fish
Replies
3
Views
53

### Re: How to get the address of the formula's result from 2D array (Matrix)

The only way to do that i think is to search for the result in the matrix. That isnt as bulletproof as using the formula conditions to build the address formula.
24. ## Thread: VBA code for excel form

by steve the fish
Replies
12
Views
542

### Re: help with a simple VBA code for excel form

Try using:

CDate(TextBox1.Text)

rather than just Textbox1.Text
25. ## Thread: How to get the address of the formula's result from 2D array (Matrix)

by steve the fish
Replies
3
Views
53

### Re: How to get the address of the formula's result from 2D array (Matrix)

You could do this: