How to paste the same cell one tab over

Discussion in 'Excel' started by Kyle Hinterman, Nov 20, 2015.

  1. Kyle Hinterman

    Kyle Hinterman

    Joined:
    Nov 20, 2015
    Messages:
    1
    Likes Received:
    0
    I am building a spreadsheet where page 1 is a summary page of the next 7 tabs. Column D is a summary of the data entered on tab 2. Column E is a summary of tab 3 and so on down the spreadsheet. Tab 2-8 are all the same and I'd like to cut & paste the formula I wrote in column D to the corresponding columns, but when I do so, the formula that is pasted is one column to the right on the same tab, which means I have to do a lot of manual editing.

    Is there an easy way to cut this formula: =tab2!V20 and make it paste =tab3!V20?

    Thanks in advance for your help
     
    Kyle Hinterman, Nov 20, 2015
    #1
    1. Advertisements

  2. Kyle Hinterman

    XLPadawan

    Joined:
    Jun 9, 2016
    Messages:
    32
    Likes Received:
    5
    Location:
    Allen, TX
    In A1, =COLUMN(). Extend to the right until you have a number high enough to match your last Tab# sheet.
    Copy and paste into B2:
    =INDIRECT("'Tab"&B$1&"'!"&"A"&ROW(A1))
    Fill right and down with the formula until you account for all your tabs and all data records. New tabs will be easy to account for as long as you stick with the same tab-naming convention.​
     
    XLPadawan, Jun 10, 2016
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. jushin100
    Replies:
    1
    Views:
    984
    madonaalbert
    Jun 21, 2012
  2. Stormy4757
    Replies:
    1
    Views:
    758
    XLPadawan
    Jun 9, 2016
  3. marste7

    Copy and Paste Formula

    marste7, Aug 27, 2013, in forum: Excel
    Replies:
    1
    Views:
    774
    XLPadawan
    Jun 16, 2016
  4. asyluma
    Replies:
    0
    Views:
    485
    asyluma
    Oct 9, 2013
  5. Hwalker1
    Replies:
    2
    Views:
    556
    XLPadawan
    Jun 13, 2016
  6. SargeUSMC

    renaming cell ranges

    SargeUSMC, Jul 1, 2014, in forum: Excel
    Replies:
    1
    Views:
    321
    MyHelpster
    Jul 3, 2014
  7. iggydarsa
    Replies:
    0
    Views:
    294
    iggydarsa
    Apr 27, 2015
  8. Garrett Gaston

    Many sheets, one DB?

    Garrett Gaston, Feb 22, 2016, in forum: Excel
    Replies:
    1
    Views:
    620
    Becky
    Feb 25, 2016
Loading...