how to prevent a formula in a summary sheet from automatically updating when a row is addedto the referenced sheet

I have a workbook set up that consists of several sheets and a summary
sheet that refers to the third row on every other sheet.

The worksheets that are referenced contain dated information that is
sorted with the most recent entries entered in row 3 and these are the
entries I need to track in the summary sheet.

When I insert a new row 3 in these worksheets, the formula in the
summary automatically updates to line 4 and I need it to always
reference row 3, the newest entry.

This should be simple but I cannot find the solution.

Any ideas?

Thanks,
krisp1950

0
krisp1950 (1)
1/12/2006 7:49:04 PM
excel 39879 articles. 2 followers. Follow

2 Replies
421 Views

Similar Articles

[PageSpeed] 51

You could use the INDIRECT function.  For example, if you are referring
to Sheet2!A1 then,

=INDIRECT("Sheet2!A1")  (this is for A1-style) or

=INDIRECT("Sheet2!A1",FALSE) (this is for R1C1-style)

HTH

Steve


-- 
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7571
View this thread: http://www.excelforum.com/showthread.php?threadid=500806

0
1/12/2006 9:30:56 PM
<krisp1950@gmail.com> wrote in message 
news:1137095344.278535.65070@g47g2000cwa.googlegroups.com...
>I have a workbook set up that consists of several sheets and a summary
> sheet that refers to the third row on every other sheet.
>
> The worksheets that are referenced contain dated information that is
> sorted with the most recent entries entered in row 3 and these are the
> entries I need to track in the summary sheet.
>
> When I insert a new row 3 in these worksheets, the formula in the
> summary automatically updates to line 4 and I need it to always
> reference row 3, the newest entry.
>
> This should be simple but I cannot find the solution.
>
> Any ideas?
>
> Thanks,
> krisp1950
>
These 2 work as long as you don't insert a row before row 1.
=INDEX(Sheet2!A1:A3,3)
=OFFSET(Sheet2!A1,2,0)
The Index range will grow as rows are inserted but you will get the 3rd row.
Offset is a little cleaner, but has 2 issues that bug me.  The first is that 
if
you trace dependence from Sheet2!A3 it will not know that the offset
uses it.  Also offset will fail if it refers to a cell in a closed file.
kcc


0
1/13/2006 2:33:17 AM
Reply:

Similar Artilces:

Excel VB-Copy formula down until adjacent cell (left) is blank?
Here is exactly what I am trying to do through VB in Excel: Weekly data pull fills colums A:G. Row count is always different. I am modifying the data pull through VB, and I have a VLOOKUP formula in cell H2. What I want VB to do is copy that formula down column H to the last row (with data) each week. I guess I want it to be dynamic so that as rows decrease/increase the formula is only copied down to the final row/record. I know someone out of this smart group will know how to do this! Thanks in advance! Tony (pseudo code) in a macro .... dim lngLastrow as long dim rngTarget...

Need Help with Formula #3
I need help trying to come up with a formula for a friend of mine. This is what he wants -- Using any 9 numbers -- he wants Excel to come up with every possible 3-digit combination of numbers that are divisible by 7. Is there anyone who could assist me with a formula that will perform this calculation? Can Excel do such a calculation? Thanks so much for any assistance you can offer. In A1, enter the number 7. Select A1:A142. Edit>Fill>Series, Step value 7. Format>Cells>Custom, "000". If with "using any 9 numbers" you mean you don't want any zero...

Strike-through an entire row.
OK... I need to know if there is a way I can strike through the text on a row by highlighting the row with the defunkt information and activating a macro. Let's see if I can give an example. Say I'm entering data in, row by row... Each row's data is SIMILAR but not the same. Now let's say that apon review I notice that one of the row is incorrect. I need to be able to highlight the row (Presumably by selecting the corresponding number on the left hand side {1,2,3 etc}) then I need to run a macro (that will eventually be tied to a button) and then be done the macro would tur...

Q: Referencing named cells in external worksheet ?
Using Excel 2002. I have a workbook with 12 worksheets (one for every month of the year), wherein a lot of the information is looked-up (using VLOOKUP) in simples arrays. I saw no point in implementing the arrays as a 13th worksheet, because I will have a yearly version of my monthly worksheets in one workbook (so one for 2003, 2004, etc). If I change the array(s), I want them to be reflected in all referencing cells. Problem: If my workbook containing my arrays (called "Global") is loaded, I have no problem and the references to it read as: (blabla) 'Global.xls'!Roster ...

Update Prices Online... not updating stock prices
Hi all, I just upgraded from Money 2002 to Money 2004. In Money 2004 when I do a "Update prices Online" to update the prices of my stocks, this task runs to completion with out error but the actual stock prices are not updated. The "Read Call Summary Messages" for this task said it completed successfully. I even tried turning off my firewall. I went to "Update prices manually" and there was no new entries/prices for any of the stock. I verified my stocks still have the correct stock symbol listed. I also selected "Pick Quotes to Download" and...

