Posts by markc

    Hi all
    I opened an Excel attachment from Outlook
    I thought I saved it on my drive

    I made lots of updates that took me 1 hour
    I thought I saved and closed
    I looked in the directory where I thought I save it - but nothing is there.

    I checked my recent files in Excel - nothing.
    I checked my recent files in Windows - nothing.

    Is there any way I can get this file back?


    Hi All -

    I added Custom View to right of Help on the Menu Bar.
    Works great
    Until I close Excel and later re-open Excel
    The custom toolbar is no longer available
    I have to drag it to the menu bar each time

    Is there a way to make Custom View available in this position every time I open Excel?


    Re: Copy/paste Formulas Only

    Thanks Roy
    Attached is a sample worksheet
    I added entered 2 values and a formula summing the 2 values
    I copied all 3 cells
    I moved over 1 column
    I used pastespecial formula
    The 2 numbers were pasted as well as the formula
    I need the 2 values to be skipped

    This is a very simple example.
    The actual workbook has over 40 rows with close to 20 formulas in each column and lots of hard inputs interspersed


    Re: Copy/paste Formulas Only

    Thanks Roy -

    That picks up hard coded values and pastes them over the current value
    I need to be able to skip the hard inputs


    Hi all

    I have setup a forecast model and now find it necessary to update many formulas

    I have set period 1 formulas correctly and would like to copy through periods 2-12. However each period has already been populated with data that need to remain intact in this process

    For example,
    I have formulas to copy at I5 and I9. I6,7,8 should be skipped

    These formulas should be pasted to K5 and K9 respectively, while leaving K6,7,8 intact

    This is of course a simple example, the column contains 15 formulas to be copy/paste

    Thanks much

    Hi all -

    I need to get the right most non zero value from 5 columns (D:H)
    So starting at H and working backward
    If H5 <> 0 Then C5 = H5
    ElseIf G5 <> 0 then C5 = G5
    ElseIf F5 <> 0 Then C5 = F5
    ElseIf E5 <> 0 Then C5 = E5
    ElseIF D5 <> 0 Then C5 = D5

    But I'm not sure how to write a formula to achieve this.

    Hi all

    I am trying to count some data I stripped from SAP General ledger
    I need to count a record if
    Within range of GL Codes
    Within date range

    Here is the sumproduct formula I came up with

    Data! Contains the data to be analyzed
    $A is the unit number(s)
    $C is the GL Codes
    $B1 is the lowest GL code to be incl
    $B8 is the highest GL code to be incl
    $J is a range of dates
    $B10 is lowest date to be incl
    $C10 is the highest date to be incl

    Currently the formula returns a #VALUE! error
    Where did I go wrong?


    Re: Indirect Function Inside Sumproduct Formula

    Thanks Bob -

    I will add the dynamic named ranges for the criteria and the range to sum
    But I believe I still need to use indirect to allow the user to choose which table (sheet) is to be presented.

    Unless there is a way around this?


    Re: Indirect Function Inside Sumproduct Formula

    Thanks for the help guys.
    Both solutions worked equally well
    But I like the latter a little better as the end range could grow in the future

    I guess I need to loop through each data sheet and get the end row nad use the maximum value in the frmla.


    Hi all -

    I am attempting to build an interactive user-app to let the user choose a table name and update P&L values based on selection.

    Start: Row 2
    End: Row 2831 (Some tables less, this is max size table)

    Here is my current frmla

    B$8 is comparing the Unit number
    $A9 is comparing descriptive label
    $D$2:$D$2831 is range of values to be summed if 2 conditions are met

    This frmla is currently returning a #REF! error

    So then I tried re-writing the Indirect arg's by placing the COl/Row Ref's in cells


    This is also returning a #REF! error

    I attached a small sample file
    I had to trim all data sheets out but 1
    I had to trim out all data but 2 units
    Should be enough to test with though

    Thanks much

    Re: Select Single Cell After Paste Large Range

    Hi all -

    Determined I need to activate the sheet before I can select a range (cell) on the sheet.


    This works and removes the blue shading from the pasted area.
    But on every sheets a cell in row 10 is selected not row 6
    Usually in ColA but I have one sheet with Col's A & B hidden so should select in Col C, but E10 is selected instead.

    It's not a big problem, just an oddity that doesn't seem to make sense.

    Thanks much

    Re: Select Single Cell After Paste Large Range

    I added application.cutcopymode = false
    No help. Most likely I used incorrectly

    code below

    Hi all -

    I would like to select cell A6 after pasting a large range so that when send to user the entire range is not selected

    I tried




    I receive


    Select method of range class failed

    How can I select a single cell after pasting the large range?


    Hi all -

    I am trying to turn autofilter on for every sheet in the workbook except "Homepage"

    I thought this code would do it

    For Each ws In wbDest.Sheets
                If ws.Name <> "Homepage" Then
                    ws.AutoFilterMode = True And ws.FilterMode = True
                End If

    No error messages are received.

    But when I open the file the filter arrows are not visible
    Can you please tell me what I'm doing wrong?