Search results

Sorry those are two alternative methods: (1) sumproduct (2) array formula I did that with all my examples. Sorry if I wasn't clear. Both work the same and juts as well. For the array formula, don't type the curly braces on the outside of the formula. But do hit Ctrl-Shift-Enter instead...

Gotcha. I was listing alternatives to the VBA code already linked. i.e. Workbook/worksheet protection or network security (which is the most common way to restrict access). If you want or need the workbook to have it's own login system, then I have nothing to add to the previously linked VBA...

If having a single password for everyone to use is acceptable, without separate logins, check the following: http://office.microsoft.com/en-us/excel-help/secure-a-workbook-with-a-password-HP001112410.aspx Excel does not have built-in login capability. Typically file access per user is...

If there is no special reason to store the data in Excel, you shouldn't. Use another data store for speed and reliability. Access is OK. Microsoft recommends using SQL Server for general purpose use (there is a very lightweight desktop version available for free). Various open source...

Asa

In my first comment with what I believed were corrected general purpose "OR" calculations: The version for ranges of criteria is correct, but the long version with criteria specified individually is incorrect. The - signs between tests need to be doubled, or else just eliminate all - signs...

Phew! Updated examples again. Version 3 of examples has another update to "Asa's Formula #2". You can ignore most of my previous comments. I believe I have resolved any speed issues. This formula for No./Qtrs is shorter and simpler than my previous versions, and it should be about the...

I just updated the examples file (same link). The only change was to the No/Qtrs column in the "Asa's Example #2" to increase the efficiency/speed a little. That is the version of the formula that can handle situations where characters other than comma, space, and 1 2 3 4 were included in...

Sweet! You don't need to link to the helper column. It serves no purpose in the summaries. It is only useful as an interim step in the initial calculation of the split Q1/Q2/Q3/Q4 amounts. Will give you more thoughts next time, busy at the moment. Could you upload your file or files...

If you want the executions to start in 10 seconds from now and be spaced by about 10 seconds, just multiply your 10 seconds times X: For X = 1 to 5 Application.OnTime Now + TimeSerial(0, 0, 10 * X), "RefreshData" Next X

Finally, if you want to stick with a boolean flag, change the MsgBox line to: bFlag = MsgBox("Have you filled in the CAP-X in the Buyer's?", vbQuestion + vbYesNo, "Confirm") = vbYes and change the If line to: If bFlag Then

Change Dim line to: Dim bFlag As Long should resolve it, at first look. Alternatively, you could do without the flag variable entirely if you want, by combining the MsgBox call and the If command (although after the End If you won't be able to refer to the user's choice again): If...

OK, Here we go. The AND test for criteria in sequential columns. Either of: =SUMPRODUCT(--($A$2:$A$10=$J22:$M22))=4 {=SUM(--($A$2:$A$10=$J22:$M22))=4} (Array formula in braces) Note the =4 in there - it needs to = the number of criteria (J, K, L, M columns = 4). You could replace the =4...

To mix and and or, you can mix the techniques.. oops, edited I better try it before I give a combo formula.. my suggestion didn't look right :)

Actually, Luke, aren't these both: =SUMPRODUCT(--(A2:A10=Criteria1),--(A2:A10=Criteria2),--(A2:A10=Criteria3),--(A2:A10=Criteria4)) =SUMPRODUCT((A2:A10=Criteria1)*(A2:A10=Criteria2)*(A2:A10=Criteria3)*(A2:A10=Criteria4)) AND truth tests? If any one of them fails, (=0) the whole product will be...

From Microsoft: Here's the article: Guildines and Examples of array formulas. It goes into some depth about array formulas. In short, an array means multiple values. A simple range A1:A3 is an array. An array formula can do operations on multiple arrays without aggregating (SUM, AVERAGE...

See if this helps clarify. Quarter Reporting Split Amounts Example.xlsx You talk about having the helper column linking to the field reports... this is some kind of misunderstanding. The "helper column" is only needed at the detail level, not the summary level, based on my understanding of...

Hi Guity, You can also use an in-cell formula. =SUBSTITUTE(A1,CHAR(13),"") Will do the same for cell A1. Excel appears to remove the line break if you use either CHAR(13) (carriage return) or CHAR(10) (line feed). If you want a space instead of a line break, you can use...

Unfortunately, I think it's those 3 days worth of formula-entry that need to be replaced. We should be able to work out a way for you to get the formulas entered more quickly than that. Perhaps a macro. Yeah, I hate it when I start seeing formulas in my sleep!! There are some missing...

Aaah -- I didn't see the quarter columns with more than one quarter. If you want to split the amount evenly between quarters, that's what jeremymjp was trying to help you with. I assume it's the field report detail sheets that have quarters listed this way for some line items, right? Not on...

Hi bfraser, I can't find the pivot table in the workbook. In the data table in the first worksheet I see where you used a circular reference formula in column E. It evaluates to the "date" 1/0/1900, which is internally represented in Excel as the date serial number 0. I was able to filter...

Hi, Regarding using a variable with MsgBox-- MsgBox is a function, and functions are designed to return information when you call (use) them. The MsgBox function actually returns a number (a LONG, aka long integer). If you don't specify any buttons for the message box other than the default...

Thank you John! I was impressed with the well-written chapter from MrExcel, too, and it certainly is the kind of practical approach that really is what Excel is all about. Excel excels at the "custom solution" and that chapter not just solves this problem but teaches some great...

MyValue = ws.Range("A1") '???? The above assigns to MyValue the Value in cell A1. It actually refers to a Range object, but Value is the default property of a range. It only works for one cell. .Cells(i, "A") = MyValue The Above line assigns MyValue to the Value property...

I am not experienced with manipulating pivot tables in code, but you can find your answers by using debugging techniques like breakpoints, stepping through your code a line at a time, and monitoring the properties of the relevant object using the Watch window or using Debug.Print commands in the...