Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-04-2011, 06:06 AM
OTPM OTPM is offline Multiple VLOOKUP's checking multiple Cells Windows 7 32bit Multiple VLOOKUP's checking multiple Cells Office 2010 32bit
Expert
Multiple VLOOKUP's checking multiple Cells
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 05-04-2011, 07:00 PM
macropod's Avatar
macropod macropod is offline Multiple VLOOKUP's checking multiple Cells Windows 7 32bit Multiple VLOOKUP's checking multiple Cells Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #3  
Old 05-05-2011, 01:37 AM
OTPM OTPM is offline Multiple VLOOKUP's checking multiple Cells Windows 7 32bit Multiple VLOOKUP's checking multiple Cells Office 2010 32bit
Expert
Multiple VLOOKUP's checking multiple Cells
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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
Attached Files
File Type: xlsx At Risk Milestone Report.xlsx (12.8 KB, 18 views)
Reply With Quote
  #4  
Old 05-05-2011, 08:24 PM
macropod's Avatar
macropod macropod is offline Multiple VLOOKUP's checking multiple Cells Windows 7 32bit Multiple VLOOKUP's checking multiple Cells Office 2007
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
You can then use this function like a formula. It takes three parameters:
• 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]
Reply With Quote
  #5  
Old 05-06-2011, 03:20 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Multiple VLOOKUP's checking multiple Cells Windows 7 32bit Multiple VLOOKUP's checking multiple Cells Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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")
It "looks up" the IDs listed in I11:Z11, finds them in column A and then counts how many of them have a finish date later than today. If any of them do, then it returns "At Risk".

Perhaps I misunderstood though.
Reply With Quote
  #6  
Old 05-09-2011, 04:58 AM
OTPM OTPM is offline Multiple VLOOKUP's checking multiple Cells Windows 7 32bit Multiple VLOOKUP's checking multiple Cells Office 2010 32bit
Expert
Multiple VLOOKUP's checking multiple Cells
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 05-09-2011, 04:58 AM
OTPM OTPM is offline Multiple VLOOKUP's checking multiple Cells Windows 7 32bit Multiple VLOOKUP's checking multiple Cells Office 2010 32bit
Expert
Multiple VLOOKUP's checking multiple Cells
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Quote:
Originally Posted by Colin Legg View Post
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")
It "looks up" the IDs listed in I11:Z11, finds them in column A and then counts how many of them have a finish date later than today. If any of them do, then it returns "At Risk".

Perhaps I misunderstood though.

Hi Colin
Many thanks for this - I will try it out.
Tony
Reply With Quote
  #8  
Old 05-19-2011, 06:27 AM
OTPM OTPM is offline Multiple VLOOKUP's checking multiple Cells Windows 7 32bit Multiple VLOOKUP's checking multiple Cells Office 2010 32bit
Expert
Multiple VLOOKUP's checking multiple Cells
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 05-19-2011, 05:07 PM
macropod's Avatar
macropod macropod is offline Multiple VLOOKUP's checking multiple Cells Windows 7 32bit Multiple VLOOKUP's checking multiple Cells Office 2007
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
The function is now called with four parameters, thus:
=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]
Reply With Quote
  #10  
Old 05-23-2011, 03:21 AM
OTPM OTPM is offline Multiple VLOOKUP's checking multiple Cells Windows 7 32bit Multiple VLOOKUP's checking multiple Cells Office 2010 32bit
Expert
Multiple VLOOKUP's checking multiple Cells
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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
Attached Files
File Type: xlsm Copy Master Milestones At Risk Report Generator.xlsm (442.8 KB, 8 views)
Reply With Quote
  #11  
Old 05-23-2011, 03:45 AM
macropod's Avatar
macropod macropod is offline Multiple VLOOKUP's checking multiple Cells Windows 7 32bit Multiple VLOOKUP's checking multiple Cells Office 2007
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #12  
Old 05-23-2011, 11:18 AM
Catalin.B Catalin.B is offline Multiple VLOOKUP's checking multiple Cells Windows Vista Multiple VLOOKUP's checking multiple Cells Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, România
Posts: 386
Catalin.B is on a distinguished road
Default

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,
Attached Files
File Type: xlsm Copy Master Milestones At Risk Report Generator.xlsm (482.2 KB, 19 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple VLOOKUP's checking multiple Cells 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
Multiple VLOOKUP's checking multiple Cells Moving formula range multiple cells when moving sum over one cell FraserKitchell Excel 4 02-26-2010 10:38 AM
Multiple VLOOKUP's checking multiple Cells Set up multiple accounts or... cabinfever1888 Outlook 2 08-28-2009 07:31 AM
Multiple VLOOKUP's checking multiple Cells How to transfer multiple cells from excel to word by formulas justziggy Word 5 05-23-2009 02:33 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:20 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft