Posts by markc

    Re: Concatenate Each Row To Cells In Column

    Thanks Dave,

    I'm not receiving any more errors.
    But I definitely have a logic problem

    The final string is correct for the first record (Rows 2-4)
    But not the second record (Rows 5-6)

    Rows 3 and 4 are updating with comments from rows 5 and 6, respectively
    The intent is that rows 2,3,4 should concatentate and be placed in row 2 - which works

    Then 6 should be concatenated onto 5 and place in row 5 - this does not work
    Apparently I'm not updating intRecord correctly. After buggering with it for 2 hours I"m not sure how to.

    Any ideas?
    Updated code below

    Thanks much!

    Hi all,
    Does anyone have any ideas as to the code logic below
    I can't figure out what I'm doing wrong

    Hi all,

    I am trying to concatenate multiple lines of comments into one string and place that string in the comment field corresponding to the start of each record set

    I believe the code below is close, but I am receiving this error


    Run-time error '91':
    Object variable or with block variable not set

    Debug points to this line

    rngComment = .Cells(intCounter, 5)

    I'm not sure what I am doing wrong.
    Can you please nudge me in the right direction?
    Many thanks!

    Hi all
    I have entered text as I would like in the formula bar
    However not all of the text is displayed in the cell
    I have sized the cell so it should definitely dispaly all text
    Word wrap is on
    But the text displayed in the cell is truncated

    Also the spreadsheet was originally in 2007 format, I converted to 2003

    I also tried copying all text from the formula bar, pasted to notepad, deleted from Excel, copied from Notepad, pasted back to the formula bar.

    Still not all text is displayed
    In a helper cell I dropped in =len(xx) where xx is the reference. Returned 1357

    Thanks much!

    Re: Microsoft Query: Data Source .xls Files

    Hi Dave,

    Yes, Excel Files is selected in the databases tab of the Choose Source Dialog Box
    Still, I do not see any Excel files listed.

    I also tried MS Access Database, I browsed to the folder containing the download files
    I cannot see .mdb files either.

    I tried everything on another PC in the house with only Office 2003 no Office 2007 a in my primary system.

    Still, no files are visible when I browse through Choose Data Source in MS Query

    Any ideas what is going on?

    Hi all -

    Trying to work through tutorial on creating offline cube from an Excel file, A CompleteGuide to PivotTables: A Visual Approach, Cornell, ISBN 1-59059-432-0, pg 248.

    Steps I am taking:
    1. Click on Data Menu
    2. Import External Data
    3. New Database Query

    On Databases tab
    4. Click on Excel Files*
    5. Click on Browse Button

    I migrate to the loction of the sample files I downloaded, but no files appear in the Browse Data Sources dialog window.

    Files of type drop down reveals only "Data Sources" (w/o quotes)

    I browsed the folder with Windows Explorer. There are several Excel files in the folder as well as sample, .cub and .mdb files.

    I do have Excel 2007 installed on the PC as well, but working through the tutroail with Excel 2003.

    Any ideas why the Excel files are not visible?
    Thanks all

    Re: Show Data Labels On Bars Of Bar Chart

    Thanks all,

    Could you expand a bit?
    I tried dragging the vertical value axis to the bar chart, had no impact
    Was there somthing else I was to drag n' drop?


    Thanks. I am trying to make the value axis appear as overlaid on the bar chart as opposed to immediately to the left of the bar chart


    Looks good, except lost the data value labels.
    Can I have both using this method?

    I walked through the steps you provided
    They were a little different for xl 2007, but very close

    For label options I chose Category Name and Value.
    For label position, I chose Inside Base

    This yields the category name.. a comma.. category value
    Is it possible to have the label inside base but the category value to the right end of each bar?

    Thanks all!

    Re: Overlay 2 Charts

    Thanks Daniel,

    Works great!
    Interesting that dragging one chart on top of the other works, but copy/paste does not

    Thanks for the help

    Hi all,

    Using Excel 2007

    I am trying to overlay 2 identical charts
    On the first chart I set all items to 100% Transparency except for the line and the area charts

    On the 2nd chart (exact replica) I did just the oppposite leaving all axes, gridlines, etc.. visible and made the area and line charts transparent

    I then right clicked the 2nd chart and brought it forward
    I then copied the first chart and pasted it to cell F11

    I then copied the 2nd chart and pasted it to cell F11

    The outcome was not what I was looking for
    I would like the charts to retain all coloring and for the gridlines to appear in front of the line and area charts

    What did I do wrong?


    Re: Format Dates On Chart Axis

    Thanks Andy,

    I am using Excel 2007
    I had to save the workbook in 2003 to upload here.

    I set the labels in column F on Data = to Col A for rows 1 & 13.
    I then applied a custom date format: [$-409]mmm d, "'"yy (though I would like a 4 letter month to use all letters instead of 3)

    I formatted the x axis for text axis, this solved the problem with altering the line chart to end points only.:thanx:

    The only item left, is the date labels for points 1 and 13 are wrapping instead of expanding along the x axis.

    Is it possible to expand horizontally along the axis or do I need to use a text box and set = Data!$F$1 for the first data point for example?

    The workbook is attached
    The chart I am working on is in cell H5


    Re: Format Dates On Chart Axis

    Thanks Dave,

    I tried as you suggested.
    I used a helper column and only set $F1 and $F13 equal to $A1 and $A13 respectively
    This resulted in altering the chart to a straight line instead of the current jagged line.

    I also tried Setting $F= $A, which resulted in a jagged line, but too many labels in the x axis

    The chart is also displaying the beginning and end dates as Jan 1, '04 and Jan 1, '05 respectively. Should be Feb for both points

    The worksheet is attached