Good morning. Have tried asking this several times with no success.
I have written VBA code with several index/matches
.
pasterOH = "=IFERROR(IF(RC[-3]=""Subtotal"","""",IF(RC[-4]="""",INDEX(" & stapler & OH1 & "'!" & "C" & (Month(Worksheets("SEL Onsite OH").Cells(6, 6).Value) + 6) & ",MATCH(RC[-3], " & stapler & OH1 & "'!C3,FALSE),1),INDEX(" & stapler & OH1 & "'!" & "C" & (Month(Worksheets("SEL Onsite OH").Cells(6, 6).Value) + 14) & ",MATCH(RC[-4], " & stapler & OH1 & "'!C2,FALSE),1))),""0"")"
The code works fine to look up a cell in the column that I have specified and return a dollar value if it is a match.
Here is my issue: I need to be able to sum all of the dollar values to the left of the match. For example, in the picture I posted a simple picture of a table with index/match
. In the formula the target is column E:E, whereby "tom' returns "5". What I am trying to do is if excel can find and match "tom" in column 5, it would sum B2,C3,D4,E5. I could write this if I knew what column "tom" would be in every time. I need to have a VBA solution that goes along with the pasterOH
from above that will allow me to sum the values of column 7 (January) picked by my (Month(Worksheets("SEL Onsite OH").Cells(6, 6).Value) + 6)
through whatever column the month happens to be.
For example, In May, the column I am looking up and returning a match to is column 11. I would like the code to start with the matching entry (our "tom" in this case), and sum the results of Jan- May (columns 7-11) and return that as the answer instead of tom's result only in May.
Hope that is clear. I posted a couple of times and found this answer, but don't know if it will apply or how to edit my VBA formula.
Thanks for any assistance