"Poor Man's" Scenarios

[Tenacity9]

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.

 

[ShaneDevenshire]

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.

 

[Tenacity9]

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.

 

[Max]

Venturing 2 options for you on your 2nd Q

Option a:
Consider a re-design of your summary sheet. Have an "easier-to-propagate"
lay out where the sheetnames are listed in say, B2 down, and the cell
references in C1 across

Eg you could have in B2 down, the sheetnames:
GM
Ford

And in C1 across, you'd have cell refs eg:
A5, A8, B14, A7, C4, E2

Then you could place in the top left intersection cell, ie in C2:
=INDIRECT("'"&$B2&"'!"&C$1)
and simply C2 copy across/fill down as far as required to populate the summary

Option b:
As-is, to propagate the formulas from the 1st to the 2nd block, you could
try Edit>Replace, along this sequence:

Neutralize the formulas in the 1st block, then copy n paste, then replace
sheetnames in the pasted range, then restore all formulas in both destination
and source

1. Select source "GM" range of formulas, replace "=" with say "zzzzzzz"
2. Do the copy > paste
3. In the pasted selection change the sheetnames enbloc from "GM" to "Ford"
with an Edit>Replace
4. Then restore the "=" with a reverse: Replace "zzzzzzz" with "="
in both pasted range & in the source 1st block
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300 Files:361 Subscribers:57
xdemechanik

 

[Tenacity9]

Thanks for the suggestions. Edit-Replace seems to be the best for this
application, although it would be nice if there was something more automated.

 

[Max]

Thanks for the suggestions.

Welcome. Btw, from where you're reading this (MS' webpages)
there's a "Yes" button below for you to rate responses received
Do take a moment to press that "Yes" button for all responses which help

Edit-Replace seems to be the best for this application,
although it would be nice if there was something more automated.

You could try recording a macro when you do it manually.
And then post in .programming for help to generalize it, if need be.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300 Files:361 Subscribers:58
xdemechanik
---