Announcement

Collapse
No announcement yet.

Sum of dynamic column & row

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Sum of dynamic column & row



    I have sheet where i have to find sum of given dynamic range every week and paste the answer in Last or Defined column

    eg

    Week1: on passing Name/Value of column = 7/8/19, it identifies that Sum of A to B is needed in column Sum(Z) (For all rows)

    7/1/19 7/8/19 7/15/19 Sum
    1 2 3
    2 3 5
    Week2: on passing Name/value of column = 7/15/19, it identifies that Sum of A to C is needed in column Sum(For all rows)
    7/1/19 7/8/19 7/15/19 Sum
    1 2 2 5
    2 3 1 6

  • #2
    Hello,

    Much more explicit (and easier....) than trying to describe your sheet ...

    Attach a sample file ...
    If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

    Comment


    • #3
      Thank for response, please find the sample sheet attached, appreciate the help
      Attached Files

      Comment


      • #4
        Thanks for the test file ...

        Attached is a proposal ...

        Hope this will help
        Attached Files
        If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

        Comment


        • #5
          Sorry i can't see the solution here, is it because file is .xlsx not .xlsm? Kindly let me know if i am missing something. ?

          Comment


          • #6
            my Bad, i see the solution its a formula, sorry i should have mentioned i am looking for solution in VBA... i apologize for missing this major point.

            Comment


            • #7
              Re,

              Let's recap :

              1. Are the fornulas producing the expected results ?

              2. For a macro to operate, will the dates always be located in cells C1 to Q1 ?

              3. Given any number of rows, will Column A always be populated with a resource ?

              4.Will the time span ( ranging from C1 to Q1 ) always include Today's date ?
              If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

              Comment


              • #8
                Re,

                Should you answer Yes to all the questions ....

                Attached is your test file with the macro ...

                Hope this will help
                Attached Files
                If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                Comment


                • #9

                  Once you have tested the macro ... feel free to share your comments ...
                  If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                  Comment


                  • #10
                    Thank you!!!!!! so much for your help, i am 95%done.. thanks for throwing some light on offset, evaluate functions which i never knew before.

                    Let me respond to your re-cap questions,

                    1.Are the formulas producing the expected results ? Yes Formulas gave expected results

                    2.For a macro to operate, will the dates always be located in cells C1 to Q1 ? No my actual range is Column N to BL, Actual in BM, Forecast in BN

                    3.Given any number of rows, will Column A always be populated with a resource ? Yes

                    4.Will the time span (ranging from C1 to Q1 ) always include Today's date ? - I want to give my date , so i modified Clng(Date) to CLng(myDate) but it is calculating date upto previous week not current week and similarly for Forecast it is including current week but should start from future, eg 7/25 is in week (I was able to make it work by changing the offset range values but need some conceptual clarification, asking my question below)


                    Question
                    I am a beginner so asking some more questions on Offset (in sheet attached) i spent couple of hrs and to understand offset and before asking question

                    Please share intention of picking -3 and 14 in end of Offset in Evaluate statement w.r.t address (though i understood formula perspective), please can you explain this part (attaching the sheet as well, for your input, i asked same question in VBA sheet), so that when range varies i can modify myself as well

                    Appreciate your help.



                    Attached Files

                    Comment


                    • #11
                      You are welcome ...

                      If you feel like digging into the Offset function ...

                      Below is a link with a good explanation :

                      https://www.myonlinetraininghub.com/...tion-explained

                      Hope this will help
                      If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                      Comment


                      • #12
                        Yay! i understood the intention.
                        Thank you!! Thank you!! so much for pointing to the right link... its amazing , i ll read about addresses as well in detail!

                        Comment


                        • #13
                          Hi, Sorry i have 1 more question, my original sheet has Dates in General format, its NOT in Date Format and i am not allowed to convert first row to Dates, and i tried so many options where i can match it as string to get the right value of X but i keep getting x = 2 whatever string i pass to match, please can you share your inputs where i am going wrong here


                          x = Application.Match("mydate", Sheet1.Range("A1:BL1"), 1) -- this is the modification i tried on original shared syntax by declaring my date as string mydate = CStr("06/30/19"), it always bring values of x = 2, no matter which date i provide from Range.

                          Another things which i tried were getting Rid of double quotes on myDate, or passing direct values in application match but i keep getting type mismatch error

                          Kindly share your inputs ( i will keep trying as well, if i find something i ll post here, thanks)

                          Comment


                          • #14
                            Kindly help!

                            This is how its working, so when i give "06/30/19" (06/30/19 is defined General in sheet) it converts that to 06/20/2019, if it try to give without quotes, excel generate Run time error 6 for overflow
                            then i used another variable usedate to format

                            mydate = "06/30/19"
                            usedate = Format(mydate, "mm/dd/yy") --> Value of Date on watcher shows "06/30/19" (With quotes)



                            lrow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
                            Set rng = Sheet1.Range("A2:A" & lrow)
                            x = Application.Match("usedate", Sheet1.Range("A1:BL1"), 1) --> it does match here, make x=2 not the expected value 39 , it always remains 2 no matter what i pass , this is where i got stuck - Kindly help

                            Note: when i do the formula in excel =MATCH("06/30/19", "A1:BL1", 1) , i get value as 39 but not in VBA


                            If the Cells were defined as dates it works flawlessely with any range

                            Comment


                            • #15


                              Resolved the error using x = Application.Match(CStr(usedate), Sheet1.Range("A1:BL1"), 1)
                              By Leaving the variable declaration as Variant

                              Comment

                              Working...
                              X