Reference changes when I insert new columns in the source sheet

I have absolute references set up to access data from a different sheet in 
the same workbook.  When I insert new columns in the data source sheet, the 
absolute reference change to reflect the movement of the columns.  I don't 
want them to do that.  How do I keep the absolute references absolute.
0
Utf
3/24/2010 3:23:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
436 Views

Similar Articles

[PageSpeed] 6

You can use an INDIRECT function to give you the range, and as it is a
string within the function then it will not change when rows are
inserted.

Hope this helps.

Pete


On Mar 24, 3:23=A0pm, John Bare <John B...@discussions.microsoft.com>
wrote:
> I have absolute references set up to access data from a different sheet i=
n
> the same workbook. =A0When I insert new columns in the data source sheet,=
 the
> absolute reference change to reflect the movement of the columns. =A0I do=
n't
> want them to do that. =A0How do I keep the absolute references absolute.

0
Pete_UK
3/24/2010 3:32:16 PM
Absolute reference means that the reference will always point to the same 
cell(s). No matter what changes you make to the sheet in terms of inserting 
rows or columns the reference will always poit to the original cell(s) you 
specified. To do what you are asking requires using a formula such as 
indirect. With indirect you create your cell reference in static text.

=Indirect("A1")
Will always return the value in cell A1. Note that this formula can not be 
dragged to increment the reference. Additionally the formula is volatile so 
if you have a lot of this type of formula your recacluation performance will 
suffer.
-- 
HTH...

Jim Thomlinson


"John Bare" wrote:

> I have absolute references set up to access data from a different sheet in 
> the same workbook.  When I insert new columns in the data source sheet, the 
> absolute reference change to reflect the movement of the columns.  I don't 
> want them to do that.  How do I keep the absolute references absolute.
0
Utf
3/24/2010 3:52:04 PM
Look up the INDIRECT worksheet function and see the advice under the example

RegMigrant

"John Bare" wrote:

> I have absolute references set up to access data from a different sheet in 
> the same workbook.  When I insert new columns in the data source sheet, the 
> absolute reference change to reflect the movement of the columns.  I don't 
> want them to do that.  How do I keep the absolute references absolute.
0
Utf
3/24/2010 4:02:03 PM
Reply:

Similar Artilces:

Outlook 2000 change in behavior
I don't know if anyone else has experience this but since installing critical updates I have noticed that when I double click to open email messages in my inbox the messages do not immediately open. The hour glass appears and it takes longer than before installing the critical updates for the email messages to open. I have run the detect/repair tool, and disk defrag. My operating system is windows 2000. Any advice is appreciated. Jackie Darden ...

Max clinet change to Cache mode per server
Hi, we have 2 Exchange 2003 a-p Cluster on Unisys ES7000 with 9000 mailboxes on each cluster. The Clients work with Outlook XP. We plan a Rollout of Outlook 2003 SP2 with activated Cache Mode. Our Offline Adressbook has abaout 10 MB and one Mailbox a average space of 35 MB. Now my question, how much client can rolled out parallel that the download of the mailbox content has only small impact to the Exchange Server performance ? Has anybody some experiences in this case Thank you Ingo ...

How can I insert a cell reference in a footer (eg for variable foo #2
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.LeftFooter = .Range("A1").Text End With End Sub This code should go in the ThisWorkbook code module. -- HTH RP (remove nothere from the email address if mailing direct) "wngg001" <wngg001@discussions.microsoft.com> wrote in message news:8A0F9D9E-269F-45CF-A6E3...

Edit named range reference
Hope you understand my terminology I have a number of named ranges that I use a formula for in the "Refers to" field. The formula is actually larger than the space allowed in this field and so the end of the formula is not visible when I want to edit it. My problem is if I want to edit this field, I cannot move to the end of the formula. The moment I start moving it inserts other cell references. Is there a quick key or function key which allows me to edit this. Hope this makes sense When you select the formula, hit F2, and you can move about the formula at will without corrupt...

Number of active sheets
I have Office 2003 installed to Windows 2003 terminal servers but I have a problem with on of our users and excel. The issue is that when the user is trying to have multiple spreadsheets opened he keeps getting the message "This operations has been cancelled due to system restrictions- Contact your system Administrator" I can find no setting in excel to enforce a limit on the number of speadsheets a user can have open. Thanks in advance fo any advise Michael Not an expert in these matters, but; Is it possible that the user is trying to initiate another session of Excel vs....

Excel 2000: File >> New menu command causes application crash
This is occurring on only one machine on a network! After saving an emailed Excel template to a standard network location for Microsoft templates, when selecting File >> New this installation of Excel 2000 crashes ("Excel not responding" on Close program dialog). On other machines there is no problem. Have "repaired" Microsoft Office using that option from the installation CD-ROM, have removed Excel from Office installation, rebooted and reinstalled Excel. No change on the problem behaviour. Everything else appears to be working just fine in this copy of Excel. We ...

Macro Help/Duplicate Items + Insert Rows + Sum
I am trying to create a template that will do the following: 1. Find Duplicate Entries (AlphaNumeric) In A Column 2. Insert 2 Rows Between The Duplicate Entries Then: 1. Sub-Total(Another Column With Random Numbers) Of The Duplicate Entries 2. Format the Sub-Total In Bold I have gotten to the point of writting a macro that will identify the duplicate entries; does anybody know how to do the rest? This is a changing set of data, transferred to excel from a relational database (Lotus123 Rel2, which contains anywhere between 3000 to 5000 rows. I cannot spend time grouping the data ...

Add Text Field Columns
PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect])) I have two fields both of which are text. When I try to add them as above, I get a concatenated result, not the sum. How does one add them? -- On Fri, 18 May 2007 17:38:49 -0600, bw wrote: > PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect])) > > I have two fields both of which are text. When I try to add them as above, I get a > concatenated result, not the sum. How does one add them? How does one add "This is text." + "this is also text&qu...

