Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Poor Man's" Scenarios
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Poor Man's" Scenarios
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Poor Man's" Scenarios
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Poor Man's" Scenarios
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 EditReplace, 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 EditReplace 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" wrote: .. For eg, 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Poor Man's" Scenarios
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" wrote: 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 EditReplace, 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 EditReplace 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" wrote: .. For eg, 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
"Poor Man's" Scenarios
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |