Your first answer is perfect. Thanks.

The second part didn't work, but that is because my initial explanation was
faulty. Sorry!

Actually, each tab's information takes up several rows on the Summary tab,
with a blank space at the end of each set of several rows. So, each piece of
information from each tab is not one row down from the previous tab, but
several. Also, the source information on each tab is not in one row but
scattered throughout the tab. It is then transposed to several rows on the
Summary tab. Does this affect your formula?

Also, I would like to give each tab a name, not just Sheet. Does this
invalidate your formula and if so which other parameter can I insert in your
formula instead of Sheet to make it work?

For example, two rows on the Summary I want to copy have the following
references reading columns from left to right:

GM!A5 GM!A8 GM!B14
GM!A7 GM!C4 GM!E2
etc.

I want to copy these references a few rows down to automatically paste these
references:

FORD!A5 FORD!A8 FORD!B14
FORD!A7 FORD!C4 FORD!E2
etc.


Conceptually, I almost need a Paste Special where, instead of, say,
multiplying by a value in a cell, I use a value in a cell (FORD) to
substitute for GM and keep the A5, etc. constant instead of changing the row
from A to E because it is 4 rows down on the Summary tab.

I don't think there is a Paste Special for this, but hopefully there is a
workaround which works conceptually the same in a semi-automated way without
a lot of keystrokes each time.

If I can clarify in any other way, please let me know.

Many thanks for your assistance.

"ShaneDevenshire" wrote:

Hi,

Question 1 - Hold down the Ctrl key and drag the template tab to a new
position. Ctrl+Drag creates an exact copy but with a different name. All
formatting (column widths...), and data are copied.

Question 2 - If I understand it correctly, if your sheets are named in order
Sheet 1, Sheet 2 and so on you can reference the same cell in all sheet with
one formula which you can copy down.
=INDIRECT("sheet"&ROW(A2)&"!A1")
with this example you are returning the value from cell A1 of Sheet2. When
you copy it down it will be Sheet3!A1 and so on.


--
Thanks,
Shane Devenshire


"Tenacity9" wrote:

I can't use Scenarios for the following task because it takes about a minute
to do the Summary on Scenarios and it sometimes crashes Excel. So, I am
seeking an alternative:

I have a multi-tabbed workbook where I want to have key cells from each tab
copied to a separate row on a Summary tab.

Each tab follows the same template.

1. Is there a way to make one of the tabs a blank template so that every
time I want to create a new tab to fill in the information, the template is
easily transferred (including column widths, etc.) to the new tab. I suppose
I can copy and paste from the template tab to the new tab, but I 'm wondering
if there is something more streamlined to do this.

2. On the Summary tab, I laboriously put cell references from Sheet 1! into
the appropriate cells. The information from the next tab will start on the
next row on the Summary down from the information from the existing tab, and
so on. If I copy and paste the existing rows to the new rows, the references
will still refer to Sheet 1 and will have the relative locations thrown off.
Is there a way to copy the existing rows to the new rows and make a blanket
change from Sheet 1! to Sheet 2! Also, to keep the same absolute cell
references to apply to Sheet 2!

With regard to the latter, I know I can laboriously hit F4 to put $ next to
each cell reference before I copy the formula, but this is time consuming.
Is there a way to change Sheet 1! to Sheet2! on a blanket basis and also
insert the $ on a blanket basis to the references are made absolute all at
once.

Also, if my idea of tabbed woksheet connected to a summay tab is not
optimal, I certainly would appreciate suggestions as to a more user-friendly
way of accomplishing my task without using Scenarios, which is not working
well in this instance.

More rather than less detail is appreciated, because I am no more than an
intermediate Excel user, not advanced.

Thank you.