how to change the icon text
To all, What event to I do to handle when my app get iconified? I Need to set the window text for my icon differently than my app title bar text. When I run my app, the window text for the app title bar is "foo", when the user iconifies the app, I want the icon text to be "bar". How do I do that? Thanks for the help, Reza Take a look at CWnd::OnSysCommand. SC_RESTORE and SC_MINIMIZE is what you want to look for in this method. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "reza razavipour" <rrazavipour@stbernard.com> wrote in message news:uK18XkXH...

Inserting specific number of records
I have a Data Entry form in which the user have to insert a specific numbrer of record, depending on the type of product he choose. Ej: The product "A" have to have 3 data, so it will need to insert 3 record on the table only, no more. I have created a simple code in VB that I supose to have to restrcit the number of record records, depending on a Textbox value **************************************** CODE **************************************** Private Sub LECTURA_BeforeUpdate(Cancel As Integer) On Error GoTo Err_Lectura Dim Respuesta If Form.CurrentRecord <= Forms...

Copy chart with reference table
I have a pie chart neatly tied to a data table alongside. Now I want to copy both the chart and table to a different area of the sheet so I can modify the new data table and have 2 different charts. My issue is that the chart will only allow absolute references, so when I copy both the chart and table together the new chart still is tied to the old table - so I have to go and change the source data ranges one by one. Any way that I can do this easily? Copy the whole sheet. The copied chart links to the data on the copied sheet. Now cut the range that includes the copied char...

Re: Two-Column Problem
After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Charley Kyd <kyd@incsight.com>... > But now, when I add a pair of pages, Publisher gives me guides for two > columns per page but gives me only one page-wide textbox per page, > not two. Can't you create your text boxes yourself? Or change the text box that appears to a two-column one? -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer: http://mvps.org/the_nerd/disclaim.htm ...

New template
I would like to have available two types of Outlook (2003) pages for preparing out going emails. One to be blank which is the current default and a second that has my name, address and telephone along with a disclaimer (legal) on the bottom. Thanks, John You can create a message with a signature that has that information in it, then save it to your hard drive as an Outlook Template file (.OFT). That file can be opened from Windows at any time and it will look like a regular new mail message -- finish it and send it on its way. The .OFT is completely reusable. -- Jocelyn Fiorel...

Column spacing
Is there any way I can control the spacing between the columns in the layout guides (chosen from the Arrange menu)? I want to set 0.7 cm column spacing for a text frame split into 3 columns and see the guides for this setting so that I can then use individual text frames on a second page. I know I can set the column spacing in the text frame formatting option but this is not reflected in the layout guides. Please can anyone help? I am using Publisher 2000. I have this work around - I make my own ruler. Make pull a rectangle, looking at the "size" indication in the lower r...

Data in columns not rows
I have file that is names, addresses and phone numbers. When I copy it into excel it comes out like this. (COLUMNA) NAME ADDRESS ADDRESS2 PHONE I need it to be like this COLUMNA COLUMNB COLUMNC COLUMND NAME ADDRESS ADDRESS2 PHONE Is there a way to change this without copying and pasting? Jenn Is the data consistently 4 rows? Or do you have varying sets? How far down Column A do the sets extend? If 4 rows per set try this..... In B1 enter =INDEX($A$1:$A$3000,(ROW()-1)*4+COLUMN()-1,1) Drag/copy across to E1 then select B1:E1 and drag/copy down u...

