Search results

Hi Alex, "Multiple If scenarios" are usually handled using a single If/Then/[ElseIf...]/[Else]/End If block, or a Select statement, as I have used below. Here's a version that avoids the appearance of the selection changing, and prints using the built-in printer dialog (familiar; can select...

You can execute your print code specifically when a hyperlink is clicked by using the FollowHyperlink event in VBA. This code in the worksheet that has the hyperlinks should work, I think, if you leave your hyperlinks such that they automatically select the report ranges: Private Sub...

To grab a number from one cell at a time on the report you are linking to, using the INDEX and MATCH functions together is an appealing solution. It supports wildcards (* and ?). Definitely the fact if I understand you correctly, that the identifying, key info is not exactly the same poses...

1/ In the Visual Basic Editor, make sure the above code is in the "ThisWorkbook" section. (Double-click "ThisWorkbook" in the project pane). 2/ Make sure the password listed in the code is the one you want. 3/ Change "Sheet1" in the code to the name of the worksheet you want to protect and...

Hi again Nitin, I updated my mockup formulas to be significantly faster. I tested with about 13,000 rows and the formulas I suggested yesterday, and it took roughly 10-20 seconds to do a full recalculation of the spreadsheet. Today's formulas--roughly 0.25 seconds ! Please download my...

Hi Perdelta and Jeremy, Nice solution, but {MIN({0,0,0,Start4,0,0,Start7,0})} = 0 For First login and Last login, I think you need to exclude the 0's from the array. The following will do that with an IF() test. Boolean values are ignored by most aggregate functions, and if you don't...

Luke, your generosity and great solutions really help make this community. Congrats.

Hi Nitin, OK, so, if I follow you (plus my assumptions)-- Total Premium = employee premium + premium for each of his/her dependents. All premiums are listed individually for each insured person covered. Company Amount (of Premium) to Bear = employee premium + one husband/wife premium +...

Yay :) If you find that the wildcard match is not sufficient (it really is just confirming the placement of a decimal point), you could resort to use MID() to specifically check that certain parts of the text meet certain criteria.

Did you try my suggestion of using SEARCH to handle wildcards? the wildcard capability of MATCH isn't really relevant to your formula.

As to your PasteSpecial problem, that's interesting behavior you're seeing. It works fine for me. I tested two blank columns on the right side of my copy selection, and used the line of code you indicated, and the paste definitely did copy the blank cells, as it overwrote other data I had in...

Hi Eli, Also, formulas, as long as they still result in the values you want, pose no problem. When you save that worksheet as a CSV file, all formulas are converted to values during save. Excel does not save formulas to CSV files. Asa

There are several solutions, and your problem is a bit advanced. Without modifying the structure of your data a little, the formulas to solve your problem I think will be pretty slow. So, I recommend some structural changes to your data. Two alternatives come to mind: (1) Normalize your...

Sue, SEARCH, by the way, throws an #NA! error if it doesn't find what you search for. To check that it DIDN'T find something, as you are trying to do, I think, check for #NA!. e.g. =ISNA(SEARCH("????.???",INDEX(where,MATCH(what,where,0)))) gives you TRUE if that matched cell...

Alternatively, maybe you can solve it yourself -- If your formula "practically works"... You can't check that "value of matched cell" <> "?????.???" and expect wildcard processing. the ?'s aren't in the first parameter to a MATCH function, they are part of a boolean...

Ok. Can you write your requirement out, exactly, in words, naming cells and columns instead of what type of information they contain? That would make it easier to give you a formula that will do what you want. when you say ".000" do you literally mean zeroes? or is that code for 3 numeric...

Hello, Sue! I can tell you one problem---you've got your wildcards in a position that they won't be handled. Also, just guessing, but if LEFT($G8,8) has to do with a desire to restrict your criteria to the beginning of the cell matched, no matter what or where you put LEFT(xxx,8), it won't...

Great! http://www.ozgrid.com/Excel/outlining-protected.htm has a solution for you.

One high-tech solution that comes to mind is a VBA macro that runs when opening the workbook and checks all the links. It could maintain information on the code names for the worksheets, which stay the same through renames, retrieve the current name of the sheet, and update the links...

Hi Guity, glad to help! Don't be afraid to try it :) Yes, substitute handles multiple instances. If you want make the change permanent, Copy the new calculated column, and paste it back with Paste Special/Values to remove the formulas. It might be possible to use search and replace to...

Hi Chuck, I'm glad you got things worked out nicely! A couple other useful properties of ranges that come to mind, in VBA: range.UsedRange --- = the used part of the range (the part of the range that falls within the last used row and column of the whole worksheet; blanks within that area...

Bringing it together: the working formulas I've proposed so far... Testing a range of data (e.g. $A$2:$A$10) for matching a range of criteria ($F22:$I22) -------------------------------------------------------- OR test: =SUMPRODUCT(--($A$2:$A$10=$F22:$I22))>0 AND test: {...

Corrected AND test for a vertical range of data and a horizontal range of criteria. I did not find a good SUMPRODUCT solution, only an array formula solution. All formulas mentioned in this post need Ctrl-Shift-Enter when editing/entering the formula: --- This will result in TRUE if every...

For a detailed explanation of how this works, check Chandoo's article "Advanced Sumproduct Queries". Particularly read the "Scenario 4: Sum values within a 2D Range matching multiple ordered criteria" section. Explained are what SUMPRODUCT does, and what adding and multiplying do. There is...

-- converts true to 1 and false to 0 (forces conversion to a number) in certain calculations, OR() and AND() cannot be used, so one has to use arithmetic operations to achieve the same logic. multiplication, then test for =1 : AND LOGIC addition, then test for >0 : OR LOGIC addition...