You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Pull values from a range

Advertisement

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.

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

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

Answers by Expert:

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.

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.