Creating a new document from a multiple paged Excel document
Can I save only one page of an existing Excel multiple page document? If so, how? Thanks to anyone that can take the time to answer/instruct. Right-click the sheet tab that you want, and hit Move or Copy. Choose "Create a copy" and then, from the dropdown, choose "new book". **** Hope it helps! **** ~Dreamboat Excel VBA Certification Coming Soon! www.VBAExpress.com/training/ ******************************** "Lisa" <Lisa@discussions.microsoft.com> wrote in message news:CBBDECC6-2432-41D4-99F2-C1EF10B1EC1F@microsoft.com... > Can I save only one page of...

Using a Text / Data output as a cell reference
I am trying to use the end of a column as a divisor and need to convert what i guess is text into an actual cell reference if possible. In column A, say there are 13 data points ending at cell A13. I then try to turn that into a cell reference with the formula ="A"&TEXT(COUNTA(A1:A13,),"0") I put this formula in B1 the output of this is then A13 what i need this to do, is be able to divide any cell by A13. The reason I am going through all this trouble is that there are many columns and each has a different # of data points. I hope this is somewhat clear. Thanks ...

Cashiers changing prices: "access pricing" and discounts
Hi: We are a wine shop and give 10% discounts when customers purchase six bottles of wine, and 20% when they purchase 12 bottles. I've got the discounts to automatically kick in when 6 or 12 bottles are scanned into the system using mix and match. However, I've noticed we get an error about "cashier cannot change prices" when the minimum quantity for discount is entered into the system. The reason is b/c the cashier does not have the "access pricing" check box turned on in their detail setup. The problem is we don't want the cashier to have the &quo...

Change Default When Selecting New Message From Form
Whenever I select New -> Choose Form, it defaults to "Organizational Forms Library" (which is empty). How can I make it default to "User Templates in File System"? Thanks. I've been looking for a way to do that for years, but have never found = one. FYI, there is a newsgroup specifically for Outlook forms issues = "down the hall" at microsoft.public.outlook.program_forms or, via web = interface, at = http://www.microsoft.com/office/community/en-us/default.mspx?dg=3Dmicroso= ft.public.outlook.program_forms --=20 Sue Mosher, Outlook MVP Author of Con...

TEXT TO SPEECH CHANGES
Currently my text to speech config reads ONLY LETTERS if the letters are CAPITALs(IE when it come to the word ABLE, it speaks A B L E how can I change the setting to make the system say "able" Thanks Enter this small macro: Sub Macro1() For n = 1 To 100 Cells(n, 1).Speak Next n End Sub And it should "say" whatever text you enter in A1 thru A100 -- Gary's Student "pcor" wrote: > Currently my text to speech config reads ONLY LETTERS if the letters are > CAPITALs(IE when it come to the word ABLE, it speaks A B L E > how can I change...

Opening a new record form but allowing searches
I changed the "Data Entry to Yes" to have my forms open for new records, but it seems to have disabled my ability to move from record to record or allow for searches. Is there another way that I could have set it up so that it allows for both? On Thu, 4 Mar 2010 17:54:06 -0800, Fallout <Fallout@discussions.microsoft.com> wrote: >I changed the "Data Entry to Yes" to have my forms open for new records, but >it seems to have disabled my ability to move from record to record or allow >for searches. Is there another way that I could have set it up ...

Nesting? How do I create a table to reference?
Tried to nest more than 8 componets but it did not work. Any help on creating a table to reference it would be appreciated. Never done it before. Tried the Excel help, and it was worthless. Here is my original post that explains more: http://www.excelforum.com/showthread.php?t=498860 -- mcr1 ------------------------------------------------------------------------ mcr1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15496 View this thread: http://www.excelforum.com/showthread.php?threadid=498905 ok you have a two column table.starting in the lh column you...

Simple question
I have an xls sheet that always print 6 copies. When ever I want to print, I have to change it to 1 copy. It's only this one xls sheet. Everything else prints 1 copy like it's suppose to. It's not the printer settings. Like I said, it's just this one xls sheet. Can someone tell me how to make it print 1 copy only and then stay as 1 as the defualt. I have made many changes to the sheet and saved it many times, but it always defaults to 6 copies. Very Strange. Thanks in advance. Phil If maybe as simple as this, as each page can have the page count se individua...

Evey time I create a new document and save it, it defaults to template
I am using excel 2003. Evey time I create a new document and save it, it defaults to template - xlt. To create a document I just do new -> blank document. How can i fix this? thanks Tools>Options>Transition. Save Excel Files As. Change to MS Excel Workbook. Gord Dibben MS Excel MVP On Fri, 5 Feb 2010 17:54:26 -0600, "Greg" <greg@nospam.com> wrote: >I am using excel 2003. >Evey time I create a new document and save it, it defaults to template - >xlt. >To create a document I just do new -> blank document. > >How...

Able to change the fonts and Header in POS
It would nice to configure the font in Point of sale so it would fit into the size quickpad you use. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=e86e0f80-4b12-4bef-9222...