Copying the same cell from different sheets

I have a bunch of worksheets, each formatted identically.  Let's say their 
names are "A" to "Z".  I insert a new sheet and I want to examine the same 
cell from all sheets.  Suppose there is a value representing annual expenses 
in cell D13 of each sheet.  My new sheet might look something like this:

Sheet    Annual Expense
A        $100
B        $150
C        $200
etc.

Is there a way to do this without having to enter the formula separately 
into each cell?  It seems to me I saw somewhere a way to "build" a formula by 
stringing the components together.
0
Steve3367 (862)
3/16/2005 6:49:09 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
255 Views

Similar Articles

[PageSpeed] 16

Hi steve

Try this to create the formulas with a macro
http://www.rondebruin.nl/summary.htm


-- 
Regards Ron de Bruin
http://www.rondebruin.nl



"Steve" <Steve@discussions.microsoft.com> wrote in message news:CE523045-FD2B-4319-A267-E7D02D4809C7@microsoft.com...
>I have a bunch of worksheets, each formatted identically.  Let's say their
> names are "A" to "Z".  I insert a new sheet and I want to examine the same
> cell from all sheets.  Suppose there is a value representing annual expenses
> in cell D13 of each sheet.  My new sheet might look something like this:
>
> Sheet    Annual Expense
> A        $100
> B        $150
> C        $200
> etc.
>
> Is there a way to do this without having to enter the formula separately
> into each cell?  It seems to me I saw somewhere a way to "build" a formula by
> stringing the components together. 


0
rondebruin (3790)
3/16/2005 7:01:01 PM
IF and only IF your sheets are actually named as displayed, then use the 
formula

=INDIRECT(cell_containing_first_sheet_name&"!d13")

then copy it down for all the sheet names

If D13 is not a constant - i.e., you want to use different cell addresses in 
different summaries, then put the address (D13) in another cell, say B1, and 
use this formula

=INDIRECT(cell_containing_first_sheet_name&"!"&B$1)


"Steve" wrote:

> I have a bunch of worksheets, each formatted identically.  Let's say their 
> names are "A" to "Z".  I insert a new sheet and I want to examine the same 
> cell from all sheets.  Suppose there is a value representing annual expenses 
> in cell D13 of each sheet.  My new sheet might look something like this:
> 
> Sheet    Annual Expense
> A        $100
> B        $150
> C        $200
> etc.
> 
> Is there a way to do this without having to enter the formula separately 
> into each cell?  It seems to me I saw somewhere a way to "build" a formula by 
> stringing the components together.
0
DukeCarey (494)
3/16/2005 7:43:03 PM
Reply:

Similar Artilces:

text in cell looks different when printed
text in cell looks ok on screen. when printed, text is moved around, out of alignment from the way the cell looked when viewing the screen. When keying in information into a cell, how do you get the text to skip a line? when I press enter key, the pointer moves to the next cell. Hi I would check your printer driver for your first problem. How does it look on Print Preview? For your second question, use Alt Enter and this will insert a carriage return into the cell. -- Andy. "thomas brown" <anonymous@discussions.microsoft.com> wrote in message news:35c101c42944$...

Problem with Copy-Paste in Publisher 2003
I use Publisher to make a monthly newsletter and already have the template laid out, I have been doing this for 2 years. I was using Pub 2000, and I find a problem with copying and pasting from my website into an exsiting text box with pub 2003. It wants to make a new unsized text box Is there a fix? Thank you When you are ready to paste into Publisher, select Edit|Paste Special. From there you can chose unformatted text. (I don't particularly care for that feature but it's the way it is.) -- JoAnn Paules MVP Microsoft [Publisher] "docproc" <docproc@discussio...

SO/HQ Store Numbers Different
v1.3 I know this is bad, and I know how bad, but, I need some help here. 1. Location X has a local store Id of 3. 2. HQ Manager lists Location X with a store ID of 102. HQ Mgr already has a store Id 3 associated with a different location. 3. I need to change Location X's store Id to 102. 4. I will be made crazy by the end of the weekend with this one. :-) Thank you for any thoughts and ideas. -- Jocelyn Jocelyn, Try this; http://tinyurl.com/y87a24 I don't completely understand your problem without looking at your data, but reading both your post and the KB, you may only wan...

Replace Cell Address in A Long Formula
From time to time, I need to do some change in some very long formula. For example: =TRIM(IF(ISERROR(FIND(" ",B46,FIND(" ",B46,1)+2)),MID(B46,FIND(" ",B46,1)+1,LEN(B46)),MID(B46,FIND(" ",B46,FIND(" ",B46)+2),LEN(B46)))) I want to replace all the "B46" with "F18". Is there a easier way? Thanks. One quick and dirty way is to Edit>Replace B46 with F18. HTH Anders Silven "plumstone" <plumstone@discussions.microsoft.com> skrev i meddelandet news:29A2448B-1055-4847-9779-EA0FDDA5C05D@microsoft.com... > From...

