You are here:

Excel/Pull values from a range

Advertisement


Question
My 'actual' input range is AC38:BH38 which contains either of a value or "".
There is also a row $AC$58:$BH$58 which contains either of "Y" or "".  This row $AC$58:$BH$58 does not contain any -ve values although it may contains "Y" or "".

If the cell/(s) in $AC$58:$BH$58 contains "Y", it certifies that:
1. the corresponding cell/(s) in the above row#38 contains values & not "".
2. from the corresponding cell/(s) only of the input range AC38:BH38; the answer (as required below) has to be pulled.
While pulling the answer, the formula should consider 2 conditions:
Formula should consider those cells of the input range AC38:BH38 ONLY:
1. where corresponding cell/(s) in the row#58 contains "Y"
2. the cell value of the input range AC38:BH38 must be>0

Answer required:
1. Value of the 'Last cell' (Right hand side) of the input range AC38:BH38 meeting the above 2 conditions.
2. Value of the 1 cell LEFT to the above cell PROVIDED it also meets the above 2 conditions.
3. Value of the 2 cell LEFT to the above cell(answer #1) PROVIDED it also meets the above 2 conditions.
4. Value of the 3 cell LEFT to the above cell(answer #1) PROVIDED it also meets the above 2 conditions.

Answer#2="" if only 1 cell of the input range AC38:BH38 meets the above 2 conditions.
Answer#3="" if only 2 cells of the input range AC38:BH38 meets the above 2 conditions.
Answer#4="" if only 3 cells of the input range AC38:BH38 meets the above 2 conditions.

Answer
Hi Kumar,

I apologize for my slow response.  When I got back from vacation I was overwhelmed by the backlog of questions in my input queue.

Since you mentioned you wanted a function (rather than a macro) and it must return 4 values, I wrote a user-defined function to accomplish this. I wasn't sure how general you wanted it, so I made it so it would work on input ranges in any row, but the two ranges had to include the same range of columns, as in your example.  I called the function PullValues (see the code below) and you simply give it the range where the values are, and a row number where the second range containing the "Y" or "" values are.  Since it is an array function you must enter it in four cells in a row using Ctrl-Shift-Enter, and it will return the four answers in those four cells.  The value will be blank if the conditions are not met for that particular answer.  So for your problem you would call it like this:

=PullValues(AC38:BH38,58)

and Excel will surround the function with curly braces because it is entered as an array function.

Here is the code:
____________________________________________________________________

Function PullValues(R1 As Range, Row2 As Long) As Variant

'If the cell/(s) in Row2 (corresponding to cells in R1) contains "Y", it certifies that:
' 1. the corresponding cell/(s) in the above row#38 contains values & not "".
' 2. from the corresponding cell/(s) only of the input range R1; the answer (as required below) has to be pulled.
'While pulling the answer, the formula should consider 2 conditions:
'Formula should consider those cells of the input range R1 ONLY:
' 1. where corresponding cell/(s) in Row2 (columns same as R1) contains "Y"
' 2. the cell value of the input range R1 must be > 0

'Answer:
' 1. Value of the 'Last cell' (Right hand side) of the input range R1 meeting the above 2 conditions.
' 2. Value of the 1 cell LEFT to the above cell PROVIDED it also meets the above 2 conditions.
' 3. Value of the 2 cell LEFT to the above cell(answer #1) PROVIDED it also meets the above 2 conditions.
' 4. Value of the 3 cell LEFT to the above cell(answer #1) PROVIDED it also meets the above 2 conditions.

'Answer#2="" if only 1 cell of the input range R1 meets the above 2 conditions.
'Answer#3="" if only 2 cells of the input range R1 meets the above 2 conditions.
'Answer#4="" if only 3 cells of the input range R1 meets the above 2 conditions.

  Dim Ans(0 To 3)   As Variant
  Dim LastCell      As Range    'the "right side" of R1 meeting the 2 conditions
  Dim i          As Integer  'the relative column index within R1
  
  'initialize the 4 answers to indicate "not found"
  Ans(0) = ""
  Ans(1) = ""
  Ans(2) = ""
  Ans(3) = ""
  
  For i = R1.Cells.Count To 1 Step -1
     If ConditionsMet(R1(i), Row2) Then
        'found first answer
        Ans(0) = R1(i).Value
        'check cell to left of R1(i)
        If i > 1 Then
         If ConditionsMet(R1(i - 1), Row2) Then
         Ans(1) = R1(i - 1).Value
         'etc.
         If i > 2 Then
         If ConditionsMet(R1(i - 2), Row2) Then
         Ans(2) = R1(i - 2).Value
         If i > 3 Then
         If ConditionsMet(R1(i - 3), Row2) Then
         Ans(3) = R1(i - 3).Value
         GoTo Done
         End If
         Else
         GoTo Done
         End If
         Else
         GoTo Done
         End If
         Else
         GoTo Done
         End If
         Else
         GoTo Done
         End If
        Else
         GoTo Done
        End If
     End If
  Next i

Done:
  PullValues = Ans

End Function

Function ConditionsMet(C1 As Range, Row2 As Long) As Boolean
  'ConditionsMet = True if cell C1 contains value > 0 and not blank AND
  '          cell corresponding to C1 column in Row is "Y"
  
  ConditionsMet = C1 > 0 And C1.Parent.Cells(Row2, C1.Column) = "Y"
         
End Function
__________________________________________________________________________

I believe it does exactly what you want, but if I have misunderstood any of your requirements please feel free to follow up.

Damon

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.