# Search:

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

Page 1 of 6 1

1. ## Thread: VLOOKUP 255 cell limit workaround - help

by Joe4
Replies
7
Views
36

### Re: VLOOKUP 255 cell limit workaround - help

No worries!

Just want to make sure that they do not get unintended results...
2. ## Thread: VLOOKUP 255 cell limit workaround - help

by Joe4
Replies
7
Views
36

### Re: VLOOKUP 255 cell limit workaround - help

Dante,

I would actually recommend using:
=VLOOKUP(C2,A:B,2,0)

If you leave the last argument off, it does an "approximate" match, and seldom would you ever want an "approximate" birthday if...
3. ## Thread: Calculation just doesnt add up

by Joe4
Replies
4
Views
22

### Re: Calculation just doesnt add up

In mathematics, the order of precedence perform multiplication BEFORE addition.
So to do what you want, use parentheses, i.e.

=(E18+E34)*0.45
4. ## Thread: VLOOKUP 255 cell limit workaround - help

by Joe4
Replies
7
Views
36

### Re: VLOOKUP 255 cell limit workaround - help

Do you mean your lookup range has more than 255 rows?
There is no limitation on that. Excel can handle that just fine.

If that is not what you are talking about, please explain in more detail,...
5. ## Thread: SUMIF based upon worksheet name?

by Joe4
Replies
5
Views
85

### Re: SUMIF based upon worksheet name?

Yes (or you can use SUMPRODUCT).

See: https://www.techonthenet.com/excel/formulas/sumifs.php
6. ## Thread: SUMIF based upon worksheet name?

by Joe4
Replies
5
Views
85

### Re: SUMIF based upon worksheet name?

Let's say that you have a working SUMIF function like this that returns the correct value for Month for the name in cell B4 on the current sheet:

=SUMIF(May!A:A,B4,May!B:B)
Now, let's say that...
7. ## Thread: Unique Code for Same Names Appearing in a Column

by Joe4
Replies
5
Views
47

### Re: Unique Code for Same Names Appearing in a Column

You are welcome.
8. ## Thread: Unique Code for Same Names Appearing in a Column

by Joe4
Replies
5
Views
47

### Re: Unique Code for Same Names Appearing in a Column

Did not see the formula I provided above?
If you do not like having to place a 1 in the first cell only (E2), you could use this formula in all cells instead:

=IF(ROW()=2,1,IF(D2=D1,E1,E1+1))
9. ## Thread: Unique Code for Same Names Appearing in a Column

by Joe4
Replies
5
Views
47

### Re: Unique Code for Same Names Appearing in a Column

Assuming that this is going in column D, and the first line of data is row 2 (header in row 1), place a 1 in cell E2, and then place the following formula in cell E3 and copy down for all rows:
...
10. ## Thread: Format(1000, "##0.000E+00") not working to format as engineering type

by Joe4
Replies
9
Views
97

### Re: Format(1000, "##0.000E+00") not working to format as engineering type

Yeah, there are many Excel functions that don't appear in VBA, and then some that are similar that go by different names (i.e. the "Text" Excel function is similar to the "Format" VBA function).
And...
11. ## Thread: Format(1000, "##0.000E+00") not working to format as engineering type

by Joe4
Replies
9
Views
97

### Re: Format(1000, "##0.000E+00") not working to format as engineering type

If there is no digit to fill it because it is less than the value, it will pad fill it with a zero.

I believe that the issue you are having is there does not appear to be a way to tell the...
12. ## Thread: How to replace weekend and capture the next business day

by Joe4
Replies
3
Views
65

### Re: How to replace weekend and capture the next business day

That looks significantly different from your original post.

It does not matter what your date format is, as long as the entries are actually dates, and not text or numbers.
Can you confirm? An...
13. ## Thread: Simple Formula in Excel?

by Joe4
Replies
1
Views
52

### Re: Simple Formula in Excel?

