Re: Return Matrix Results Based On Two User-entered Selections
Assuming your dropdowns are in cells G1 and H1, this formula will do the trick:
[bfn]=INDEX($A$1:$D$4,MATCH($G$1,$A$1:$A$4,0),MATCH($H$1,$A$1:$D$1,0))[/bfn]
Re: Return Matrix Results Based On Two User-entered Selections
Assuming your dropdowns are in cells G1 and H1, this formula will do the trick:
[bfn]=INDEX($A$1:$D$4,MATCH($G$1,$A$1:$A$4,0),MATCH($H$1,$A$1:$D$1,0))[/bfn]
Re: 2003 Code Stops In 2007
No, it is my understanding that any code that uses Application.FileSearch will not work in Excel 2007. I don't use Excel 2007, so this doesn't concern me, and I therefore have no solution for you. However, there are plenty of people who do use Excel 2007 and I'm sure that they have figured out ways to get around this. If you search the forum for "FileSearch in Excel 2007", you should be able to find said work-arounds.
Re: 2003 Code Stops In 2007
Yes.
Re: 2003 Code Stops In 2007
Application.FileSearch is not supported in Excel 2007. A search for "FileSearch in Excel 2007" should give you some work-arounds.
Re: Count Unique Entries Within Variable Date Range
Your DCount formula in G10 yields 29 (which is the number of unique entries in your list). Why do you have a note stating that the result should be 11? I don't understand. If you change your criteria to look at 1/7/2008 only, your formula gives 13, which also seems to be correct, i.e. there are 28 entries, but only 13 of them are unique. What am I missing here?
EDIT: Ok, now I see what I'm missing. The formula works, but only when 1/7/08 is the start date. Let me look at this at little more.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]My solution is rather ugly so hopefully someone else will come along with something better. Until then, this method will work. Based on the data in your sheet 1, I made the following assumptions:
Create a range named "rng" [bfn]=OFFSET($A$1,MATCH($G$1,$A:$A,0)-1,4,MATCH($H$1,$A:$A,1)-MATCH($G$1,$A:$A,0)+1,1)[/bfn]
Then use this formula to get your unique count:
[bfn]=SUMPRODUCT((rng<>"")/COUNTIF(rng,rng&""))[/bfn]
Re: Iterative Calculation
Quote from amainHello, I need to perform an iterative calculation that will continue until the value is below a certain number.
Only way I know is with UDF. Something like:
Re: Selecting Multiple Ranges Meeting One Condition
If your data is all in one row, what exactly do you mean by "bottom right"? Do you just want to select the 3 cells to the left of the blank cell?
Re: Formula To Count Number Of Characters In Each Cell
Did you look at any of the Possible Answers above?
Re: Re-Enable VBA Code Protection
Quote from Techhie_Timturtle44: The code does not have a digital signature. Would that affect anything in this case?
It shouldn't, but I have had problems before when someone using Excel 2000 worked with files that I had saved in Excel 2003. The problem only occurred, however, when I had my code digitally signed. Once I removed digital signature, everything was fine. My code is always protected, so I don't understand why you are having a problem with compatibility.
Re: Roundup If Value Great Than X.xx5
You're welcome. For the record, you may want to change your title so that it is more descriptive of your actual question, perhaps something like "VLookup Based on Rounded Values."
Re: Roundup If Value Great Than X.xx5
[bfn]=IF(ISERROR(VLOOKUP(ROUND(E4,2),tier_upsell,3,TRUE)),"",VLOOKUP(ROUND(E4,2),tier_upsell,3,TRUE))[/bfn]
Re: Re-Enable VBA Code Protection
Does your code have a Digital Signature?
Re: Loop Through Cell Comments
Andy, I think I like SpecialCells better than my method, but you would need to test that comments existed or your code will cause error. Something like this perhaps:
Sub LoopSelectedCellComments()
Dim rngTemp As Range
Dim rngComment As Range
Dim strBuf As String
On Error Resume Next
Set rngComment = Range("B2:D10").SpecialCells(xlCellTypeComments)
On Error GoTo 0
If rngComment Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If
For Each rngTemp In rngComment
strBuf = strBuf & rngTemp.Comment.Text
Next
Range("A1") = strBuf
End Sub
Display More
Re: Roundup If Value Great Than X.xx5
If your lookup table is in A1:B3 and is sorted in ascending order, and the value you are looking up is in A6, then this formula should work:
[bfn]=VLOOKUP(ROUND(A6,2),$A$1:$B$3,2,TRUE)[/bfn]
Re: Two listboxes item select simultaneously and paste on sheet
Yes. As long as ListBox1 has its MultiSelect property set.
Re: Add Incrementing Letter To The End Of Each Cell In A Highlighted Range
Here is a slightly longer solution that allows for more than 26 cells.
Sub AddLetters()
Dim i As Long
Dim x As Long
Dim y As Long
Dim sTemp As String
Dim Cell As Range
i = 0
For Each Cell In Selection
i = i + 1
x = ((i - 1) Mod 26) + 1
y = (i - 1) \ 26
If y < 1 Then
sTemp = Chr(96 + x)
Else
sTemp = Chr(96 + y) & Chr(96 + x)
End If
Cell.Value = Cell.Value & sTemp
Next Cell
End Sub
Display More