Maintain cell references when data is deleted

Hello,

I have workbook that has formulae that reference cells in a sheet that
is updated by first deleting all the existing data and then the new
data is input in the exact same configuration.  When the old data is
deleted all my formulae go to #ref.  Is there a function that will
maintain the reference to the cell even when the old data has been
deleted?

Example

Sheet1 cell B5  formula is  =+Sheet2!B14-Sheet2!D14.  When sheet 2
data is deleted and replaced with new data  Sheet1B5= 
Sheet2!#ref-Sheet2!#ref.  I would like to maintain the cell references
on sheet 1

Thanks
JBESr
0
jbesr1230 (21)
9/17/2003 12:44:28 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
260 Views

Similar Articles

[PageSpeed] 28

JBE,

=INDIRECT("A2")

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"JB" <jbesr1230@hotmail.com> wrote in message
news:35b46061.0309161644.d3bb914@posting.google.com...
> Hello,
>
> I have workbook that has formulae that reference cells in a sheet that
> is updated by first deleting all the existing data and then the new
> data is input in the exact same configuration.  When the old data is
> deleted all my formulae go to #ref.  Is there a function that will
> maintain the reference to the cell even when the old data has been
> deleted?
>
> Example
>
> Sheet1 cell B5  formula is  =+Sheet2!B14-Sheet2!D14.  When sheet 2
> data is deleted and replaced with new data  Sheet1B5=
> Sheet2!#ref-Sheet2!#ref.  I would like to maintain the cell references
> on sheet 1
>
> Thanks
> JBESr


0
Earl
9/17/2003 12:47:54 AM
Reply:

Similar Artilces:

Insert new row as cell contents change
Insert new row as cell contents change. After importing data I have a spread sheet with a column that contains a series of alpha numeric characters. At various random intervals in this column the contents change. EG rows 1 to 4 could contain ABC, then rows 5 to 15 could become 222. I am looking for a method to insert a blank row automatically between the rows were the contents change. Many Thanks Geo George If you are familiar with VBA the code below will do what you want. Preselect the column of data first Sub InsertRowAfterValueChange() Dim myCell As Range Dim sCurrVal As String ...

Clear cell value without macro?
Is there any way to clear a cell value when the sheet is selected or the workbook opened without using a macro? Ed Click on the cell and press the <delete> key. Is this what you mean? Pete On Dec 9, 1:44=A0pm, Ed from AZ <prof_ofw...@yahoo.com> wrote: > Is there any way to clear a cell value when the sheet is selected or > the workbook opened without using a macro? > > Ed > On Dec 9, 1:44 pm, Ed from AZ wrote: > > Is there any way to clear a cell value when the sheet is selected or > > the workbook opened without using a macro? On Dec 9, 7:02=A0a...

VBA to add and remove text within cells
Hi, I have a field named "Postal" at the top of column F that always include a number with 5 digits then a city name then a region name, such as "11090 CARCASSONNE Linguadoca-Rossiglione". I need to create a program to have this field changed as following : "F-11090", then copying "CARCASSONNE" into the City field which is empty (column G). The city name is always starting just one space character after the postcode, same thing for the region name, it always starts one space character after the city name. The region has to be removed completely. ...

Reading ranges and copying data from Excel when it is not open
Is there a way in code to copy data out of an Excel file even if Excel is not up and running? If Excel were open, I'd copy, say, the first 30 rows and paste the info to PowerPoint. Then, since rows 1-5 are for column headings, I'd hide rows 6-30 and copy a new range which would look involve 30 rows, but since rows 6-30 were hidden would be 1-5 and then 31-55 as a contiguous block. I am trying to do this in VBA without opening Excel and instatiating objects, etc. Is it possible? What VBA commands would I use? You would need to treat the excel file as a database and use ADO to g...

How do I maintain formatting?
I'm using Word 2K and when I save as a .txt file all formatting is lost. I use a Text to HTML converter prior to uploading to a website. How do I go about preserving the formatting so that the web doc looks like the one I created in Word? Best regards, -- VHH III Txt is plain text and that does not support formatting. Html does support formatting, but its requirements are entirely different from those of a Word document and so there is little correlation between the two. You can view what the Word document will look like in html by using the Web view. -- <>&...

empty items in deleted items folder
Multiple items appear in Outlook Deleted items folder, no sender, receiver, or message. Some happen when I am not on my computer. Changed passwords, checked virus scan (multiple). Tried Microsoft reinstall fix, problem still exists. What version of Outlook are you using? What anti-virus software are you using? Are you using the Hotmail connector? -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.html Author: The Lawyer's Guide to Microsoft Outlook 2007: http://tinyurl.com/ol4law-amazon "Ra...