Excel crashes when entering numbers and formulas into cells
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel This only happens with one file...I have tried switching users, bringing it over to Sun Office and then bringing it back, bringing it to 2004 and back, reinstalling 2008, etc. It works fine with Windows 2003. <br><br>I have also copied and moved tabs to a new spreadsheet and the problem still occurs after a few hours of productivity. <br><br>I think the file is corrupted as other spreadsheets work fine. Any thoughts on how to fix? <br><br>Thanks much! Andy <br><br&...

How do I expand the number of characters allowed in an Excel cell.
The default for maximum number of characters in a single Excel cell is 1024. If you enter more than that all you see is astricks (********). If you select the cell where the astericks show, you can read the information in the formula bar above. However, it does not print out well. I am importing web data from a Web Based Application into Excel and require a column for comments which normally falls below the default maximum. On those occassions it does not, it is a pain. I have run into this problem for quite a while and have finally decided to put it out there for the more advan...

Sheet size in workbook
Hi, is there a way to find out the size of each sheet in a workbook? Thank's ahead Precision: Is there a way to find out the number of bytes used by a sheet? Precision: Is there a way to find out the number of bytes used by a sheet? You could get kind of an estimate by looking at the current size, deleting the sheet and saving the workbook without the sheet--then subtract the sizes. But it's just an estimate. (and don't damage your real workbook--do it against copies!) Bobby wrote: > > Precision: Is there a way to find out the number of bytes used by a > sheet? ...

moving sheets between workbooks?
I made a copy of my workbook before adding to it for obvious reasons, but now when i copy the new sheet to the original wookbook all the formulars are poniting to my backup copy. how can i stop it doing this? or quicky make it use sheets from within the current workbook? Many thanks, Steve Your choice on what's easier for you. To correct the links *after* copying (moving) the sheet: <Edit> <Links> <ChangeSource> button, In the "ChangeLinks" window, link to the present WB, which in essence, is linking to itself, which re-directs the links to the WB you'...

multiple sheets lose printer configuration
When printing a workbook, the included sheets start to lose printer setup after the first 10-13 pages. The setup has the job going to tray1 for punched paper, and all works for the first 10-15 pages. Then the job continues, but to a different tray. I could not duplicate this with multiple pages from word doc or multiple pages of notepad. Only occurs in Excel (2K) . Has anyone had a similar issue that was specific to Excel 2K ? Thanks ...

Insert a space in all blank cells of the first column
Dear Experts: For some specific reasons I would like to enter a space in all blank cells of the first column of my spreadsheet called sheet 1. How is this done by using VBA? Help is much appreciated. Thank you very much in advance. Regards, Andreas First, this is usually a mistake to do. It'll mess up formulas like: =if(a1="","it looks empty","it doesn't look empty") If you have other formulas that use these empty cells--like: ='sheet 99'!a1 and you're seeing 0's where you don't want them, try modifying your formul...

Run a Hyperlink from another cell
I have a master spreadsheet and a cutdown spreadsheet. I have a hyperlink to a file in the master spreadsheet which I want to link to on the cutdown spreadsheet. At the moment if I click on the hyperlink on the cutdown spreadsheet it opens the master and goes to the hyperlink cell reference. I want to open the file from the original hyperlink when I click on the cell in the cutdown spreadsheet. I hope somebody can help. ...

Sheet Protection -- Content vs. FORMATTING
I know that in Excel 2003, it is possible to protect a worksheet making it possible for a user to change FORMATTING but NOT CONTENT. Is this a new feature to Excel, Starting with 2003? I just tried to do the same thing in Excel 2000 and I don't even see the option . Is it "ALL OR NOTHING" in 2000? Intro'd in Excel 2002 2000 is all or nothing. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It&...

how to skip the blank cells
hi, i copied some cells including blank, but after checking the skip blank button when i try to paste the blank cells are also copied. Select the copied cells including the blank... ....Select Edit then Go to (ctrl G) Click the special botton...select BLANKS click OK the performed delete command >-----Original Message----- >hi, >i copied some cells including blank, but after checking the skip blank >button when i try to paste the blank cells are also copied. >. > ...

Hide rows if cells = 0.00
I am trying to hide rows if any cell in a columm = 0.00. Example: If any cell in column D = 0.00 than hide the row containing that cell. Highlight your data, then from the menu bar select Data > Filter > Autofilter. Select the drop-down in column D and choose Custom. Use the drop-downs to set your selection to read 'does not equal' '0.00', then click OK. Those rows with 0.00 in column D should now be filtered out (they're still in your data, just not visible). --Bruce "dford" wrote: > I am trying to hide rows if any cell in a columm = 0.00. Exa...

Conditional formating an entire row vs. only one cell
I have 400 rows of data. There are four columns that contain any one of five different key letters (R, NR, O, U, NA). What I want to do is that everytime a letter such as NR shows up in any of the four columns, the entire row has a conditional format applied. When I do "Cell Is", the conditional format is only applied to the one cell that meets the condition even if I have the entire row(s) highlighted when I create the conditional format. I tried to find similar posts to help but have not been successful. Using Excel 2003. Highlight all your data (assuming you have start...

Print preview publisher file different pages appear
When I print preview a catalog publication I created with Publisher , it shows me different pages , not the pages I see them on my actual screen. As for the color issue I have installed the driver for printing , but how can I print ?? your quick reply is appreciated Are you using the catalog merge? Your explanation of your problem is not very clear. What you see on the screen if you are using catalog merge is only one merge block. When you look at print preview, you are seeing the total merge. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.mi...

Counting cells basesd on multiple criteria
Ok so i have two sheets In SHEET 1 column A i have a list of about 1000 different alarms. Then In SHEET 2 i have a log for 30 days with the alarms that occured in those thirty days. The log takes up 35465 thousand rows. Column D has the name of the alarm and Column C has if the alarm was an IN or OUT alarm. What i want to do is in SHEET 1 column 2 display how many times each of these 1000 alarms occured in the last 30 days but only when it was an IN alarm. I cant use something that refrences the name of the alarm i need a formula that can actually refrence the cell because it would be ...

Format Cells #15
is there anyway I can apply a custom format to cells that can show either whole numbers, or decimals points e.g A1=15 A2=15.5 I would like this to be the standard for all cells in my sheet. I know you can apply Custom formats using [ ] etc. Thanks Pete If you use the General format, it will show the number of decimals that you've entered in the cell. Pete wrote: > is there anyway I can apply a custom format to cells that > can show either whole numbers, or decimals points e.g > > A1=15 > A2=15.5 > > I would like this to be the standard for all cells in my...

MS publisher 2003 take 8 to 16 seconds in copy/past & Save event?
We are Newspaper Publishing press, and use Publisher 2003 for Unicode sindhi (arabic/R to L)... MS publisher 2003 take 8 to 16 seconds in copy/past & Save event? can any body help us in this case, why it is because of and what is its posible solution Regards, digitalsindh@yahoo.com ...

sum value of nonblank discontinuous cells
I'm trying to figure out in what way I can sum up the currency values of select cells in a row based upon alternate select cells having a value that is not blank. I'm trying to create a worksheet that will allow our group to order items and provide totals. So, if they do not enter anything into that particular column the adjacent one will stay blank. However, if they enter a quantity in column a then column b will provide a total. I've gotten that far. What I want to do now is to read those specific quantity columns and if they have a number (isnumber) then to add those...

Delete Sheet without warning message (no VBA)
As you know, when you do Edit | Delete Sheet, Excel pops up a warning that the sheet "may contain data" (doesn't it know? :-) and do you really want to delete it. I've been doing an iterative process of testing a program that inserts its results on a new sheet in the active workbook. Then I delete the sheet, change a parameter, and run the sheet again. I'd like to suppress the warning. I know how to do it in VBA, but is there any Excel option that will suppress that warning through the GUI? -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA ...

Cell Won't Accept Numerical Data
I'm trying to edit one cell, and Excel 2003 refuses to accept any changes. The problem with the cell is that it keeps mis-aligning. I've tried clearing it, and that works, but when I re-enter the data, it insists on entering the data the same way. I've tried editing in the fx function bar, but the program still insists on entering the same data the same way, that is, mis-aligned. I've tried turning off the enable auto complete for cell values, and that didn't help. Then I tried following some MS troubleshooting steps: double-click the cell, delete the data (so...

How do I set up CRM in a different language for users?
Our users are primarily Norwegian-speaking. Is it possible to setup MSCRM to display pages and dialogs in Norwegian? looks like the answer is not yet Question: What other languages are supported with Microsoft CRM version 1.2? Answer: German, Danish, Dutch, French, Spanish, Portuguese, International English, and Italian. -- John O'Donnell Microsoft CRM MVP http://www.microsoft.com/BusinessSolutions/Community/CRMFaqLanding.aspx "Rolf Barbakken" <Rolf Barbakken@discussions.microsoft.com> wrote in message news:77A8B04E-D074-4759-B6F5-C93D4E440AF5@microsoft.com... >...

How can I share information between multiple sheets in Excel?
New question: I know now how to use "=sheet1!a1" to copy cells from sheet 1 to sheet 2,3 etc. Is there is a formula that will allow me to enter data into sheet 2, and have it copy automatically into sheet 1 and 3, or into sheet 3 going to sheet 1 and 2? So that I can enter the data into any sheet and have it transfer to all sheets, rather than always from sheet 1 to sheets 2 and 3. Nothing built into excel (except for the formula you showed). But I'd be very hesitant to separate my data. I'd do my best to keep all the data on one worksheet. You can use Data|Fi...

Insert a new Worksheet AFTER the existing (Active) sheet
I want to insert (or add) a new Worksheet to my existing Workbook, but I want it to appear AFTER my existing (Active) Worksheet. The default places it BEFORE my current sheet. This places Sheet2 before Sheet 1. I'd like the new sheet (Sheet2) to be added AFTER Sheet1. Is this possible? This should give you a few ideas: http://support.microsoft.com/default.aspx/kb/288402 -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Ronnie" wrote: > I want to insert (or add) a new Worksheet to my existing Workbo...