#1
|
|||
|
|||
Multiple VLOOKUP's checking multiple Cells
Hi
I have a question I need some help with. I want to create a formula that will check multiple cells against multiple Finish Dates in a spreadsheet. The formula I have to check the first cell is: =IF(VLOOKUP(I11,A:F,6,FALSE)<TODAY(),"At Risk","On Time") and this works fine but I would also like to check columns J to Z in the same formula. I am trying to avoid using VBA so that it can be used in the future by users who are not familiar with VBA. Any help would be appreciated. OTPM |
#2
|
||||
|
||||
Hi OTPM,
Given there are two or more ranges, in which either might have a true/false result, you need to clarify what happens if one is true and the other false. The following approach works if either if true: =IF(OR(VLOOKUP(I11,A:F,6,FALSE)<TODAY(),VLOOKUP(I1 1,J:O,6,FALSE)<TODAY()),"At Risk","On Time") To require both to be true, change the OR to AND Also, I'm not sure how you plan to extend the range all the way to column Z. I've assumed you're using the same offsets from J as for A, but maybe that's not so.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Hi Paul
Many thanks for the prompt response. However this does not appear to gie me what I need. What I am trying to do is provide an indication as to whether a Task/Milestone is at risk if dependant tasks are running late without having to give management the full project plan. I attach my sample spreadsheet and in H14 I have put your formula and got the result#N/A. Basically in any given row I may have dependencies from column I through to Z. I want to check if any of those dependent tasks are running late and therefore makes the Milestone at risk before it actually happens. Hope this makes it more clear. any help would be appreciated. Tony |
#4
|
||||
|
||||
Hi OTPM,
Add the following function to a code module attached to your workbook: Code:
Option Explicit Function RiskTest(Rng1 As Range, Rng2 As Range, Rng3 As Range) As String Dim i As Long, j As Long RiskTest = "On Time" With Rng1 For i = .Column To .EntireRow.End(xlToRight).Column For j = 1 To .Row - 1 If .Offset(j - .Row, 1 - .Column).Value = .Offset(0, i - .Column).Value Then If .Offset(j - .Row, Rng3.Column - .Column).Value < Rng2.Value Then RiskTest = "At Risk" Exit Function End If End If Next Next End With End Function • the address of the first predecessor to test • the address of the cell holding the completion date against which to test the predecessors; and • the column holding the predecessor completion dates. So, if you want to test the the set of predecessors starting in I11 against a completion date in F13 and the predecessor completion dates are in column F, you'd used the formula: =RiskTest(I11,F$13,F:F)
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
||||
|
||||
I * think * this native formula does what you want:
Code:
=IF(SUMPRODUCT(($A$2:$A$11=$I11:$Z11)*($F$2:$F$11<TODAY())),"At Risk","On Time") Perhaps I misunderstood though. |
#6
|
|||
|
|||
Hi Macropod
This works perfectly on my test spreadsheet. I will now test it on a full plan. Many thanks for your help. Kind regards Tony |
#7
|
|||
|
|||
Quote:
Hi Colin Many thanks for this - I will try it out. Tony |
#8
|
|||
|
|||
Hi Macropod
Youir Function works fine until there is no Predecessor number. IN this case I need the Function to check if the Finish Date is less than Today. Your help would be appreciated. Tony |
#9
|
||||
|
||||
Hi OTPM,
Try: Code:
Function RiskTest(Rng1 As Range, Rng2 As Range, Rng3 As Range, Rng4 As Range) As String Dim LastRow As Long, LastCol As Long, i As Long, j As Long RiskTest = "On Time" LastRow = Rng1.Row LastCol = Rng1.EntireRow.Columns(Rng1.EntireRow.Columns.Count).End(xlToLeft).Column If Rng1.Value = 0 Then If Rng3.Rows(LastRow).Value < Rng4.Value Then RiskTest = "At Risk" End If Exit Function End If For i = Rng1.Column To LastCol For j = 1 To LastRow If Rng1.Columns(i).Value = Rng2.Rows(j).Value Then If Rng3.Rows(j).Value < Rng4.Value Then RiskTest = "At Risk" Exit Function End If End If Next Next End Function =RiskTest($I$11,$A:$A,$F:$F,$F$13) (the $ constraints are optional), where: • the 1st parameter is the address of the first predecessor to test • the 2nd parameter is the column holding the project IDs • the 3rd parameter is the column holding the project completion dates • the 4th parameter is the address of the cell holding the completion date against which to test the predecessors.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#10
|
|||
|
|||
Hi Paul
Many thanks for the updated Function. However it does not appear to work. I enclose a sample copy of my report and have put a comment in cell I2 which describes what should happen. I have had a go at modifying the Function and will continue to do so, however I am struggling a little as your knowledge is far superior to mine. Your help is really appreciated. Tony |
#11
|
||||
|
||||
Hi Tony,
The function isn't designed to work quite the way you're using it. You appear to have each project's predecessor completion dates being evaluated against the project's completion date. The way the code is written, if no predecessors are specified, the project is assumed to be its own predecessor. In all cases, the code tests against a date that's been input into another cell. If you use the project's completion date for this, then that's what the completion will be tested against. You can make it more flexible in this regard by having a 'default' date in another cell and adding an IF test to the expression. For example: =RiskTest(J2,A:A,F:F,IF(J2="",G$35,G2)) where the 'default' date (today) is in G35. If you do that, I believe you'll get the results you're after. Plus, you can specify other dates if/when the need arises. I note that on row 9, you have a value in column K but not in column J. Be careful with that: if there is nothing in column J, column K will not be tested.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
|||
|
|||
Hi Tony, can you check this version of your report sample, maybe you can use it this way. Check the comment in cell I25.
Hope it helps, |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
filling multiple cells whislt using Filter | VinceO | Excel | 1 | 05-09-2011 06:38 AM |
Multiple task lists and multiple calendars | kballing | Outlook | 0 | 01-18-2011 10:23 AM |
Moving formula range multiple cells when moving sum over one cell | FraserKitchell | Excel | 4 | 02-26-2010 10:38 AM |
Set up multiple accounts or... | cabinfever1888 | Outlook | 2 | 08-28-2009 07:31 AM |
How to transfer multiple cells from excel to word by formulas | justziggy | Word | 5 | 05-23-2009 02:33 AM |