Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pasting rows with formulas (Excel 2000 on 2K Pro)

    OK, un-hiding the columns did not resolve the problem I am having. I have a workbook with 50 sheets. I am trying to have my sheet 1 reflect totals from various areas throughout the work-sheets. Since the formulas are very repetitious I decided to copy and paste rows. Then whatI do is use the replace command to change the name within the formula wich works very well with one little glitch. The glitch is that when I paste the row, the formulas jump 3 columns. Thefirst 3 columns of my Main sheet wre hidden but that is not the problem. Have 50 rows to do with about 40 columns.
    Why does the paste command jump 3 columns? (the 3 is equal to the number of categories per worksheet

    Thank you in advance!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pasting rows with formulas (Excel 2000 on 2K Pro)

    Could you give a few more details or perhaps attach a sample file?

    Steve

  3. #3
    New Lounger
    Join Date
    Jan 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting rows with formulas (Excel 2000 on 2K Pro)

    On Sheet 1 column A, I have listed 48 Names starting with cell A5 down to cell A52
    Each command starts a row that extends all the way to cell (column) BH starting at cell (column) E5
    Upon entering the formulas in the first row (next to the first Name in Column 1), to save time, I decided to use the copy command on the whole row (E5 - BH) and copy it to the next row in sequence. Then I can use the Search and Replace command to change the name within that row, which works. Here's the glitch: the formulas do not appear in the corresponding cells, they appear 3 cells over in the row, with zero in the first 3 cell of the row instead of the respective formula, with appear on the 4th cell in the row.

    The 48 sheets in the workbook named after each person in the front sheet Column A have a table. This table has 4 columns of products and 23 rows, representing a variation of each product sold. The entries are in numbers. The front sheet will show me a glance at what everyone is selling, etc.

    I do not want to link each cell using the formula bar and then clicking on the worsheet and then each cell over and over.

    I hpe tis cleras it up a little. Thank you for your help.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pasting rows with formulas (Excel 2000 on 2K Pro)

    How are your names created (what is the "refers to")?

    Just a guess:
    If the name is based on relative cell reference, it will change dependent on the "active cell". If you want it locked it must be an absolute reference. Are you using relative references, ie no "dollar signs" ($)?

    Steve

  5. #5
    New Lounger
    Join Date
    Jan 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting rows with formulas (Excel 2000 on 2K Pro)

    I am not using Dollar Signs
    The formulas in the front sheet (sheet 1) look like this in the cells
    Cell 5E 5F 5G 5H 5I 5J
    'Smith Row'!E24 'Smith Row'!F24 'Smith Row'!G24 'Smith Row'!E25 'Smith Row'!F25 'Smith Row'!G25
    Cell 6E 6F 6G 6H 6I 6J
    Plainfield!E24 Plainfield!F24 Plainfield!G24 Plainfield!E25 Plainfield!F25 Plainfield!G25

  6. #6
    New Lounger
    Join Date
    Jan 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting rows with formulas (Excel 2000 on 2K Pro)

    Steve I got it, Thank you for your assistance. All better.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •