Posts by Billyrj


    Thank you as i have been working this for so long it became a quest. It works perfect and cant thank you enough. I unfortunately have some gaps in my working with formulas and could visualize working SUMPRODUCT. More work to on my end and again very thankful for for your effort.

    I have come full circle on this project. My original Formula was a sumifs but was not the correct direction. I am attempting to use Index Match formula and not getting the desired results.

    My desire is to Look in column "A" and find a match to K3

    Second look in Column "B" and find Match to K4

    Provide the value of Column "C"

    with my data in the work sheet my result should be 36 and i am getting a value error.

    My current formula is

    =INDEX(C2:C10, MATCH(1,(K3=$A$2:$A$10) * (K4=$B$2:$B$10),0))

    I would love to find the values in C:i and transpose accordingly but am trying baby steps and trying to find value in "C" for now.


    I have attempted to utilize a Vlookup but still struggling with returning the correct value.

    In K11 I have a formula that is looking in Column "A" for "DOG" and then in Column "B" for "G" then return the value in column "C".

    My formula: =VLOOKUP(K3, IF(B2:B19="G", A2:I19, ""), 3,FALSE )

    I did reference cell K3 as it has 'DOG" but just doesn't seem to work. I attached and updated book. I originally wanted to copy and paste Column C:E but just could not figure that out. Thanks

    Okay I have been playing with this for a little bit and am struggling with how to resolve.

    On Sheet "Test2" I have aray of data and in cell AK8 my formula "=SUMIFS(Test1!C8,Test1!A8,=SUMIFS(Test1!C8,Test1!A8,$AJ$7,Test1!B8,"G"),Test1!B8,"G")" its was pointed out that i was using a sumifs to return 1 cell. I need to look in "$AJ$7" as a reference to find in column A on sheet "TEST1" find "CAT" then if then match G and copy columns C:I to TEST2.

    I am essentially matching two columns and parsing data. And cannot figure out a better approach with the data i am using. I have tried to use Vlookup and have really struggled.

    Thanks in


    I do not have a good answer for that. At the time I was working this project I was experimenting with sumifs. This formula worked for many years until input data was rightly changed. when it did i had to fix an onerous amount of formulas to get workbook back up and running. It does ask for the sumifs of one cell which which returns the year. In this case 2021 Column. My desire would be to not have modify code if a row is deleted on test1 sheet. Thanks Billyrj

    I have a rather large workbook and have been using a SUMIFFS formula that is starting to become a little cumbersome and i need to modify to alleviate constant corrections.

    My example I posted has 3 sheets.

    sheet "TEST1" has a pivot table of data pulled from my server. The problem is some lines will be removed over time.

    Sheet "TEST2" and "TEST3" Has data pulled from TEST1

    The problem i am having is I have written the formula to look in a specific row and Cell so when a row is deleted I have to modify to formula to look in the correct cell. The funny part is probably has worked for several years until recently but i do realize there is a better way just not found it yet.

    my formula is below:

    =SUMIFS(Test1!C8,Test1!A8,$AJ$7,Test1!B8,"G") I tried to keep make sure i referenced the specific item in AJ7.

    I think i should transition this formula to probably a Vlookup but open to any suggestions

    Thanks and apologize i left off my attempt on using a countifs off. The data I am looking at is on sheet2 and tally a cross section of the following.

    Sheet2 column "A" and "B" but then Year will be chosen by using Sheet3 Data Validation "B1" the references sheet2

    I have attempted to you use countifs but failed to properly reference "B1" Which is why i thought VLOOKUP might be the solution

    I cannot figure out how to reference the data validation cell in SHeet3 "B1". My formula stops at the 3rd criteria as it faults out.

    My results should reference year 2023. My struggle is the 3rd reference utilizing the sheet3 B1 and the ability to change that and get the proper results when the change the date.

    Thanks again

    Working on possible countif statement but got confused when i added third variable.

    1) on sheet3 I want to look on sheet2 for Column A:A for "DOG"

    2) then look in column B:B for "A"

    3) I then look in Sheet3 "B2" which corresponds to Sheet2 C1to I1

    In my example i used 2023 as this number changes every year so i was trying to do a Dropdown with a Vlookup or just a count if but since i will change the range on sheet3 B2 i was going in loop with no success. Any help would be a bonus.


    I have to admit i may have made this complicated but i honestly do not see my error.

    I have a Cell Validation that selects an name on sheet TEST1 "F6". Works FIne i get the list of names from sheet TEST2 column "F".

    The formula in Cell validation "F6" is =OFFSET(test2!$F$2,0,0,COUNTA(test2!$F$1:$AF$10)).

    My desire is to get The numerical value using =VLOOKUP(F6,test2!A2:F10,1,0) looking in test2.

    I have and 2 examples and the AS IS works by Cell Validation and looking for the numerical value and adding name but i now have a need to work project in opposite fashion.

    Always appreciative for help


    royUK i figured it out. I had to modify my text in my datevalue formula and now i just reference a cell that i use for this report. Thanks again

    Changed to 

    In this case its a time between two dates. Every year i have to change "9/1/2022" to the following year so i was desiring to ref a cell that is is changed so i do not have to do so on multiple pages.


    written out looks like ***This is with I2 written out***   
    =(DATEVALUE("9/1/2022")-(2/1/1996)/365 which equals 26.2

    I am working a project that every year i have to change a formula and would desire automate. I beleive i am using date value as intended but desire to ref a cell so i can change on 40+ sheets all at once and not separately. I would ref U2


    I did and seems it should work but not sure why. Sorry i did not copy correct code. The only issue is that it does not exclude Run Zones work sheet and it is correctly spelled.

    If oWs.Name <> "Run Zones" Then oWs.Range("P2:P10000").Interior.Color = xlNone

    royuk thanks i ran the second code and got an error compile error and it highlights ".Count" portion of the code with the following message

    Compile error:

    for each may only iterate over a collection object or an array

    Sub ClearFill()
    Dim oWs As Worksheet
    For Each oWs In ThisWorkbook.Worksheets.Count
    If oWs.Name <> "enter sheet name here" Then oWs.Range("P2:P10000").Interior.Color = xlNone
    Next oWs
    End Sub