Persistent References to Data in other Sheets

Is it possible to have a cell in a worksheet reference a cell in another worksheet in the same workbook, and if the referenced cell is deleted, reference the new cell, rather than #REF

For examle, if I have something in Cell A1 on Sheet1, I can use =Sheet1!A1 on Sheet2 to display the contents of the cell.  However, when I delete Row A on Sheet 1, so I can put new data into it, my reference changes to =Sheet1!#REF

How can I force the reference to stay =Sheet1!A1
0
anonymous (74722)
1/20/2004 4:06:06 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
667 Views

Similar Articles

[PageSpeed] 37

"Cyndrax" <anonymous@discussions.microsoft.com> wrote in message
news:4E408DC8-803B-47AE-8320-C6B8A561B22C@microsoft.com...
> Is it possible to have a cell in a worksheet reference a cell in another
worksheet in the same workbook, and if the referenced cell is deleted,
reference the new cell, rather than #REF?
>
> For examle, if I have something in Cell A1 on Sheet1, I can use =Sheet1!A1
on Sheet2 to display the contents of the cell.  However, when I delete Row A
on Sheet 1, so I can put new data into it, my reference changes to
=Sheet1!#REF!
>
> How can I force the reference to stay =Sheet1!A1

Why do you delete the row rather than just clearing its contents? To clear
contents, select the row and press the Delete key on the keyboard.

To answer your question literally, you would use
=INDIRECT("Sheet1!A1")
But it's better to avoid having to use this - by clearing, as above.


0
Paul
1/20/2004 4:13:43 PM
I would just use the delete key, but this is going in an Excel file that will be manipulated by others.  This issue has already come up once, and I can't imagine it getting better

