Posts by dangelor

    Thanks, xlite, but no.

    What I want is what happens when you go to File>Open or Ctrl+O. A window pops up and you click on the file you want to open.

    It's probably really simple, but I can't seem to figure it out.


    I've been using the following code to open a file from my floppy drive. Unfortunately, the file is now too large to fit on the disk.

    Sub ImportFile()

    ChDir "A:\"
    Workbooks.OpenText Filename:="A:\ALARMDET.TXT", Origin:=xlWindows, _
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(10 _
    , 1), Array(20, 1), Array(30, 1), Array(40, 1), Array(50, 1), Array(60, 1), Array(70, 1), _
    Array(95, 1), Array(100, 1), Array(103, 1), Array(107, 1), Array(110, 1), Array(119, 1), _
    Array(124, 1), Array(135, 1), Array(140, 1))
    End Sub

    Can someone show me how to modify the code to allow me to select the file from my hard drive?


    Hi Sal

    Select the DATE range.
    When the range is highlighted, go to Conditional Formatting.

    In condition 1, select "Formula Is" and enter


    without the quotation marks. Then select the formatting changes you want.

    Click on Add>> and in condition 2, select "Formula Is" and enter


    without the quotation marks. Again, select the formatting you want.

    Condition 2 assumes there are no other values in the row you are totaling.

    Hope it helps.

    You might try changing the total frames cell to show an error message if it sums higher than 50. Something like...

    =IF(SUM(D2:D7)>50,"Error! "&SUM(D2:D7)&" total frames.",SUM(D2:D7))

    ...inserting the correct range in place of D2:D7.

    You could also use a variation of the formula in Conditional Formatting to highlight the totals cell a different color when it's over 50.

    For limiting each frame to 10 frames, setting up the data validation to - whole numbers equal to or less than 10 - should work fine.


    Hi Dave,

    Highlight the cell in Sheet 2 that you wish to display the value from Sheet 1.

    Press + to initiate a formula.

    Navigate to and highlight the cell in Sheet 1 containing the value you wish to display on Sheet 2.

    Press Enter.

    In the cell on Sheet 2 you should now see a formula similar to this:


    where "Sheet1" is the name of the worksheet containing the desired value and "A1" trhe location of the cell containing the desired value.

    Hope that helps.


    Gentlemen, thank you all.

    I was doing it properly, but didn't realize that the dates in a pivot table are text. I had to expand my formula to compensate.

    Would anyone care to see if it can be improved upon?


    ReportBase = dynamic range of pivot table



    I'm having trouble setting the range using the pivot table

    Basically what I want to accomplish is this:

    The pivot table's first column is all dates. The tables second, third, and fourth columns contain the information I want to retrieve.

    When I substitute the dynamic range formula for "Pivot Table", the cell returns a NAME error.

    Assuming my pivot table is located in a worksheet labeled "ProductionQ" and the data's left, top corner is cell A7, can you walk me through the dynamic range formula written in cells on a different worksheet?

    ie VLOOKUP(A2,******,2,FALSE) where ****** is the Offset formula.



    Thanks, Andy, for the info.

    The XY Chart Labeler add-in works, but the labels are static and it required a separate pivot table for the COST totals.

    I really would like it all to happen with just one table and a click of the refresh button.


    I am trying to create a 100% stacked column chart from an existing pivot table.

    I have each column (MONTH) representing the % of the count of parts ORDERED within three PRIORITY catagories: Emergency, Rush and Rountine.

    I would like the data labels for each point to show, not the count of parts ORDERED, but the sum of COST of each PRIORITY in each column (MONTH).

    Is this possible using Excel 2000 or 97?

    (All caps above indicate existing columns in the pivot table.)