Replies

Much better idea. I’ll do that. Thanks.

Hi Hans,
Sorry if I was unclear. They never show the path on the original PC, and always show it on the other PCs that have the different install location for the add-in. Very Strange.

Thanks, Hans. On the computer where the files are created, the formulas are typed in correctly, the results are correct, and the paths are not displayed. But when such a file is then sent to another user with the other install path, the path from the initial machine shows up before each add-in function in a formula, and the functions result in errors.
I tried your suggestion – deleting the paths then does allow the formulas to work. So that’s great, at least that’s something for us to work with. But I just want to be sure: according to the book, this sort of thing shouldn’t be happening, right?
Thanks again.

Thanks so much, Hans. That’s perfect.

Great. Thanks a lot.

Thanks for the reply Hans. I just got back into work now. I’ll check this when I see the user later today.
Best,
JDB

HI Legare,
The problems were:
a) some macros in Personal.xls weren’t being found when I ran them.
a couple of crashes
c) This morning, I sent a new workbook to someone, and suddenly she got one of my personal toolbars on her screen, although of course the macros it runs weren’t present.
(We’ve got a heavily protected environment. I ran a full scan anyway and came up with no viruses etc.)
This last problem made me suspect that my xlb file was corrupt, so I deleted it. I also moved the code from thisworkbook in Personal per Jan. These two actions seem to have cleared up the problems.
Thanks.

Thanks I never thought about the permanent nature of thisworkbook in that way. I’ll move the code elsewhere and call it. Never used application.ontime before, so I’ll take a look at that.
Regards

Gotcha – I see now that I didn’t read your 2nd reply closely enough. Glad that it may be helpful though.
Regards,

Hi,
II think you can use Data Validation to do what you want, if I’ve understood it correctly. In the attached I put the data in row 5 starting in column A, then selected the data cells to the right of A in that row and entered the data validation formula
=NOT(OR(AND(B5>MIN($A5:A5),MAX($A5:A5)>0),B5<0))
The range expands to check all cells to the left. The MAX part is to make sure that there is a numerical entry to the left.
This does not, however, look to the right at all, to see if a new entry will make entries to the right of it invalid (because they are higher than the new entry.) You could Validate against that, or use a similar formula with conditional formatting just to flag those cells when they become invalid.

Regards,

Thanks very much. Looks like hpagebreaks(x).location.address will do the trick.

Thanks, Hans, for both explanations. I think I will use your idea – create one sheet with the settings and then copy it.
Regards

In the attached file, I’ve put one column with Exit Dates, another with the calculation for the nearest +60, 90 or 120 days date in the next quarter, and another in case that day is a weekend day. If this is not the sort of solution your looking for, can you attach a sample workbook, showing how you’ve laid out the data and what you’ld like to see?

Okay, I think I see what you want.
If the exit date is in D20, you can use:

=IF(ROUNDUP(MONTH(D20)/3,0)ROUNDUP(MONTH(D20+60)/3,0),D20+60,IF(ROUNDUP(MONTH(D20)/3,0)ROUNDUP(MONTH(D20+90)/3,0),D20+90,D20+120))

To show the nearest of the +60, +90 or +120 dates that is in the next quarter.

If you have a date in D20, you can use the formula

=ROUNDUP(MONTH(D20)/3,0)

to get the quarter of that date.

So if there’s an exit date in D20, in the +60 date cell, you could do something like

=if(ROUNDUP(MONTH(D20)/3,0)=ROUNDUP(MONTH(D20+60)/3,0),”Same Quarter”,D20+60)

and a similar thing for the 90 day cell.