How do I set text to top of cell next to wrap text in Excel?
In the cell next to a wrap text cell, I want the text to appear at the top of the cell, not at the bottom. Ex. cell A1 is a title for that line and cell A2 is a specification for the title. The specification is much longer than the title so I use wrap text, but when the row height is elongated to show all the text, the title ends up even with the bottom of the spec. How can I make the title even with the top of the cell? Format-->Cell, Alignment tab. Set vertical alignment to top. ************ Anne Troy www.OfficeArticles.com "Carpenter Gary" <Carpenter Gary@discussio...

Help with data points in line graph in Chart function in Excel.
I added data to an existing line graph and the data points do not align properly with the values on the Y axis. For example, a data point valued at 119 lines up between 400 and 800 on the Y axis. I know there's a way to fix this, but just can't find it. Thanks! ...

Can't install CDO (Collaboration Data Objects)
Trying to install CDO under Outlook/Office 2003. I go to Advanced Customization under the Office setup, find Outlook, and change Collaboration Data Objects to Run from my Computer, then click Update. It starts to work, then pops up the message: Error 1311. Source file not found: X:\L9561403.CAB. Verify that the file exists and that you can access it. The CD does have the file on there (marked hidden). I copied all of the install files to a local directory and unmarked them hidden just to try, but no change. This is driving me crazy - what could possibly be wrong??? Never mind, ju...

How do I fix a formula in a cell?
I'm using Excel 2000. I have 2 sheets in my work book. Cell A1 in Sheet 2 has a simple formula: "=Sheet1!A1". Thus the data in sheet 1 A1 is always replicated in Sheet 2 A1. However, if I "cut" Sheet 1 A1 and "paste" somewhere else in the book, Sheet 2 A1 still reads the original data in Sheet 1 A1 and the formula in Sheet 2 A1 has changed to refer to the new location of the pasted data. Normally, this is not a problem but in this case I want the data in Sheet 2 A1 to read whatever is in Sheet 1 A1 at all times. I do not want the formula "=Sheet1!A...

Formula to determine Cell Color Format (Shading)
I was wondering if there is any formula or macro to use to evaluate cells that contain a certain format. I want to create a Yes/No column for when cells are formatted with a color so that I can sort a large amount of data based on the color the cell is shaded. Ideas appreciated. I don't want to do find>all by format that is available on the Finder menu because I cannot populate the other column based on that and I want to be able to frequently evaluate only rows of data contain the cells colored a certain way. you can use a macro that will analyze the color like: if sheets(&quo...

Setting a reference to a libray in code
Hi I am trying to reset references to Excel and Word from Access 2007 i.e. code developed on 2007 with office 2007 but when dstributed to users the reference is broken because they are on say office 2000. I have read the link provided in previous posts on this subject but it doesnt seem to be of any help to me. What i do is look through the references to see if any are broken (i already know the excel one is though by looking at tools > references) so For each ref in References if ref.isbroken then 'fix the reference end if next I was then going to fix the br...

User Data
Is it possible to access the user table in MSCRM? I want to capture some more details for given users say his/her competency level. I am not able to add a field via Deployment Manager. Can we do it using a custom build application? Competency has to be stored for each user. So I would require having competency level corresponding to each user. Hi, Modifying the fields for userinformation isn't possible in this version. In mscrm 3.0 it will be possible. You will have to wait untill the crm 3.0 release or built your own application. You can use the systemuserid for making the ma...

line spacing within a cell
When using "center across selection" with Wrap Text, how do you get a full line of space between information you are placing in the same cell that I have expanded Thanks Kristi I'm not sure what you want, but if you're looking to place a hard return after a line of text to get that blank space, use Alt+Enter. HTH -- Michael J. Malinsky "Kristi" <anonymous@discussions.microsoft.com> wrote in message news:5d4401c3e5c3$7abd5e00$a001280a@phx.gbl... > When using "center across selection" with Wrap Text, how > do you get a full line of spac...

copy data vba
Hi, I need a macro that searches the client name in b2 in other workbook that has a sheet for each client. So the name=B4s cliente in B2 should match matches with the client=B4s name sheet and copy the data of row a2 to e2. Is this possible? Thanks CN = Range("B2").Value Workbooks("Other Workbook.xls").Worksheets(CN).Range("A2:E2").Copy Range("C2:G2") HTH, Bernie MS Excel MVP "1234" <altachicasaudaces@gmail.com> wrote in message news:8d909f9d-9503-40cc-ad20-ca3a1e9b82ba@k19g2000yqc.googlegroups.com... Hi, I...

