Posts by watters2

    Hello Everyone,


    I have a data dump I would like to put into a pivot table to know the number of days someone worked in a week. However due to the data dump when i put this into the pivot table the numbers are misleading when somone has worked on multiple activities on the same day. This counts as three or two days when really it was only one day. Is there any way to easily clean the data or fix this problem.


    I have copied a sample of the data and pivot table. The real data is extremely long thus I can't clean this manually.


    Thanks in advance.


    Jason

    I have a summary sheet that has a vlookup that pulls back data from another sheet. The formula works very well however when I use my drop down to change the value the vlookup it pulls back data of different length, so every I have to adjust the column width of my summary sheet. Can some one please help me with a marco that adjusts the width of columns. I could use the macro recorder but when I do it sets itself for the length of the current summary and I need something that can fluctuate with the different lengths.


    Thank you in advance.


    Jason

    G'Day!


    I need to return a name to a cell based on row of cells with changing values -- below is the formula I am trying to use but we have too many if statements. Does anyone have an easier way of doing this?


    Thank you in advance.


    Jason



    =if(C11="Cathe", "Cathe", if(D11="Kevin", "Kevin", if(E11="Casey","Casey", if(F11="Derek","Derek",if(G11="Crystal","Crystal",if(H11="Jason", "Jason", if(I11="Jennifer","Jennifer", "False")))))))

    G'Day everyone:


    Sorry I know there are numerous posting on this topic however I have searched them and I am still having problems. I am trying to sumif with two conditions which I understand is not possible so I am using sumproduct.


    I am trying to compute the revenue for several project numbers within a certain month. For example, I have project numbers 171610, 189430, and 192310 I want to find out how much revenue was recongized within the month of April.


    Here are a couple formulas that I have been using:


    =SUMPRODUCT(('Actual Rev'!B2:B7776=I7)*('Actual Rev'!Q2:Q7776='Summary Page'!A8)*('Actual Rev'!Q2:Q7776='Summary Page'!A9)*('Actual Rev'!Q2:Q7776='Summary Page'!A10)*('Actual Rev'!Q2:Q7776='Summary Page'!A11)*('Actual Rev'!Q2:Q7776='Summary Page'!A12)*('Actual Rev'!Q2:Q7776='Summary Page'!A13)*('Actual Rev'!Q2:Q7776='Summary Page'!A14)*('Actual Rev'!Q2:Q7776='Summary Page'!A15)*('Actual Rev'!U2:U7776))


    Where I7 equals the month and Summary Page!A8 equals the project number. This returns a zero.


    If I seperate by Project for example everything seems to work fine. I have also tried to define the project numbers but this returns #N/A.


    What am I doing wrong? I am new to arrays but from the other posts I can get those examples right I just can't get the formulea to work right on my data. Thanks for the help in advance.


    Cheers,
    Jason

    Derk,


    Thanks for the response. Correct, the CPI amount will be independent on when the the 12 months ends and for simplification we can assume CPI will always be the same.


    I have attached a copy of the format. If you have a better format I am still in the early stages of creation.


    There is another details tab that populates the cash flow page. What I want is that this change allowing cash flow 2 to start in month 14 or whatever depending on the if statement and then I don't know if I need another if statement that says CPI starts 12 months after first number etc. Does this help?


    Also with Cash Flow One - this will always be four months but the four months will vary I was wondering if the offset funtion could be used here.


    Thanks again,
    Jason

    Files

    • CashFlows.xls

      (24.58 kB, downloaded 61 times, last: )

    G’Day Everyone:


    I am working on a small model that calculates cash flows, within the model the cash flows are broken up by month and after 12 months the cash flows automatically increased by CPI. The cash flows do not always start at the same month. I would like to us an if statement to instruct the cash flow to start in the appropriate month however my problem is getting the CPI calculation to automatically start 12 months after the cash flows starts.


    (ie cash flow 1 starts in month 6 so on month 18 I would like the cash flow to increase by CPI. And cash flow 2 starts month 3 I would like to start the CPI increase on month 15.) These months will change with every example I put into my model.


    Thank you in advance for everyone’s help. Let me know if I need to explain the problem in greater detail.


    Jason

    Hello Everyone:


    I don’t know if this needs to be done in access or excel…I have three lists of numbers all six digits long. The lists are all various lengths. I would like to compare list one to two and see which numbers are similar in each list. And then I would like to compare list three to one and do the same. And if this is not too complicated I would like to compare list two to three.


    If my description is a little vague here is a little more specific explanation. I have three computer applications that are not integrated however they are using the same six digit code. I want to compare the codes in each and see which codes are similar between all three.


    I know should be an easy way to do this in either access or excel but I can’t think of it. I have attached a copy of the numbers in case that helps. Thank you in advance.


    Cheers,
    Jason

    Hello Everyone,


    I have what should be a simple problem using drop down boxes. I am trying to create a quick sheet to calculate margins on various products. I have one drop down box were you pick the product. And then second drop down were you pick the value added services. Then I have another cell that should calculate the costs of the corresponding product and value-added service. Everything is working except for the cheapest value added service. Which is inserting 0 however is inserting the wrong value. It is inserting the value for another value added service. I am using the vlookup function to reference the drop down boxes. I have the table sort in ascending order. Thank you for your help.


    Regards,
    Jason

    Andy,


    Thank you for your help. The first suggestion worked like a charm.


    As for the second quetion. What I have is one userform that populates several cells. Depending on the department selected it will have around five to ten people. There will be a time, day, and couple other pieces of information that corresponds with that name. Then my second userform allows me to sort by time. Then I want the third userform to allow me to highlight (change font colour) for all people that have the selected day.


    Once again thank you for your help.


    Cheers,
    Jason