Problems after kb890830 and kb978542 updates
After windows Vista installed kb890830 and kb978542 I noticed 4 problems so far. 1. MSN Service not available, error code: 800706b5. (This happened with previous updates and I fixed the issue by doing a system restore to a previous date.) 2.When I started internet explorer (ver 8.0.6001.18904) I got a message my default search engine was corrupted by another program. 3.I tried the usual system restore and found there were no restore points available 4. I had some unknown problem trying to log into this site. It kept giving a site not available message. I am running windows vis...

Non-VBA formula to find 2nd Sunday of a given month
Can anyone help me write a formula to find the date of the second sunda in a given month? Thanks in Advance, Dav ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com David, Assuming you have a date in A1, this gives the 2nd Sunday of that date =DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+7 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "DavidObeid&q...

Sorting rows out of order after all rows are numbered in sequence
Data is in 4 columns with Headers described below: Col A - Line# (rows are numbered in original order in worksheet) Col B - Acct# (property number) Col C - Category (code for the type of information in the rows ie: E, V, X, T) Col D - Label (description of the data or values in the row) There is one row of Expenses (all begin with "LOE - ") that is numbered out of sequence from the other "LOE -" items. I need to Move it up with the other "LOE -" items and if possible Re-sort all the "LOE -" items BUT only the "LOE -" rows within e...