count cells in a document, but excude cells with a "0"
I want to count the number of cells that have a value greater than "0", but not use a range. The cells that will be counted are every other cell in that column. EX: 45 22<-- 18 6<-- 9 17<-- 2 0<-- so my answer would be "3", the amount of cells that have a value greater than "0". the arrows are not on the work sheet they are just used to point out which cell I need counted. You have not given any cell references where your data is but a fomual like this should be close... =SUMPRODUCT(--(MOD(ROW($A$1:$A$10), 2) = 0), --($...

Maintaining Links
All, I have a db that is linked to a table of another db. They are kept in one folder. Whenver I move the folder to another comp or change the location I need to re-link. How do I maintain the link so that I don't have to relink every time I move the folder? Thanks in advance. What you are asking is "How can I be in 2 places at the same time?" Do I really need to answer that? On the other hand, you can have the front-end app automatically ask for the new location. Have a look at the following: http://www.mvps.org/access/tables/tbl0009.htm -- Arvin Meyer, MCP, MVP http://...

Cell protection in Excel
Help please! I use Microsoft Excel in the latest two versions of Microsoft Office to test pupils at school in mathematics and English language. This involves using hidden formulas and locked cells with answers in - all protected by a password. It doesn't work! This is even the case when access is denied to the individual cells. The youngsters just insert the coordinates of the answer cells into the 'Name Box' (top left), go to the cell and copy the answer out into the answer cell. Am I doing something wrong? Is there any way of preventing access by this method or copyin...

how to keep data mods in a doomed transaction
If i have a doomed (or will be doomed) transaction and i want to write the error and context (like proc name and parameters) to a table, how can i get that write to survive the ensuing ROLLBACK? I solved this but wonder if there is another way. I couldn't figure an all TSQL solution. Is there one? Here is what i did. I created a SQLCLR proc that accepts the parameters necessary. It first connects to the Context Connection. Once there builds a connection string based on that information: "SELECT 'Data Source=' + @@servername + ';Initial Catalog=' + d...

How to check if each value in a cell range is contained in a second cell range?
For example, suppose I have a range 1 2 3 4 5 and a second range 2 3 6 6 7 Is there a formula that would return a value of, say, 1 for each valu in the first range that is NOT contained in the second range. For example, here, I would want to return 1 0 0 1 1 Thanks very much, Kat -- ModelerGir ----------------------------------------------------------------------- ModelerGirl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=636 View this thread: http://www.excelforum.com/showthread.php?threadid=50664 On Mon, 30 Jan 2006 22:06:04 -0600, ModelerGirl <Mo...

Calculating page/report sums using calculated data from subquery
Here is a scenario from my report: The report will grab data with a main query into a parent row Depending on the data, a child row may exist, if it does the report will display it The child row will use data from the parent row in order to do a calculation in the child All of this works just fine and the report data is correct. Now, what I am having issues with is doing the page/report sum calculations. The requirements dictate that I need to create a sum of both the parent column and also the child column in the totals. I have been trying to do this all day and cannot see...

how to manually copy text from one cell to end of other text cell
probably stupid question, but anyways... I want to manually copy the content of 1 text cell to the end of another text cell, on the same sheet. For that, I copy the content of the source cell (ctrl-C), I move the cursor to the target cell and I hit "F2" to position the cursor at the end of the text of the target cell. But as soon as I hit "F2" the text of the source cell is "unselected" and hence cannot copy the source text... help much appreciated! Hi, do this when you select the cell to copy don't press Ctrl C, you will see the text in t...

View Excel Cell Location
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I'm new to the Mac, previously used PC. When I open Excel, the row at the top are numbers instead of letters. Can't find anything in preferences, it's making it hard for me to understand the calculations. Thanks. It sounds like you were on the right track but gave up a bit too soon ;-) Excel> Preferences> General, remove the check on "Use R1C1 reference style". HTH |:>) Bob Jones [MVP] Office:Mac On 2/10/10 7:13 PM, in article 59bb29eb.-1@webcrossing.JaKIaxP2ac0, "Peggy_F@of...

Cells will not calculate
I am using the replace feature to modify a rather long formula. I have to modify it in each column, abour 30 cells per column. Anyway I am replacing $D with $E = the replace feature works perfectly but after the change the cell does not calculate and return the data... HELP. It is far to tedious to make the chabge manually. =IF(ISNA(MATCH("N12",Schedule!$D$49:$D$102,0)),"",INDEX(Schedule!$B$49:$B$102,MATCH("N12",Schedule!$D$49:$D$102,FALSE))) Even with calculation set to manual, the cell should recalculate after making a find and replace change. Your form...

Replace a spreadsheets named cells/ranges with exact cell address.
I have a named range (A1:A6) which is called MyRange. Lets say each cell is populated with a number A1 = 1, A2 = 2. A3 = 3 etc. In the row (B1:B6) beneath it, I have entered =MyRange in each cell. As such the values in B1:B6 mirror those in A1:A6. That is, even though I use MyRange in each cell in Row B, Excel knows which cell to pull from in rowA to correctly populate the cells in Row B. Now If I use the Macro below to delete the named ranges I get the following range in each of the RowB cells =A1:A6. Once again Excel knows which cell to pull from in Row A to populate cells in Row...