Posts by BC...

    Re: Merge dynamic ranges for Pivot Table


    Suppose you have to have source data on 2 sheets. ie, you are being forced to use a pivot table to report on 70,000+ records and the level of detail of the source data cannot be summarized. How do you merge 2 tables with the same columns, on separate sheets, allowing for the creation of pivot tables on other worksheets?

    Re: Network links with spaces


    Quote from norie

    How are you inserting this link?


    Just typing as listed. Then Word, or other Microsoft apps 'recognize' it as a link, but stop where the space is between the word New and Files.

    Hopefully this is an easy one, but I haven't found an answer to it.


    When you enter a the path of a file in a Word doc, which is located on a network and you enter something like...


    \\NetworkServer\Folder\New Files\newfile.doc


    Microsoft breaks the link at the space between New and Files. How can you enter this so it recognizes and preserves the link?

    Re: macro to F2 on a cell and enter


    Another approach is to do a find and replace for contents of the cells. For the numbers treated as text problem, where you want the numbers treated as numbers, you can record a macro to find the numbers 0-9 and replace them with themselves. This will fire the edit cell, event.

    Re: Creating a Folder, Exist?


    The sample posted earlier works fine, unless you have multiple levels of folders to create, in which case you need to loop thru each level to check for existence and create if needed.


    It would look something like this.


    Re: Text to Number Syntax


    I wrote a script that will find/replace the numbers 0-9 on whatever my current selection is. This, like the *1 approach, initiates an Excel event that updates the value to be recognized as a number.

    Re: Automte report generation


    I've adjusted the summary table and the organization of the source data to allow you to plug in the value for the current week and have the previous 3 weeks calculate and sum the source data for your graph.


    I did mess up the graph, so you'll have to fix that. Also, I didn't use any named ranges for the SUMIFs that I used in the summary table. So keep in mind this is a rough revision that could and should be refined to meet your needs.


    Hope this helps.

    Re: Stop Vlookup Returning #N/A!


    Quote from Seti

    Try:


    IsFormula and =ISNA(cell in question)=TRUE and then set the font color to white



    Thanks, I didn't have the =TRUE part of the formula

    Re: Stop Vlookup Returning #N/A!



    Dave in the following thread, you mention using conditional formatting to hide the #N/A result.


    http://www.ozgrid.com/forum/showthread.php?t=27083


    Could you post an example of the condition setting?


    Thanks

    Re: sumif error using dynamic ranges


    Quote from tinyjack

    PortfolioDlrs is setup wromg, it starts at 2 and not 3.


    TJ


    Sorry, that was due to cutting this file down so I could attach it. I did end up removing a row at the top that moved the named ranges from starting on row 2 to row 3.

    Re: sumif error using dynamic ranges


    Quote from tinyjack

    It makes no difference if they are 'dynamic' or just plain named ranges, the formula should work as written if the ranges are refering to the right cells.


    Could you attach the file?


    TJ


    Here is the file. I put the formula examples next to the appropriate cells, three examples all looking for the first value in the SDD column.


    Thanks for the help

    Re: sumif error using dynamic ranges



    Yes your are right. The problem with using this volatile function is I have alot of similar ranges and can't afford the processing overhead.


    So it shouldn't matter how I name the ranges it should work but it doesn't.

    Re: sumif error using dynamic ranges


    Quote from shades

    How do you have them named? Are they the same length?


    Not sure what you mean by your 1st question, but they both ranges start and end on the same row #, they just refer to different columns.

    Re: sumif error using dynamic ranges


    Quote from tinyjack

    Are you sure PortfolioSDD and PortfolioDlrs are refering to the sheet Portfolio?


    TJ


    Yes they do refer to the Portfolio sheet. I press F5 and enter the named range to select it and verify this.

    I am using a SUMIF function that utilizes dynamic named ranges for the lookup and sum arguments and I am not getting the correct result.


    Example
    =SUMIF(PortfolioSDD,$B10,PortfolioDlrs) is returning 0


    =SUMIF(Portfolio!A:A,B10,Portfolio!H:H) is returning 300,000 (which is correct)


    the PortfolioSDD range is A3:A289 and the PortfolioDlrs range is H3:H289


    What could be causing this issue?