Posts by Billyrj

Thanks this works and have been struggling on properly using tables getting better but never learned early on. My biggest issue is i will need to feed the range or manually change to provide best results. Thanks and really appreciate the assist.

Thanks works great. But I have a couple of questions.

1) why does M11 say 1/25/1925? I cant make sense out of that.

last question is probably more on the line with how to properly align a spreadsheet. I will have to change range ='Run Zones'!U8-AVERAGE(I5:I10)

I5:I10 range will change quite a bit for data calculations. Approx 10K rows. I am unsure of a good dashboard setup. Any ideas will be helpful.

again thanks and works perfect

Have been working a spread sheet and have ran into a snag. I calculate the time between two dates and come up with years and months.

My problem now is I need to look at we will say 5 and figure out the average. The formula I used to calculate time between dates is below. How to I calculate the average between multiple dates on test sheet starting at T2. I was trying to create a calculation box but cannot figure a means of doing so. I am open to any ideas

Thanks

=DATEDIF(I2,'Run Zones'!$U$8,"y")&" Years "
&DATEDIF(I2,'Run Zones'!$U$8,"YM") &" Months"

Pecoflyer,

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.

MR,

Appreciate the review but honestly do not know how to do what you saying but will research.

Thanks

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.

Thanks

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

royUK,

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.

Billyr

Carim Thanks for the assist. Not sure why Vlookup did not the opposite direction and again appreciate the help.

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

Billyrj

thanks

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

=(DATEVALUE("9/1/2022")-I2)/365

Changed to 

=($V$1-I2)/365

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.

Example:

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

Attached is an example it also clears on the page i do not desire. Thanks

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

=(DATEVALUE("9/1/2022")-I2)/365

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