I need to write this formula in basic
Hi! Does anyone know how to write this formula in Basic. I need to make it work in Openoffice because Excel seems to crash with a list of 20000 people. =IF(ISERROR(VLOOKUP((RC1&R1C),Sheet1!R2C1:R45000C4,4,0)),"""",VLOOKUP ((RC1&R1C),Sheet1!R2C1:R45000C4,4,0)) Thanks!! ...

Functions & Formulas
Hello, I like to see if anyone is able to help me come up with a formula or function that will do the following for me: I have a set of 9 computer generated (somewhat random) numbers. THESE NUMBERS WILL NOT BE IN AN ASSENDING OR A DECENDING ORDER. Example is something like the following set of numbers: 18.34, 19.37, 20.4, 19.38, 17.96, and so on, up to nine numbers. I CAN ORDER THESE NUMBERS IN A ROW OR A COLUMN, EACH IN A SEPARATE CELL. HOWEVER, THIS IS HALF OF THE PROBLEM!. NOW I have another number, I WILL CALL THIS NUMBER MY CONTROL NUMBER. This control number (19.11...

Validation rule to prevent overlapping time periods
I have a table: InactID-AutoNumber PreceptorID-Number InactStart-Date/Time InactEnd-Date/Time I don't want records that have overlapping time periods for a PreceptorId. I tried: [InactStart] < (DLookUp("[InactEnd]","tblInactive","[PreceptorID]=" & [PreceptorID] & " and [InactID]=" & [InactID]-1)) but found that the DLOOKUP function was not permitted in a table validation rule. Any suggestions? You won't be able to use a table validation rule here. Instead, use the BeforeUpdate event procedure of the *form* where the data...

Search and Replace, or Add New Row
I am using Excel 2003. Worksheet1 contains columns (A) Invoice (B) Name (C) Address (D) P.O.# ... and others. I need to perform a search of column A using an invoice number and if it IS FOUND, then replace the entire row with new data. If the invoice number IS NOT FOUND, then I need to add a new row. Is there a simple way to do this? Check your other post.. "GEdwards" wrote: > I am using Excel 2003. > > Worksheet1 contains columns > (A) Invoice > (B) Name > (C) Address > (D) P.O.# ... and others. > > I need to...

Help with formula containing text
Hi I need some help on the following. I have a column of text, linked to other worksheets, that is continuously changing. I need to be alert if the same piece of text appears in the column more than twice, e.g. Tom Tom Jane Mary Mary Tom "ALERT" Thanks Mike -- mlhynes ------------------------------------------------------------------------ mlhynes's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=12959 View this thread: http://www.excelforum.com/showthread.php?threadid=401787 Mike Visit Chip Pearson's site for much help on duplicates. Fin...

Automatic link
when i open a workbook, the warning prompts saying there are automatic link, and ask if i want to update or not. anyway to eliminate it? btw, i don't hv any url link inside the workbook. This is by design of Excel. In Excel XP you have the option to not prompt but the links will automatically udpate. The links that it is referring to are links to other workbooks not to hyperlinks. All other versions of Excel don't have this option so you would have to open the file with code to get the prompt to go away. Chris "Soe" <soenandar@hotmail.com> wrote in message news:...

Preventing CFrameWnd derived resizing
Hello fellow newsgroupies, I have some CFrameWnd derived classes (both child and popup windows) which display various CView derived windows. I want to prevent them from being resized (larger or smaller) but still allow them to be minimized and 'maximized' back to their fixed sizes. How can I do this? Do I have to modify the views, the frames or both? Many thanks in advance, 'Newsgroupie' England There are several ways to do this: (i) handle WM_GETMINMAXINFO .. void CMyFrame::OnGetMinMaxInfo(MINMAXINFO* lpMMI) { // TODO: Add your message handler code here and/or call de...

How to prevent breaks in sentences within a paragraph when email
When I type a paragraph within an email, it looks great, even after I've added words to the sentence, or changed the sentence structure. Then I send the email, and sometimes send it to myself, but the sentences and paragraph are chopped up, and the idea doesn't "flow" like I wanted it to. Any suggestions on how I can keep the sentences together so they make more sense? I have looked at every option in "Tools", but haven't found a solution. Thanks. Are you using HTML or plain text? HTML will generally keep the same formatting while plain text ...

parent/child row relationships
I have a database that contains several rows for one record. (I didn't set this up) Anyway, there is a header row that has one or more detailed row, using the same ID number. There are unique keys that are in sequential order for each row. I need to be able to bring up the header row on the top of the form, and all detail at the bottom of the form -- VB@work You gonna need form and subform with Master/Child link on the Record field (ID number). Use these queries -- Van_Work_Header --- SELECT Van_Work.Record, Van_Work.Sequential, Van_Work.Text1, Van_Work.Text2 FROM Van_Work ...

VBA Range Formula
Can anybody tell me how to get this to work? In other words, how do I reference my procedure variables in a cell formula? Thanks! Sub Marktest() Set b = Cells.Find("Total", , xlValues) Set c = Range("C5") Range(b.Offset(-1, 2), c).Formula = _ "=sumproduct(($B5=Range(b.Offset(-7, 1), b.Offset(-5, 1)))*$C$1:$C$3)" End Sub I'd do something like: Option Explicit Sub Marktest2() Dim myRng As Range Dim b As Range Dim c As Range With ActiveSheet Set b = .Cells.Find("Total", , xlValues) Set...

M05 update fails.
Uninstalled M04 and installed M05 over a week ago. M05 said an update was available. I let it run and it said "Update Complete". Now, EVERY time I shut down Money, I get "Money is attempting to install an update. To complete, restart Windows, and then Money". In the last week, I tried the following to no avail. I've re-run the update several times, deleteted the mnyupdate.log several times and restored the registry to several previouse dates. It still gives me this msg EVERY time I shut Money down. Any Ideas or should I just go back to M04???? Frank ...

Sorting table automatically
I have a set of results which are collected into a table B3:AB23. The data is then sorted by the following macro below. I would like the table to be automatically sorted without me having to use the Keyboard Shortcut: Ctrl+y 30-50 times a day. Can this be done? Sub League() ' ' ' Keyboard Shortcut: Ctrl+y ' Range("B3:AB23").Select Selection.Sort Key1:=Range("C4"), Order1:=xlDescending, Key2:=Range("J4") _ , Order2:=xlDescending, Key3:=Range("H4"), Order3:=xlDescending, Header _ :=xlGuess, OrderCustom:=1, Mat...

Rollup worksheets into a master worksheet
I would like to have three different people update their own product lines within a master workbook. The master worksheet needs to retain sorting ability. Is there a formula or macro I can use to pull this information from three individual worksheets into the master workbook, based on the user name? The master worksheet would then become "read only". * All column headers are the same * The workbook would contain these worksheets: Master, Cathy, Laura, Tonya Thanks for your consideration. ...

How can I retrieve rows from 11 to 20 ( Selete TOP 10 gives first 10)
Using 'Select Top 10' gives me first 10 rows of my table. I need to retrieve the next 10 records. I am writing to an excel spreadsheet where I need to write top 10 rows starting from B2 and next 10 records starting from I2. So how can I split them? Every time I have to split into 10rows each. Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201004/1 Something like this will get the job done. I don't know your table/ field names... SELECT TOP 10 primaryKey FROM myTable WHERE primaryKey NOT IN (SELECT TOP...

Insert Trigger to Update Value of Column in Inserted Row
I am trying to write a SQL Insert trigger which would populate the value of a column in the inserted row with the value of a column from another database table. The table into which the row is inserted does not have a primary key match with the other database table. The two tables can be related through a join to a third table. "CarlC" <CarlC@discussions.microsoft.com> wrote in message news:834D03E6-84CA-4CF3-9781-12FA18713940@microsoft.com... >I am trying to write a SQL Insert trigger which would populate the value of >a > column in the inserted ...

row height #12
My default row height is set at 13.20 and it goes up and down in .6 increments. Everyone else in my office has the default as 12.75 and it goes up and down in .75 increments. Our fonts are exactly the same, and every other option we could think of is the same, where can I adjust this? Thanks Open a new workbook and make the desired change to row height, save the workbook in C:\Program Files\Microsoft Office\Office10\XLStart as Book.xlt (or wherever located on your system). Make sure you move and save the original Book.xlt first in case you decide to restore the original. I don't ...

Data Validation, Via A Formula
Is there a way to apply data validation to one cell, based on the result of another cell? For instance, if in A1, a data validation list displays sports (ie baseball, basketball,etc), and if the user selects "Baseball", then the data validation in B1 would only list baseball teams, from the range named baseball teams. And if basketball is selected, then only basketball teams are displayed. If at all possible, could this be done via a formula and WITHOUT using VB? If not, I could also use any help to accomplish this via VB coding. Any and all help will be appreciated...