Re: Excel 2007 Calculation Bug
Sorry...
We have a saying that "Sometimes TWO are better than NONE".
Thanks.
Re: Excel 2007 Calculation Bug
Sorry...
We have a saying that "Sometimes TWO are better than NONE".
Thanks.
Hi,
I searched the forum for "bug 850" and while nothing was found I allow myself to present to you a bug found (not by me) in "Excel 2007":
Type the Formula: =77.1 * 850 in cell A1 and you will get 100,000 (instead of 65,535).
In cell A2 type: =A1+1 and you will get: 100,001 (instead of 65,536)
BUT..., if you type, in cell A3, = A1*2, you'll get the correct result: 131,070
I assume that "Microsoft" programmers are already aware of this bug.
Have a nice weekend,
Michael
Edit by Admin
Fix bug in bug report
Re: Calculate Date Difference In Weeks
I assume that is it - or... !?
Thanks a lot for all your efforts.
Re: Calculate Date Difference In Weeks
"Daddy",
with this kind of rhythm you will end with a 3-4 characters formula…
Thanks.
Re: Calculate Date Difference In Weeks
Thanks, Richard,
Yes, indeed, but with one tiny missing left bracket:
=INT((B4-B3)/7)+1+(WEEKDAY(B4)<WEEKDAY(B3))
Michael
Re: Calculate Date Difference In Weeks
Thanks, Dave,
and to close (I hope) the subject, here is an even shorter formula:
=IF(WEEKDAY(B4)-(WEEKDAY(B3))<0,1+INT((B4- B3)/7)+1,INT((B4- B3)/7)+1)
I check it also over years-change dates and it works OK.
Michael
Re: Weeks Intervals Without Weeknum()
No way I'm going to argue and/or discuss about our Army (for better and for worse).
Anyhow, I think I have got it.
Any comments (if any) will be noted.
Thanks again, Michael
Re: Weeks Intervals Without Weeknum()
Yes, as I mentioned before those are military computers under strict regulations.
With the help of WEEKNUM there was no need for my question in the first place.
Michael
Re: Weeks Intervals Without Weeknum()
Thanks, Richard,
I tried to adopt your suggestion and came out with a "long" formula – although I eliminated the use of two roundups and I also succeeded to calculate the weeks while a change of year occurs.
The big question: is there a way to shorten this "huge" formula ?
Michael
Added later:
Although I multiplied by 53 I am aware of the fact that there are 52 weeks in a year.
(Maybe 29 days in Feb. may dissorder it !?)
The multiplication is not consistent - sometimes I need to multiplied by 52 and sometimes by 53 in oreder to get the correct answer.
So, something has to be done about this as well.
Thanks.
Hi,
On some Military computers the Add-ins were not installed, therefore the WEEKNUM function cannot be used.
The task is to calculate the time interval (in whole weeks) between two dates.
(Every start date is to be calculated as a whole week – same for the End date).
I tried but the most but came up with the incorrect formula in the attached workbook.
Any assistance in writing the correct formula (incl. UDF - as long as no add-ins are to be installed) will be appreciated.
After posting - I tried DATEDIF but without success.
Thanks, Michael
Re: Hyperlink Does Not Maintain Path To Target File
Thank you, both.
Re: Hyperlink Does Not Maintain Path To Target File
Thank you.
Hi,
I read about Hyperlinks in the August 2006 "OZGRID" Newsletters but found no answer.
Assume 2 workbooks - book1 and book2, both located in "c:\" (root of drive C)
Now, In book1 I have declared an HL to book2 using CTRL+K and upon clicking in that cell the book2 is opened.
So far so good..
After MOVING(!) book1 to a new location (D:\DATA\) and clicking the HL - I get an Error "cannot open the specified file".
While book1 was still in root C:\ - I tried to force to c:\book2.xls by typing it in the HL address window but after saving, Excel deletes the "c:\" and leaves "book2.xls" only – I think because both files were located I the same directory.
I am aware that my "problem" can be solved by using the function HYPERLINK – but I would like to learn (if possible) how to insert an HL using CTRL+K that will maintain the path although moving the file which contains the HL).
Thanks in advance,
Michael
Re: Group Numbering In Steps
Thank you so much.
Although I found the answer to the first range, I asked about, - which is:
=INT((ROW()-ROW($A$10))/5)*2+2
Your Formula is much more understood.
Michael
Hi,
I am trying to produce a list of numbers (preferred Sheets Function and not VBA).
Every group should be of 5 Identical Numbers - starting with 2 and step of 2.
(An Example is shown in the attached picture).
I would like to produce that list with the help of ROW() and all available build-in Functions.
If possible, I will appreciate very much some explanation as for the principal of producing such a list when, for inatance, the step is 3 , starting from 3, while every group includes only 4 identical numbers and the first cell, to present the first value, is A11 and down to A26 - presented here in Horizontal layout:
(3,3,3,3,6,6,6,6,9,9,9,9,12,12,12,12)
Thanks, Michael
Re: Stop Warning When Deleting Sheets
Thanks, Dave,
That code produced a "Compile error: Expexcted: In"
so I changed the command into:
Now, it works fine.
Michael[hr]*[/hr] Auto Merged Post;[dl]*[/dl]In order to close all loose ends – I added an If check in order to prevent the deletion of ONE sheet.
Its number, in the collection, is Sheet(1)
I managed to do so with the following code but I wonder how can this be done by applying the sheets Number instead of its Name
(which can be very long and/or being changed after a while) !?
For Each SH In ActiveWindow.SelectedSheets
If SH.Name = "ABCDE" Then
MsgBox "Deletion of sheet: " & SH.Name & " - is NOT ALLOWED !"
Exit Sub
Else
SH.Delete
End If
Thanks, Michael
Re: Stop Warning When Deleting Sheets
Thank you, Will,
Using a Non-English interface I had no problem to change the "Delete" into our language term:
(Although I wonder that the following command was acceped without an error)
However, your code works fine upon deleting a single sheet.
If I try to select and delete several sheets, only one is deleted.
I tried to change the
into:
But no success.
what will be the appropriate command to meat the possibility of deleting one, OR several, sheets ?
Thanks again, Michael
Re: Stop Warning When Deleting Sheets
Thanks again but it seems it is not preventing the pop-up window from been showed.
I used the following code > closed and opend the Workbook and upon trying to delete a sheet - the Alert window keeps poping-up.
Private Sub Workbook_Open()
Application.DisplayAlerts = False
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = True
End Sub
Awaiting more help,
Mike[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Above my first post I just noted links to some similar questions.
Will Riley answered (almost 2 years ago) one of them by suggesting (Quote):
"That would involve reprogramming the sheet right click menu to replace the default delete procedure with one like I showed above".
The "above" code, he showed, was:
Sub delete_sht()
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
End Sub
So, can someone explain in details, please, how can I alter the right click menu as suggested.
I have some knowledge using VBA codes but such altering a default menu behavior sounds new to me.
Thanks, Mike
Re: Stop Warning When Deleting Sheets
Thanks, Dave,
If I understood you correct this is not what I meant.
Your suggestion is "valid" as long as the Macro runs and upon Deleting by code.
I like to allow the user to delete sheet(s) by right-clicking(!) the sheet(s) tab(s) and choosing delete - without any alert window caused by the deletion.
Mike
Hi,
Is there a way (via Menues and or VBA) to cancel the pop-up of the Alert Window when the user deletes a sheet(s) ?
(I mean sheets that contain Data)
Thanks, Mike