Thanks for the tip
    
     ----- Paul wrote: ----
   
     Why do you delete the row rather than just clearing its contents? To clea
     contents, select the row and press the Delete key on the keyboard
    
     To answer your question literally, you would us
     =INDIRECT("Sheet1!A1"
     But it's better to avoid having to use this - by clearing, as above
    
    
     
0
anonymous (74722)
1/20/2004 4:56:10 PM
In that case, why not unlock the cells where users should be able to enter
data and then protecxt the worksheet? That way they can type in numbers and
use the delete key to clear them, but cannot delete the row.

"Cyndrax" <anonymous@discussions.microsoft.com> wrote in message
news:BF091110-2E4B-4DC2-9C2A-3188D5DA52C3@microsoft.com...
> I would just use the delete key, but this is going in an Excel file that
will be manipulated by others.  This issue has already come up once, and I
can't imagine it getting better.
>
> Thanks for the tip!
>
>      ----- Paul wrote: -----
>
>      Why do you delete the row rather than just clearing its contents? To
clear
>      contents, select the row and press the Delete key on the keyboard.
>
>      To answer your question literally, you would use
>      =INDIRECT("Sheet1!A1")
>      But it's better to avoid having to use this - by clearing, as above.
>
>
>


0
Paul
1/20/2004 5:09:57 PM
I remebered having troubles with that in the past, but I must have set some option or lock incorrectly, as it appears to be working perfectly now.  Thanks
    
     ----- Paul wrote: ----
    
     In that case, why not unlock the cells where users should be able to ente
     data and then protecxt the worksheet? That way they can type in numbers an
     use the delete key to clear them, but cannot delete the row
    
0
anonymous (74722)
1/20/2004 5:46:10 PM
Reply:

Similar Artilces:

Show actual values added instead of cell references? (=A1+A4 shows as =10+2 in cell)
Is this even possible? I have the following: A1 =A2 (Shows result of 10) A2 10 A3 A4 =A5 (Shows Result of 2) A5 2 A7 =10+2 (Shows result of 27) Is there a way to show the formula in A7 as =10+2 instead of having it =A2+A5 ??? Thanks a bunch! On Dec 29, 1:24=A0pm, Naji <najisaa...@gmail.com> wrote: > Is this even possible? > > I have the following: > > A1 =A0=3DA2 (Shows result of 10) > A2 =A010 > A3 > A4 =3DA5 (Shows Result of 2) > A5 2 > > A7 =3D10+2 (Shows result of 27) > > Is there a way to show the formula in A7...

Mappings Do Not Persist Correctly in Customization File
I have noticed that when I remove default mappings that ship as part of CRM (ie: account address information to contact) and save these customizations, they do not persist when importing into a new environment. I think the reason for this is nothing is removed when doing an import into a new environment, but only added. Can someone confirm this is the case? If so, is there a plan for a more robust import process where one can remove items in 5.0? This becomes very difficult to maintain for system integrators and ISVs. Don't know about future versions. But I can confirm that is ...

Copy Data From Chart to Chart #3
I have to create separate charts for ten employees. I want to have all the data on one sheet tab. Create the chart on another tab for employee 1, format the chart the way I want and be able to add the data for employee 2, 3, 4, etc. to each tab, using the same format for chart 1 so they all match. So there should be one tab for raw data and 10 tabs for each employee. I’m using Excel 2000 Thank you ...

Persistent Connetion
I am wanting to put in place some best practices to try and optimize a db and am working on the 'Persistent Connection', as discussed in http://www.granite.ab.ca/access/performanceldblocking.htm Now, Tony starts off talking about opening a bound form in hidden mode using DoCmd.OpenForm "frmKeepOpen", acNormal, , , , acHidden Then he goes on to talking about referencing the fields ... using forms!GlobalOptions!goReportBandingColour and forms!GlobalOptions!goCorporateLogoPathandFilename I am just a little confused since the form he originally opens was call...

Bank Rec out of balance with Cash account on Balance Sheet all yea
New client. Last bal was 12/31/2007. Beginning 2008, out of balance first by $1,600+ which increases to $15,000+ by 12/31/2008. Starting with month of Jan, 2008, I have matched every Bank Trx and every Purchasing Trx to the Jan, 2008 TB to be sure they made it through to GL. There are no JE entries in cash that weren't already reversed and put through Bank Rec. The ckbk stmt balance is correct. Need help knowing where to look next. -- Katie Did Consultant There has to be some transactions in either Check Rec or GL Cash that is not in the other one. For example, when we post a b...

Chart repeats data when printed pls help !
I have a spreadsheet that consists of a long list of data on the left with a simple x-y scatter chart on the right - both list and chart are longer than one page, and therefore must be printed across several pages. When I print the spreadsheet, the chart on each page prints the first two rows of data from the beginning of the chart, so for example if the data runs from 1-100, the first page chart printout shows 1-20, the second page shows 1-2 then 13-20, the third page shows 1-2 then 23-30. Could it be something to do with excel printing out the x axis on each page, given that the x axis is ...

Reference controls on a subform
How do you set focus to a control on a subform, from the main form? -- Learning as I go... Appreciate your assistance! You need two VBA code steps to do this, the first to set focus on the control that holds the subform, the second to set focus on the control in the subform: Me.NameOfSubformControl.SetFocus Me.NameOfSubformControl.Form.NameOfControlOnSubform.SetFocus A macro action can be used instead if you want, using the SetFocus action. But you still need two steps. -- Ken Snell http://www.accessmvp.com/KDSnell/ "Charlie Lou" <Charli...

Select Rows dependant on certain field data
How can I select certain rows in a spreadsheet based upon the text foun in one column? To explain: All rows have a column that contains tex "immediate" or "ongoing" or "closed". How can I select only the row that contain the word "immediate" in that column? When I say select a mean select as in ready to 'copy' or 'cut' tha data ready to be pasted elsewhere. I know what I mean :confused -- Madd ----------------------------------------------------------------------- Maddy's Profile: http://www.excelforum.com/member.php?action=getinf...

Finding the Cell Reference for the minimum value of a data list.
I am trying to figure out how to automatically refernce a location of a cell (ex. C5)which has been calculated to be the minimum value of a set of data. If I can reference the row of this cell absolutely, then I want to use that value as the beginning data reference point for both the X and Y data values of an XY scatter plot. You can reference it using the MATCH function with INDEX or OFFSET: I.e., the starting cell might be referenced as: INDEX(C:C, MATCH(MIN(C:C),C:C, 0)) while the range to plot could be referenced as: OFFSET(C1,MATCH(MIN(C:C),C:C, 0)-1,0,numrows,numcol...

If data present, then display graph.
Is it possible for one to display graphs based on whether data is present in particular cells? For instance, I wish to display the information of cells A1 to A8. A9 is blank. I create a graph to reflect the information of cells A1 to A8. I now fill in information in cell A9. I now wish to display the information of cells A1 to A9. Is it possible for me to create a formula for the graph to automatically represent data from cell A9 when data is present and automatically exclude it when data is not present? Hi, You can create a dynamic graph based on defined names. http:/...

Quering the reference tree
Hello, I am wondering if there is a nice way how to make a SELECT that would return full history of a tree-structure. Here's my table field list: 1) ID (int) - unique identifier 2) _parentID (int) - reference to parent entry in this same table 3+) [number of other tree node fields] Let's say, the data in the table are as follows: 1; null; abc 2; null; def 3; 1; ghi 4; 3; jkl I'd like to make a SELECT statement that would return all rows, starting from ID=4, that show the tree nodes, down to the last parent: 4; 3; jkl 3; 1; ghi 1; null; abc I'd like to...

How do I switch from 1,2,3... back to A,B,C... column reference na
I noticed in Excel 2003 both the rows and columns are numbered. As far creating and maintaining Excel formulas, I'd prefer the previous column labeling. Does anyone know how to switch back to the A,B,C labeling once a file has been saved and updated to the latest version of Excel? C, tools, options, general, uncheck R1C1 ref. style -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "C. White" <C. White@discussions.mi...

Exell data analysis
I am currently trying to analysis guite a large amount of data. What I have is a colum of dates and a corresponding temp and Humidity value. What I need to do is get an average for each day. Each day consists of approx 100 values and I have 6 months worth of data. If anyone assist me if find a quick way of doing this I would appricicate it. Regards Oliver Cullinan Hi I assume your data are on sheet Data in range A2:D18000 in columns Date, Time, Temp, Humidity Add a sheet Averages with columns Date, Temp, Humidity Into A2 enter start date, and fill down for all 6 months Into B2...

parenthesis on reference cause failure
Im using VBA to insert the following formula =IF(ADDRESS(5,COLUMN(Type))="HL",$F$25,$F$22) the probelm is that the reference created by ADDRESS(5,COLUMN(Type)) returns the correct reference but in parenthesis the result in the formula auditor look like this: =IF("$C$5"="HL",$F$25,$F$22) which returns a false even if HL is in C5 replacing the formula with this works fine =IF($C$5="HL",$F$25,$F$22) however, I need to creatte the reference. Any idea how to get the reference using the address function. Thanks You have to wrap it inside an INDIRECT func...

Reference Variable in multiple Subs
Reference Variable in multiple Subs Hello, I=92m trying to accomplish the following: Sub WriteQuery() Dim strSQL as String strSQL =3D _ =93Select * from myTable=94 End Sub Sub RunQuery() Docmd.RunSQL(strSQL) =91won=92t run because variable not defined in immediate sub End Sub It=92s a simplified example, but I=92m trying to reference a variable (that=92s set in one sub) in another sub=85can I do this in some manner? Thanks, alex Try declaring strSQL at the very top of the module as Public or Private variable. Option Compare Database Option Explicit Dim strSQ...

Registry Persistence
Hi there, I have a small problem with persistence of my Hive registry, and every time I do a google search I seem to get offers for all sorts of wonderful creams that will make my persistent hives go away. Anyway, I seem to have strayed from the point. I have an arm-based system with NOR Flash and SDRAM, on power up I have the OS (Win CE 6.0 R3) load from Flash into SDRAM and the upper half of the Flash becomes a file store. I also have the Hive registry created in the Flash file system. At this point everything is wonderful, because up to now I've wanted the registry settin...

Numeric Data Entry
What do I put in the validatation rule to assure that only 9 digits are entered. Validation test would say "you can only enter 9 digits in this field" On Sun, 3 Jan 2010 11:07:01 -0800, rciolkosz <rciolkosz@discussions.microsoft.com> wrote: In table design view, select the field and for the Validation Rule enter: Between 100000000 And 999999999 I'm sure you can do the Validation Text yourself. -Tom. Microsoft Access MVP >What do I put in the validatation rule to assure that only 9 digits are >entered. Validation test would say "you can ...

Function to add zeros to IP address data
Hi all, I work in a school and we have an excel spread sheet with many ip addresses that have been input into it incorectly. To make it work i need to first find the addresses without the zeros and add them where needed. For example; the need to look like this -172.22.033.010 not like this 172.22.33.10 or this 172.22.33.010 or this 172.22.033.10. In other words the last two octets have to have 3 digits. I would like to have a function that will find the missing zeros and add them. I have been working on if statements and the mid and replace functions. Any help wold be appreciated,...

2007 not updating data unless manually selecting data update from
I have data link between two 2007 workbooks, cells in book1 get data from cells in book2. However, when opening book one,it does not prompt user if data should be updated. I have to manually go to data menu -> edit links -> update values. I need to be able to automatically update book1 every time it opens. I have tried setting the properties in Edit Links so it does so, but it does not work. The book1 is not password protected and niether is book2. Thanks in advance. Imran I believe the answer can be found at: http://office.microsoft.com/en-us/excel/HP101023481033.aspx ....som...

Data tables
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Some questions regarding data tables (the functionality available under Data->Table...): <br><br>- I can't find any documentation in the Excel help. Am I just missing it, or is this functionality not documented at all? (a related question: does the Excel help contain a list of all menu entries together with relevant documentation?) <br><br>- The row/column input cell must apparently be on the same sheet as the data table, otherwise the arcane error message &quot;Input cell reference...

Using Cell Data s Sheet Reference
I would like to have sheets named after a certain text string in a cell and refernce them in formulas For example: I have a sheet named '123Main' and a sheet named 'Main' On the 'Main Sheet' column 'B' is labelled 'Address' Cell B5 has text data '123Main' I want to be able to reference from Main!C5 a formula as such: ="whatever text is in cell Main!B5 is the sheetname to reference a cell on that sheet" I hope it is clear what I'm trying to do.......thanks. -- StompS Portland, OR http://www.geocities.com/pdxinvestr/Stomps.htm...

Change cell reference in formula
Hello, i have certain formulae of the type: =SUMPRODUCT((TOTAL!$A$1:$A$150="BTS")*(TOTAL!$B$1:$B$150="A")*(LEFT(TOTAL!$R$1:$R$150;3)="A.T")) I would like to change the cell reference to 1 to 1000 (instead of 150 and i'd rather not do it by hand everywhere....is there a way t replace all 150's to 1000? Thanks in advance, Georg -- Message posted from http://www.ExcelForum.com Hi you may use 'Edit - Replace' for this. Search within 'Formulas' and change '$150' to '$1000' >-----Original Message----- >Hello, >i ha...

using the same name reference in multiple worksheets
Hi all, Dumb question, but I can't seem to get it to work... 2 spreadsheets ... In spreadsheet 1 (Year 2001), in formulas within that spreadsheet refer to, say, "Total" as ='Year 2001'!$10:$10. In spreadsheet 2 (Year 2002), in formulas I refer to "Total" ='Yea 2002'!$3:$3. But I can't seem to use the "Insert/Name/Define" function properly s that 2 ranges in separate worksheets refer to the same 'name' (if the were the same line, ie. both line 10, I would be fine). Any ideas, arghhh... -- eric -------------------------------...

Data Valadation
Hi, I have a user that has created data valadation from a list. Now she wants to change the font size in the drop down box. I have tried changing the font in the original list. She insists on the valadation not a combo box. TIA ray AFAIK, it's not possible. One workaround is to increase the zoom when the validation is activated. MVP Debra Dalgleish has some code for that: http://www.contextures.com/xlDataVal08.html#Larger HTH Jason Atlanta, GA >-----Original Message----- >Hi, >I have a user that has created data valadation from a >list. Now she wants to change the fon...

References to sheets from cells???
Cel A2 contains: G:\Directoryname\workbook.xls] Cel A10.. etc contains worksheet names SheetA SheetB SheetC .... In B10 I want to have the content of SheetA A1 cel =ADDRESS(1;1;1;TRUE;$A$2&$A10) gives 'G:\Directoryname\workbook.xls]SheetA'!$A$1 =INDIRECT(ADDRESS(1;1;1;TRUE;$A$2&$A10)) displays the value in that field. *Now comes my question:* In C10 I want to get the sum from the range $B$3:$B$303 from SheetA Which formule delivers me that result??? With kind regards Pvd -- Message posted from http://www.ExcelForum.com =SUM(INDIRECT(A2&A10&"!B3:B1...