Posts by sm789

    Hello,


    I work with monthy data. I have to prepare summary based on SUMIFS and COUNTIFS. My SUMIFS and COUNTIFS have named ranges. life SUMIFS(DepDec18,CarrierDec18,$B10,DestDec18,E$9). Each month has different number of rows. Right now on each monthly workbook, I name the ranges like DepDec18 for the data then change formulae to refer to the new named range,


    I guess this is confusing. Basically what I am asking is that in the formula SUMIFS(DepDec18,AirlineDec18,$B10,DestDec18,E$9), is it possible to refer to a cell - lets say b6 which has range name that I want to use as Text. So I write the formula SUMIFS(B6,B7,$B10,B8,E$9) where B6 has text DepDec18, B7 has CarrierDec18 and B8 has DestDec18 which are all range names.


    Thank you

    Re: Formula based on remainder value


    Sir,


    I have no idea how it works but it truly works and does what I want it to do!


    Thank you so much!



    Quote from Carim;781428

    To get rid of all the E - K Columns ...Take a look at the Adjusted Sheet ... :wink:


    HTH

    Re: Formula based on remainder value


    Thank you Carim.


    I would like to create a formula that has reference only to Column C. The reason is that this is a small excerpt of a large sheet and I need to run what if scenarios based on multiple variables. The resulting Sq Ft needs to be represented in the local format as well.


    It will not be possible to create columns E thru K in my what if scenarios.


    Any other suggestion?


    This is my first time and I am new to VBA so please bear with me.


    I have a worksheet with 1500 records. I already have a code which asks me to input a search string, goes through each row of the workshet and if Column C matches the search string, copies the entire row to a second sheet on the row next to the last row. The macro also counts the number of instances found so at the end of the search, we know that there were for example 28 records matching the string i.e. 28 new rows in the second sheet. The new set of records (28 rows in this case) are separated from the previous records by a gap of two rows.


    I have also written code to name the new 28 cells in F column to lets say MyRange. Then the macro goes to the 29th cell in F row and needs to put in a formula to sum the above 28 rows. I can't use xlUp because there may be blank cells in between.


    I had originally used [code]ActiveCell.FormulaR1C1 = "=sum(MyRange)"[code] but at the end of the macro I need to delete name reference so that the code can run next time for a different search string without any problem.


    My problem is how do I convert MyRange to the actual cell reference from what we know which is:
    (i) The cells to be summed are always in F column.
    (ii) The Active Cell is already one row below the range to be summed.
    (iii) The number of rows is stored in a variable called Counter


    Basically what I need is Sum(R-[Counter]C[0]:R-[1]C[0])


    I know this must be very simple for you veterans but I am stumped. Thank you for your help.