Posts by JimFuller1

  • Static Worksheet Reference

    Re: Static Worksheet Reference


    ac02778,

    Short Answer: Always have all the workbooks open when you edit the formulas and Excel will update correctly. Save all the workbooks.

    A Better Answer:
    Don't name the files with the year number. Use the directory structure to handle the year. That way the files don't need to be updated and maybe not even the formulas. Each new year you just copy the old directory to the new year like "C:\Data\1995" to "C:\Data\1996". When you open the 1996 files, Excel will automatically look in the folder the files are in to update the links.

    If you have to update formula with the year to reference the older folder, use search and replace in the summary workbook.

    Jim

  • pivot table querry

    Re: pivot table querry


    I don't think I can instruct you using this medium. The best advice I can give you is to drag the fields around until you see something you like.


    Sorry,
    Jim

  • if selection = string or value

    Re: if selection = string or value


    Quote


    I would like to select and manipulate only the ones that contain dates (or values).


    Brett,


    If you want a simple, fast way to do this, Dave H. has it right. Use the [Edit][Go To...] menu command and press the Special button. In the dialog box you should select "Constants" and make sure only "Numbers" has a checkmark by it. That will select all dates in your list since they're really stored as numbers as well as any numbers that don't represent a date.


    If you record the macro it looks something like this:

    Code
    Selection.SpecialCells(xlCellTypeConstants, 1).Select


    Jim

  • Dynamic formula contents

    Re: Dynamic formula contents


    Steve_J,


    Concerning the cell A1 --


    You have entered as text the following:
    '($A$3:$A$12="a")*($B$3:$B$12>1)*($C$3:$C$12<5)*($D$3:$D$12)


    Is that correct?


    Maybe you could use RIGHT(A1,99)?


    Jim

  • "Insert row" macro pasting format and formulas under last row

    Re: &quot;Insert row&quot; macro pasting format and formulas under last row


    Try this:


    Code
    Sub CopyMacro()
    ActiveSheet.Range("a65536").End(xlUp).EntireRow.Copy
    ActiveSheet.Range("a65536").End(xlUp).Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormats
    ActiveSheet.Range(Range("s65536").End(xlUp), "aa" & Range("s65536").End(xlUp).Row).Copy
    ActiveSheet.Range("s65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
    Application.CutCopyMode = False
End Sub

  • "Insert row" macro pasting format and formulas under last row

    Re: &quot;Insert row&quot; macro pasting format and formulas under last row


    Try something like this.


    Code
    Sub CopyMacro()
    ActiveSheet.Range("a65536").End(xlUp).EntireRow.Copy
    ActiveSheet.Range("a65536").End(xlUp).Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormats
    ActiveSheet.Range("a65536").End(xlUp).Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormulas
    Application.CutCopyMode = False
End Sub

  • Clear Contents Macro

    Re: Clear Contents Macro


    kfotedar,


    I recommend you turn on the macro recorder and do the actions you need to do. Excel will capture the deleting and entering of zero as you type.


    If the cells you need to clear aren't in the same place all the time, you may need to name them and select them by name before you clear them and set the values to zero.


    Jim

  • Summing Data in Multiple Workbooks

    Re: Summing Data in Multiple Workbooks


    Try this:


    1) Open workbook number 1
    2) Open workbook number 2
    3) Open summary workbook
    4) In the summary workbook type an equal sign "=" into a cell
    5) Click on workbook number 1 by clicking the name in the task bar at the bottom of the windows screen and click any cell.
    6) Type a pluse sign "+".
    7) Click on workbook number 2 by clicking the name in the task bar at the bottom of the windows screen and click any cell.
    8) Hit the enter key on your keyboard.


    That should give you the idea. Let me know if it doesn't.


    Jim

  • Matching partial data with full data

    Re: Matching partial data with full data


    hcrew86,


    It's hard to tell if there is some "hidden" data that could be unique to both lists because the "Orders List" is for a location that doesn't exist on the "Full List".


    Can you post the "Full List" data for the sample order? There may still be hope even though the chances are slim right now.


    Jim