Posts by Eternal Truth

  • Join Text With Formatted Number

    I had a hard time to put a suitable title to this query, not sure if above is good enough for my query.


    I am entering combination of text and number in a cell using a formula. I am wondering if it is possible to add formula which can format number such that it is displayed in accounting format.


    Example:
    Expected output = Trial 1,000


    Using this formula
    ="Trial "&Sheet1!A1
    where A1 in Sheet1 = 1000
    Output is Trial 1000


    Please help!


    Thanks in advance :)

  • Plot Chart With Dynamic Range

    Re: Plot Chart With Dynamic Range


    Andy,


    Thanks a lot, this works perfect for me and would save lot of time going forward. Earlier my focus wasn't on "averaging" hence I didn't ask about it.


    Now that you have provided a suggestion, which seems to save some more time for me, let me tell you precisely what I do with average.


    I have sales data for 12 weeks, I have to substract average of latest 4 weeks from previous 4 weeks (trend). Now when I add data for 13th week, the trend should automatically update itself.


    I have attached excel file to show exact scenario. Please let me know if you have a solution for this.

  • Plot Chart With Dynamic Range

    Hi,


    I maintain data which gets updated every week. I have a chart associated with this data which I want to get updated automatically. I tried to use dynamic range on the chart and it kinda works. But the issue with my data is that I have data set in a row, followed by a blank cell which is then followed by average of last two weeks. Hence, when I use a dynamic range, it also displays the last value in the row (average), which I don't want to display in my chart.


    So what I am looking forward to do is set up dynamic range or any other solution, which automatically updates my chart with weekly sales data without showing the average value in the chart.


    I have attached sample worksheet here.


    Please let me know if you know a solution.


    Thanks in advance :)

  • Pull Values Between Two Periods

    Re: Pull Values Between Two Periods


    It surely works, thanks a bunch![hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I tried the delimitor option and it pulled out the values as I wanted. However, when I was copy pasting values from external source (which I have to do often), the way value gets pasted in excel is screwed up form. I rather use formula to get the values. The formula given above works absolutely fine for middle value. Is there a way to pull the text before fullstop (.) and after it, from the following:


    oakland.california.usa


    Expected Outcome
    A B C
    1 oakland california usa


    Now I already know how to get value in cell B1 using formula, but don't know formula for getting values in A1 & C1. Please help.


    I am not sure if I should have created new thread.


    Thanks!

  • Pull Values Between Two Periods

    I have a text value which consists of two periods ".". I want to pull the value in the middle. eg
    From the text value in a cell "oakland.california.usa", I want to pull "california" in another cell. All the values in column are in similar format.


    A B
    1 oakland.california.usa california


    Hope this is not duplication of any pre-existing thread, if it is, I probably don't know the correct keywords to query.


    Thanks in advance for your help!

  • Summary Sum By Week

    Re: Lookup Based On Column & Row Criteria


    Thanks for your reply. This seems to be a solution but I haven't got chance to implement it yet. If I run into any troubles, I would buzz you again.


    Thanks again!

  • Summary Sum By Week

    Re: Lookup Based On Column & Row Criteria


    All, thanks for your replies sofar. Sorry that I could not explain my issue properly. So let me give a second shot now.


    I have attached a file which shows the source data and output table. There is no calculation involved, it is just a data pull. Also, I cann't use pivot table here as I have 30 similar worksheets in one file with different source data.

  • Summary Sum By Week

    I have a huge data set, lets say
    Wk1 Wk2 ...... WkN
    Area1 Type1 Pdt1 Val1
    Area2 Type2 Pdt2 Val2
    Area3 Type1 Pdt3 Val3
    .... ... ...
    .... ... ...
    AreaN Type1 PdtN


    I have to prepare a subset for this table, lets say for Area1, for Weeks 2 to 5 and Products 2 & 3. I need a formula that can pull in the values by looking at area, type, product and weeks.


    I believe match and index can possibly solve this. However I just cann't set it right. If you can help me with a formula that looks up for a value satisfying multiple criteria, that would be great.


    Thanks in advance,


    ET