Duplicate: https://www.mrexcel.com/forum/excel-questions/1107188-help-excel-formula-pulling-data-another-sheet.html

Please do not post the same question multiple times. All clarifications,...
14. ## Thread: Macro ActiveSheet.Formula Object doesn't support this property or method error

by Joe4
Replies
12
Views
66

### Re: Macro ActiveSheet.Formula Object doesn't support this property or method error

You are welcome.
And you learned a new little trick for getting VBA code! :)
15. ## Thread: Format(1000, "##0.000E+00") not working to format as engineering type

by Joe4
Replies
9
Views
97

### Re: Format(1000, "##0.000E+00") not working to format as engineering type

Excel functions and VBA functions are not the same, and do not always work the same.

I was looking through that article, and I could find that. I even did a search on "engineer" and it found no...
16. ## Thread: Format(1000, "##0.000E+00") not working to format as engineering type

by Joe4
Replies
9
Views
97

### Re: Format(1000, "##0.000E+00") not working to format as engineering type

Sorry, I was not familiar with the Engineering format.

I came across this article. Perhaps it will help:
https://excelribbon.tips.net/T012874_Engineering_Calculations.html
17. ## Thread: How to replace weekend and capture the next business day

by Joe4
Replies
3
Views
65

### Re: How to replace weekend and capture the next business day

For a date in cell A1, try this formula:

=IF(WEEKDAY(A1,11)<6,A1,WORKDAY(A1-1,1))
If the day in cell A1 is a weekday, it will return that day. Otherwise, it will return the following Monday.
18. ## Thread: Format(1000, "##0.000E+00") not working to format as engineering type

by Joe4
Replies
9
Views
97

### Re: Format(1000, "##0.000E+00") not working to format as engineering type

This works for me:

Debug.Print Format(1000,"0.000E+00")
19. ## Thread: Macro ActiveSheet.Formula Object doesn't support this property or method error

by Joe4
Replies
12
Views
66

### Re: Macro ActiveSheet.Formula Object doesn't support this property or method error

Things can get tricky when you are trying to add in a formula that has literal quote marks, as those are also used for Text delimiters in VBA. If you do not do it correctly, VBA cannot tell if you...
20. ## Thread: Macro ActiveSheet.Formula Object doesn't support this property or method error

by Joe4
Replies
12
Views
66

### Re: Macro ActiveSheet.Formula Object doesn't support this property or method error

You are still missing the range part, as I mentioned in my initial reply.
21. ## Thread: Macro ActiveSheet.Formula Object doesn't support this property or method error

by Joe4
Replies
12
Views
66

### Re: Macro ActiveSheet.Formula Object doesn't support this property or method error

You need to provide the range also. Otherwise, it does not know where on the Active Sheet you want the formula, i.e.

ActiveSheet.Range("A2").Formula =...
22. ## Thread: Shared Excel Database Advice

by Joe4
Replies
2
Views
207

### Re: Shared Excel Database Advice

Personally, I would use Access. If you split the database to have a single central back-end that holds all the data, and front-end that you share with users (each user has their own copy), then...
23. ## Thread: VBA - Format Rows containing word 'Total'

by Joe4
Replies
8
Views
257

### Re: VBA - Format Rows containing word 'Total'

Try this:

If the formatting piece isn't quite perfect (i.e. wrong color), you can turn on the Macro Recorder and record yourself setting the color you want. Then you can copy over the correct...
24. ## Thread: Return Characters After A String of Characters in Excel

by Joe4
Replies
3
Views
85

### Re: Return Characters After A String of Characters in Excel

You are welcome.
Glad I was able to help!
:)
25. ## Thread: VBA Clear Content based on certain value

by Joe4
Replies
3
Views
50

### Re: VBA Clear Content based on certain value

That is because your code is looking for a literal text string of "#Value" and not the "#Value" error.
You can use the ISERROR function to locate errors. See:...
Results 1 to 25 of 150
Page 1 of 6 1