+ Reply to Thread
Results 1 to 6 of 6

insert Rows with Formulas in Place on Multiple Sheets?

  1. #1
    Michael Link
    Guest

    insert Rows with Formulas in Place on Multiple Sheets?

    Hey everyone:

    I have a workbook with 8 sheets. Cells A1:A400 need to be identical on each
    sheet. On sheets 2 through 8, I use =’Sheet 1’!AX (where X is the row number)
    to pick up the info from the corresponding cell on Sheet 1 so I only need to
    enter changes once.

    I sometimes need to add rows. However, the cells in column A still need to
    correspond across sheets. On Sheet 1, I select the place where a row needs to
    insert and then select all sheets so that the row I insert goes in on all
    sheets.

    The problem is, the new row is blank, and on sheets 2 through 8 I have to do
    an autofill from the row above to get the “pick-up” formula into the new
    row. This is easy, I know, but is there a way to get the row inserted on
    sheets 2-8 to have the =’Sheet 1’!AX formula already in place? There have
    been many instances where users forget to do the autofill or do it
    incorrectly (!), with the result that things get out of sync in the workbook.
    Besides, we often have workbooks with many more sheets, so that even doing
    autofill on each sheet can be slow.

    Please please please help! Is there maybe a macro that will do this? (I can
    always hook it up to a button on sheet 1 so users can just click it and go.)

    M. Link


  2. #2
    Max
    Guest

    Re: insert Rows with Formulas in Place on Multiple Sheets?

    One way which might suffice ..

    Group Sheets 2 - 8,
    then in Sheet2,
    put in A1: =OFFSET(Sheet1!$A$1,ROW()-1,)
    and copy A1 to say, A450*,
    to cover the max expected extent in the "master" Sheet1
    Ungroup Sheets 2 - 8

    *an additional 50 rows buffer

    A1:A450 in Sheets 2 to 8 will always point to A1:A450 in Sheet1
    This will cater for new row insertions which may be made
    within Sheet1's "original" rows 1 - 400
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Michael Link" <MichaelLink@discussions.microsoft.com> wrote in message
    news:1B313B62-383A-4DCE-9A8F-EA8CE6BC61F1@microsoft.com...
    > Hey everyone:
    >
    > I have a workbook with 8 sheets. Cells A1:A400 need to be identical on

    each
    > sheet. On sheets 2 through 8, I use ='Sheet 1'!AX (where X is the row

    number)
    > to pick up the info from the corresponding cell on Sheet 1 so I only need

    to
    > enter changes once.
    >
    > I sometimes need to add rows. However, the cells in column A still need to
    > correspond across sheets. On Sheet 1, I select the place where a row needs

    to
    > insert and then select all sheets so that the row I insert goes in on all
    > sheets.
    >
    > The problem is, the new row is blank, and on sheets 2 through 8 I have to

    do
    > an autofill from the row above to get the "pick-up" formula into the new
    > row. This is easy, I know, but is there a way to get the row inserted on
    > sheets 2-8 to have the ='Sheet 1'!AX formula already in place? There have
    > been many instances where users forget to do the autofill or do it
    > incorrectly (!), with the result that things get out of sync in the

    workbook.
    > Besides, we often have workbooks with many more sheets, so that even doing
    > autofill on each sheet can be slow.
    >
    > Please please please help! Is there maybe a macro that will do this? (I

    can
    > always hook it up to a button on sheet 1 so users can just click it and

    go.)
    >
    > M. Link
    >




  3. #3
    Michael Link
    Guest

    RE: insert Rows with Formulas in Place on Multiple Sheets?

    Thanks for the fast reply!

    Actually, the rows in the master sheet that i want to 'carry over" are
    A8:A400. (I forgot that I have a number of hidden rows at the top that don't
    figure in what I need to do.)

    I've tried modifying the formula you so nicely gave me like this:

    =OFFSET(Sheet1!$A$8,ROW()-1,)

    But it just returns a zero value. Am I missing something? I'm not familiar
    with OFFSET, so I'm not sure how else to modify it, and the discussion of the
    syntax in the help box is kind of confusing.




    "Michael Link" wrote:

    > Hey everyone:
    >
    > I have a workbook with 8 sheets. Cells A1:A400 need to be identical on each
    > sheet. On sheets 2 through 8, I use =’Sheet 1’!AX (where X is the row number)
    > to pick up the info from the corresponding cell on Sheet 1 so I only need to
    > enter changes once.
    >
    > I sometimes need to add rows. However, the cells in column A still need to
    > correspond across sheets. On Sheet 1, I select the place where a row needs to
    > insert and then select all sheets so that the row I insert goes in on all
    > sheets.
    >
    > The problem is, the new row is blank, and on sheets 2 through 8 I have to do
    > an autofill from the row above to get the “pick-up” formula into the new
    > row. This is easy, I know, but is there a way to get the row inserted on
    > sheets 2-8 to have the =’Sheet 1’!AX formula already in place? There have
    > been many instances where users forget to do the autofill or do it
    > incorrectly (!), with the result that things get out of sync in the workbook.
    > Besides, we often have workbooks with many more sheets, so that even doing
    > autofill on each sheet can be slow.
    >
    > Please please please help! Is there maybe a macro that will do this? (I can
    > always hook it up to a button on sheet 1 so users can just click it and go.)
    >
    > M. Link
    >


  4. #4
    Max
    Guest

    Re: insert Rows with Formulas in Place on Multiple Sheets?

    > =OFFSET(Sheet1!$A$8,ROW()-1,)

    The amendment looks ok to me.

    I presume the above is entered into cell A1 in Sheets 2 - 8, and copied down
    to A450 as per suggested steps earlier, with sheets grouped? (The formulas
    are sensitive to the cells they are in)

    If the starting cell is *not* cell A1 in Sheets 2 - 8,
    put instead this slight revision in the starting cell:
    =OFFSET(Sheet1!$A$8,ROWS($A$1:A1)-1,)
    and then copy down by 450 rows as before,
    with sheets grouped

    Ensure also that calc mode is set to auto.
    Check via clicking:
    Tools > Options > Calc tab > Auto > OK
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Michael Link" <MichaelLink@discussions.microsoft.com> wrote in message
    news:401E7506-25E1-44A0-92E0-6AB1ACAA23FF@microsoft.com...
    > Thanks for the fast reply!
    >
    > Actually, the rows in the master sheet that i want to 'carry over" are
    > A8:A400. (I forgot that I have a number of hidden rows at the top that

    don't
    > figure in what I need to do.)
    >
    > I've tried modifying the formula you so nicely gave me like this:
    >
    > =OFFSET(Sheet1!$A$8,ROW()-1,)
    >
    > But it just returns a zero value. Am I missing something? I'm not familiar
    > with OFFSET, so I'm not sure how else to modify it, and the discussion of

    the
    > syntax in the help box is kind of confusing.




  5. #5
    Michael Link
    Guest

    Re: insert Rows with Formulas in Place on Multiple Sheets?

    Thanks again for the response. I must be pretty dense, though, because when
    rows insert across all of the grouped sheets, they continue to have no
    formulas in them, and hence don't carry the stuff from the "master sheet"
    forward. Your revision of the formula does carry info forward iin existing
    rows, and that's great, but inserted rows on subsequent sheets are still
    goiing in totally blank (with no formulas).

    Hmm. I'm beginning to think it might just be easier to continue to be the
    point person for all revisions to this thing. If I make it look mystical
    enough they'll never fire me because no one else will know how to do it. Job
    seciurity.

    "Max" wrote:

    > > =OFFSET(Sheet1!$A$8,ROW()-1,)

    >
    > The amendment looks ok to me.
    >
    > I presume the above is entered into cell A1 in Sheets 2 - 8, and copied down
    > to A450 as per suggested steps earlier, with sheets grouped? (The formulas
    > are sensitive to the cells they are in)
    >
    > If the starting cell is *not* cell A1 in Sheets 2 - 8,
    > put instead this slight revision in the starting cell:
    > =OFFSET(Sheet1!$A$8,ROWS($A$1:A1)-1,)
    > and then copy down by 450 rows as before,
    > with sheets grouped
    >
    > Ensure also that calc mode is set to auto.
    > Check via clicking:
    > Tools > Options > Calc tab > Auto > OK
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
    > "Michael Link" <MichaelLink@discussions.microsoft.com> wrote in message
    > news:401E7506-25E1-44A0-92E0-6AB1ACAA23FF@microsoft.com...
    > > Thanks for the fast reply!
    > >
    > > Actually, the rows in the master sheet that i want to 'carry over" are
    > > A8:A400. (I forgot that I have a number of hidden rows at the top that

    > don't
    > > figure in what I need to do.)
    > >
    > > I've tried modifying the formula you so nicely gave me like this:
    > >
    > > =OFFSET(Sheet1!$A$8,ROW()-1,)
    > >
    > > But it just returns a zero value. Am I missing something? I'm not familiar
    > > with OFFSET, so I'm not sure how else to modify it, and the discussion of

    > the
    > > syntax in the help box is kind of confusing.

    >
    >
    >


  6. #6
    Max
    Guest

    Re: insert Rows with Formulas in Place on Multiple Sheets?

    My fault, sorry. Think I missed this bit about
    > .. rows insert across all of the grouped sheets

    earlier in your original post

    (I had interp'd/read it wrongly that the row insertions
    were going to be made on just Sheet1)

    perhaps you might want to browse this response
    to a similar query:

    http://tinyurl.com/mt3pp

    (the 2 "barebones" subs InsertRow and DeleteRow
    seem to work ok for the OP over there, going by his reply <g>)
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Michael Link" <MichaelLink@discussions.microsoft.com> wrote in message
    news:8B3BCD39-2AA2-4F6B-BA2B-AF923A32E137@microsoft.com...
    > Thanks again for the response. I must be pretty dense, though, because

    when
    > rows insert across all of the grouped sheets, they continue to have no
    > formulas in them, and hence don't carry the stuff from the "master sheet"
    > forward. Your revision of the formula does carry info forward iin existing
    > rows, and that's great, but inserted rows on subsequent sheets are still
    > goiing in totally blank (with no formulas).
    >
    > Hmm. I'm beginning to think it might just be easier to continue to be the
    > point person for all revisions to this thing. If I make it look mystical
    > enough they'll never fire me because no one else will know how to do it.

    Job
    > seciurity.




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.05993 seconds
  • Memory Usage 8,960KB
  • Queries Executed 16 (?)
More Information
Template Usage (30):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (6)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (6)postbit_legacy
  • (6)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (40):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php
  • ./includes/functions_prefix.php 

Hooks Called (44):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1