How to paste the same cell one tab over

[Kyle Hinterman]

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

 

[XLPadawan]

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.​