The Excel Questions Thread

Discussion in 'General Chat' started by CunningSmeagol, May 4, 2009.

  1. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    Glad to help. Mr G, can you please elaborate on your question? I don't really understand what you need to happen, because as I said, what I think you want doesn't seem all that desirable to me.
     


  2. Neo1824

    Neo1824 Senior member

    Messages:
    633
    Likes Received:
    0
    Joined:
    Nov 23, 2006
    Location:
    Scottsdale, AZ
    The simple macro I wrote in my last post gives you the path of the file, which will vary based on who is running the macro and what letter their share drive is mapped to. So, if you're coworker retrieves the file from their X:\\ drive and you retrieve it from your W:\\ drive the macro will return "X:\\filename.xls" if your coworker runs it and "W:\\filename.xls" if you run it.

    Alternatively, would referencing the ActiveWorkbook work?

    Code:

    With ActiveWorkbook
    .BreakLink "Link_Name", xlLinkTypeExcelLinks
    End With

    This worked! Short and sweet.
    Thanks!
     


  3. haganah

    haganah Senior member

    Messages:
    6,351
    Likes Received:
    27
    Joined:
    Nov 24, 2007
    Location:
    New York, NY
    I'm tired today and can't think.

    1) If you're plotting ROI figures over a year, would you use a linear trendline or logarithmic or one of the other options?

    2) And is there any way to show the beginning value for the line and the end value?
     


  4. FLMountainMan

    FLMountainMan White Hispanic

    Messages:
    13,935
    Likes Received:
    2,086
    Joined:
    Aug 18, 2006
    Location:
    McAnally Flats
    How do you autofill a formula that pulls from multiple tabs? Like I want to add all the values in Cell A3 from 50 tabs and then all the values from cell B3 from 50 tabs and the all the values from Cell B3 from 50 tabs, and so on.

    It won't let me just drag down the box and autofill.
     


  5. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    How do you autofill a formula that pulls from multiple tabs? Like I want to add all the values in Cell A3 from 50 tabs and then all the values from cell B3 from 50 tabs and the all the values from Cell B3 from 50 tabs, and so on.

    It won't let me just drag down the box and autofill.


    Type it like this:

    =SUM(Sheet1:Sheet50!A3)

    EDIT - Assuming the sheets are consecutive

    You should be able to drag down. You just can't use that formula in cell A3 on Sheet 1 b/c then it would be referencing itself.

    Are you sure there are no "$"s in the formula you are trying to use?
     


  6. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    I'm tired today and can't think.

    1) If you're plotting ROI figures over a year, would you use a linear trendline or logarithmic or one of the other options?

    2) And is there any way to show the beginning value for the line and the end value?


    1 - I am not a banker. I have no idea.

    2 - Not sure what you mean, but I would think you could just make another series based on the trendline, make it invisible, and use a label from one of the first points.
     


  7. FLMountainMan

    FLMountainMan White Hispanic

    Messages:
    13,935
    Likes Received:
    2,086
    Joined:
    Aug 18, 2006
    Location:
    McAnally Flats
    Type it like this:

    =SUM(Sheet1:Sheet50!A3)

    EDIT - Assuming the sheets are consecutive

    You should be able to drag down. You just can't use that formula in cell A3 on Sheet 1 b/c then it would be referencing itself.

    Are you sure there are no "$"s in the formula you are trying to use?


    There are $s....let me guess, that's a basic no-no?
     


  8. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    There are $s....let me guess, that's a basic no-no?

    It's a yes/no. They hold either the letter (column) or number (row) in place when you drag. They are especially useful if you're dragging right and then dragging the whole thing down or vice versa.

    Also, if you reference a cell in a formula, try hitting F4 a few times after clicking the cell. It will toggle between the permutations of dollar signs.
     


  9. dhc905

    dhc905 Senior member

    Messages:
    1,033
    Likes Received:
    2
    Joined:
    Apr 16, 2007
    Location:
    San Francisco
    How can I check to see if a number is within an array? I.e. I'm trying to place quarterly payments by saying if the counter of the number of quarters that has gone by = a selection of quarters where a payment should be made, return me 1 (or the payment or whatever).

    I've tried if(match(counter,list of quarters where expenses should be paid),1,0) and If(or(counter=list of quarters),1,0). The last one gives me the right answer if I highlight the equation and hit F9, but VALUE's out when I'm not hard calculating it. WTF.

    Help!
     


  10. snuffy

    snuffy Active Member

    Messages:
    37
    Likes Received:
    0
    Joined:
    Nov 7, 2007
    This will be an easy answer. (Need to improve Excel skills).

    I have 6 worksheets with only two columns each. Column A has a listing of names, Column B has a listing of percentages corresponding to the names.

    I need to add up the listing of Names(A) with the percentages across the 6 worksheets. Essentially consolidating into one. Keep in mind some of the same names appear in the 6 sheets, with different percentages.

    Any suggestions?

    Thanks
     


  11. dsgNYC

    dsgNYC Senior member

    Messages:
    422
    Likes Received:
    0
    Joined:
    Dec 9, 2008
    This will be an easy answer. (Need to improve Excel skills).

    I have 6 worksheets with only two columns each. Column A has a listing of names, Column B has a listing of percentages corresponding to the names.

    I need to add up the listing of Names(A) with the percentages across the 6 worksheets. Essentially consolidating into one. Keep in mind some of the same names appear in the 6 sheets, with different percentages.

    Any suggestions?

    Thanks


    I'm not quite sure I understand what you're trying to accomplish. Are you trying to get a final list of only the unique names from column A and the sum of all of their corresponding percentages from column B across all 6 worksheets?
     


  12. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    How can I check to see if a number is within an array? I.e. I'm trying to place quarterly payments by saying if the counter of the number of quarters that has gone by = a selection of quarters where a payment should be made, return me 1 (or the payment or whatever).

    I've tried if(match(counter,list of quarters where expenses should be paid),1,0) and If(or(counter=list of quarters),1,0). The last one gives me the right answer if I highlight the equation and hit F9, but VALUE's out when I'm not hard calculating it. WTF.

    Help!


    I would need an example spreadsheet. If you zip one up and attach it, I might be able to help. I am trying but can't make sense of the question. Also PM me if you do so I don't miss it.
     


  13. snuffy

    snuffy Active Member

    Messages:
    37
    Likes Received:
    0
    Joined:
    Nov 7, 2007
    I'm not quite sure I understand what you're trying to accomplish. Are you trying to get a final list of only the unique names from column A and the sum of all of their corresponding percentages from column B across all 6 worksheets?

    Yes. The final summary is list of unique names from A and the sum of the corresponding percentages. Any tips/excel functions I can look into?

    Thanks
     


  14. superego

    superego Senior member

    Messages:
    1,736
    Likes Received:
    423
    Joined:
    Sep 15, 2009
    Location:
    Seattle
    I'm tired today and can't think.

    1) If you're plotting ROI figures over a year, would you use a linear trendline or logarithmic or one of the other options?

    2) And is there any way to show the beginning value for the line and the end value?


    1. Depends on what you're trying to depict. In this case, I can't think of a good reason why you'd use a log-scale to plot ROI over a given year.

    2. Like, a number over the first data point, none for those in the middle, and a number over the last data point? Whenever I've needed to do that, I've simply inserted the labels and then deleted those I didn't want. Alternatively, you could simply insert a text box into your plot showing the values you wish to display and move them to wear you want them.
     


  15. dsgNYC

    dsgNYC Senior member

    Messages:
    422
    Likes Received:
    0
    Joined:
    Dec 9, 2008
    Yes. The final summary is list of unique names from A and the sum of the corresponding percentages. Any tips/excel functions I can look into?

    Thanks


    I don't know of any formula that'll automate the process of getting a list of unique values across multiple worksheets. VBA code could definitely automate this. Your issue is very simple to deal with in a database, but will require more complexity to complete in Excel.
     


Share This Page

Styleforum is proudly sponsored by