Posts by Retailmessiah

    Hi Carim,


    I think you're still thinking that we need to summarize existing data from the purple data by migrating it to the green area in a more simple form than we are trying to achieve. I would like to keep the data on the same sheet if possible too- I don't expect to expand this sheet beyond 2025.


    In your latest example file, it seems that you're showing for TX, that January and February 2020 are both 1000 which is not correct. Since in January we are only counting Client A (because they started 01/01/2020), but in February we are counting Client B as well. They are both in their first year until January of 2021 where Client A rate increases to their Year 2 price. So Jan 2021 would be Client A Year 2 Rate + Client B Year 1 Rate. February 2021 Client B ages to Year 2, so Feb 2021 would be Client A Year 2 Rate + Client B Year 2 Rate.


    I added client names so we can discuss. Is that anymore clear?

    Oops, sorry to be unclear. I realize you couldn't determine which areas of the spreadsheet I needed populated. In your first reply you said to, "Feel free to attach a sample file, with say 20 rows showing both your input ... and your expected result ...", and I did that, I just didn't tell you which one is which.


    Since your sample file you you sent back you removed part of the needed dataset, I'm reattaching the mockup I did with some color. We're trying to derive the monthly totals by state. The Output in this case is the green shaded areas. I typed in expected values here to illustrate expected output only. The input is obtained from the blue shaded area showing the client start month, as well as their monthly cost in years 1-5. Looking for a dynamic formula that can take into account which month and year (blue output) we're calculating, and sum up all the totals for each client, taking into account if that month is in year 1, year 2, etc.


    Does that help to clarify it? I wish it was as easy as having an output tab that summarized the data!

    Feel free to attach a sample file, with say 20 rows showing both your input ... and your expected result ...:)

    Hi Carim,


    Thanks for the feedback. I've mocked up a sheet, hopefully it's clear what I'm trying to do. I want totals by state taking into account annual increases.


    Thank you!

    Hello OzGrid team,


    Thanks for being here. This one has my head spinning and I wonder if you can help me get closer to what I need. I've attached 2 screen shots showing almost all of the data set. I realized E23:E40 is obscured, but it's 2 character State Codes.


    We're trying to calculate the projected monthly revenue for each month and year in these tables. At first I starting going down the =SUMPRODUCT((E23:E40="TX")+(... and then I realized, I don't know how to determine the next steps. H23:H30 contains the anniversary date (date the fee can increase), and we list the fee by year 1, 2, 3, 4, 5 (3-5 are the same) in J22:J40, L22:L40, N22:N40, etc.


    So if client A has been there for 9 months, and client B has been there for 1 year and 2 months, we want to count each of their monthly amounts in the 1 year and 2 year columns respectively.


    Has anyone combined data in this manner before? I want the total of all of amounts in row 22:40, but I need it to grab the year 1 amount if it's <366 days from H23:H40.. Is using IFs inside of a sumproduct a thing? I think it's not, there's a syntax for if/or but I can't figure it out.


    Is SUMPRODUCT the wrong way to go?


    Thanks for any guidance you can provide.

    Re: Merge &amp; Sort Dynamic Lists


    Quote

    Dont use CODE tags on FORMULAS.



    Use copy/paste to merge the 2 lists and sort then combined list.


    Thanks for the quick reply Dave. I apologize, I read the rules over and over, and I labeled formulas as code. It was a silly mistake, and I'm sorry.


    While your solution will work, it's hardly dynamic. The idea is that I have is to have the novice user update the list of names (which will be something more like 10 lists of hundreds of names) and still have a merged data box to select from. I've used a very simple iteration of what I need this for to reduce complexity. I thought it would be inappropriate to include an example with several thousand pieces of data. Please let me know if I can provide some more detail on this, or if there are questions that you have.


    I had previously posted a second question about sorting this combined data list, but apparently that violated a rule as well. If you do have a way to successfully and dynamically merge the lists, as well as alphabetize the merged drop down, that would solve all of my issues.


    Thanks for your help,
    John

    First off, let me say thanks to the Ozgrid community that has helped me over and over through these forums. You are phenomenal.


    Question 1: I need to create a BOTH box that will dynamically pull names from both the "Chicago Office" & "Seattle Office" columns in my sheet. It is extremely preferable to do this without VB Script if possible to avoid the security warnings on opening the sheet. (Our IT department will not budge on this..)


    Detail:
    I have two lists of employees. Column A lists the Chicago Office employees. Column B lists the Seattle Office employees. I've been able to successfully define named ranges to work with these as dynamic lists. I can append names, or delete names, and the Chicago, or Seattle boxes (drop downs created with data validation formulas inside named ranges) will reflect the updated names correctly. I have been unable to make a single drop down with all the names from both offices, that is updated dynamically. I've attached a sheet so that this is easier to understand. Basically I need the "BOTH" drop down to actually work.


    The Chicago range is defined as:
    =OFFSET(Sheet1!$A$2:$A$11,0,0,COUNTA(Sheet1!$A$2:$A$11),1)
    The Seattle range is defined as:
    =OFFSET(Sheet1!$B$2:$B$11,0,0,COUNTA(Sheet1!$B$2:$B$11),1)


    In the boxes on the right of the lists, I just have a Data Validation List formula as:
    =Chicago and the other as =Seattle


    Notes: I've noticed that if there are spaces in the list then it does not display correctly. I've wondered if there is different way to write the formula to make it take into account spaces in the middle of the list, to make it more user friendly.




    Thanks for any ideas you have, or any help you can provide. If the attached sheet doesn't work for you, please access it here.


    Thanks,
    John