Paste link versus allowing two cells equal each other?

What is the difference between these methods? 
0
emerb (4)
1/4/2005 11:53:02 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
375 Views

Similar Articles

[PageSpeed] 29

Paste link simply enters a link formula, e.g, copy A1, select B1, and 
choose Edit/Paste Special/Paste Link results in B1 containing  =$A$1.

"Allowing two cells to equal each other" is rather too ambiguous for me 
to be sure what you mean.


In article <70F3973E-8392-4E58-852D-E87CAC3E8880@microsoft.com>,
 "emerb" <emerb@discussions.microsoft.com> wrote:

> What is the difference between these methods?
0
jemcgimpsey (6723)
1/4/2005 1:18:20 PM
Thanks a million for your reply,
I'm familiar with using the paste special method. What i meant by the two 
cells equalling each other is where you enter the equals sign into we'll say 
cell A1 and then select cell B1 and press return. 

What is confusing me is why with paste special you get the =Sheet1!$B$1 but 
by using the equal sign you get =Sheet1!B1. How do you know which method is 
the most appropriate?

"JE McGimpsey" wrote:

> Paste link simply enters a link formula, e.g, copy A1, select B1, and 
> choose Edit/Paste Special/Paste Link results in B1 containing  =$A$1.
> 
> "Allowing two cells to equal each other" is rather too ambiguous for me 
> to be sure what you mean.
> 
> 
> In article <70F3973E-8392-4E58-852D-E87CAC3E8880@microsoft.com>,
>  "emerb" <emerb@discussions.microsoft.com> wrote:
> 
> > What is the difference between these methods?
> 
0
emerb (4)
1/4/2005 2:07:02 PM
See the "The difference between relative and absolute references" topic 
in XL Help.


In article <CA9E032C-3179-4B29-B66B-1E256B22333C@microsoft.com>,
 "emerb" <emerb@discussions.microsoft.com> wrote:

> What is confusing me is why with paste special you get the =Sheet1!$B$1 but 
> by using the equal sign you get =Sheet1!B1. How do you know which method is 
> the most appropriate?
0
jemcgimpsey (6723)
1/4/2005 2:20:19 PM
Heya again,
Yeah i know the difference between absolute and relative. What is confusing 
me is that the cell that isnt absolute ie: =Sheet1!B1 seems to behave as an 
absolute reference. I have moved B1 to several other locations and checked 
the result on the the link in cell A1 and it always refers to B1 in both 
cases ie: paste special and =. I would have assumed that it wouldn't but it 
does. That's either very strange or christmas is catching up on me!!

"JE McGimpsey" wrote:

> See the "The difference between relative and absolute references" topic 
> in XL Help.
> 
> 
> In article <CA9E032C-3179-4B29-B66B-1E256B22333C@microsoft.com>,
>  "emerb" <emerb@discussions.microsoft.com> wrote:
> 
> > What is confusing me is why with paste special you get the =Sheet1!$B$1 but 
> > by using the equal sign you get =Sheet1!B1. How do you know which method is 
> > the most appropriate?
> 
0
emerb (4)
1/4/2005 2:47:06 PM
Yes, if you move a reference, either by dragging or by cutting and 
pasting, XL will always update the reference, just as if you inserted a 
row/column.

If you want to always refer to B1, even if you cut and paste, or add 
rows, etc., use

    =INDIRECT("B1")



In article <74D5AECE-5CDA-407C-A996-AD4C0D8EDEC4@microsoft.com>,
 "emerb" <emerb@discussions.microsoft.com> wrote:

> Yeah i know the difference between absolute and relative. What is confusing 
> me is that the cell that isnt absolute ie: =Sheet1!B1 seems to behave as an 
> absolute reference. I have moved B1 to several other locations and checked 
> the result on the the link in cell A1 and it always refers to B1 in both 
> cases ie: paste special and =. I would have assumed that it wouldn't but it 
> does. That's either very strange or christmas is catching up on me!!
0
jemcgimpsey (6723)
1/4/2005 4:42:58 PM
Reply:

Similar Artilces:

Cells not automatically changing
I have two worksheets, one that lists all of my expenses and anothe that I am summarizing this info on. I am using a SUMIF formula that sums based on what type of expense. M problem is that when I change the amount of the expense on the firs sheet, the sum does not update itself automatically on the second shee where the SUMIF cell is located. It will only change it if I save th workbook. Why does it require that I save before it updates the linked cells? Any thoughts would be greatly appreciated! Thanks -- Message posted from http://www.ExcelForum.com Hi have you checked that automat...

Combine 2 cells into 1
I have 2 sheets in Excel 2000. One sheet has a column for first name and a column for last name. The other sheet has a column in the format "last name, first name". I need to move all of the names from the sheet that has them in separate columns to the sheet with the "last name, first name" format. What is the easiest way to do this? There are hundreds of names so I'd rather not have to manually put them in. I figure Excel has to have some sort of function for this. Thanks, Brian Mosher ...

How many words or characters maximum in a cell?
Hi I am having trouble with entering all of my text into a cell. It does not show everything. I am using Excel to write my daily activities at work and need a lot of space! I'm using Excel 2000 on Windows 2000. Any advice would be appreciated. Thanks! Take a look at "Specifications" in XL Help. You'll find that XL cells can contain 32767 characters, of which the first 1024 will be displayed in the cell or printed. You can work around this limit if you manually enter linefeeds (Alt-Enter) at least every 1024 characters. In article <C9144458-3061-45FE-ABFF-50E64C...

Determine if 2 cells share a common word
I have a list with two text columns. I want to identify the rows where the cells share a common word. For example, Fujitsu Consulting in col A and Software Engineer in col B would not share a common word but Starbucks in col A and Starbucks Barista in col B would share a common word. Sub Highlight_Word() Dim rng As Range Dim Cell As Range Dim start_str As Integer myword = InputBox("Enter the search string ") Mylen = Len(myword) Set rng = Selection For Each Cell In rng ' start_str = InStr(cell.Value, myword) '(case sensitiv...

Line styles for Cell Borders
I would like to know if there is a way to add to or alter the available line styles for cell borders that appear in the Fomatting window. The number of styles is vary limited. I would like to be able to alter or add to the thickness of the lines and the types of lines shown. Is there a way to easily do this? Carl Not that I know of. You could always try putting a transparent rectangle over the cell, but I don't see a large number of options there (maybe 6?) for the line styles. On Wed, 1 Dec 2004 19:45:01 -0800, "Carl" <Carl@discussions.microsoft.com> wrote: ...

Unable to open a 82 KB XLSM file due to "Too many different cell formats" & "Converter failed to open the file." errors.
Hello. I received a 82 KB XLSM (macro version?) file from a colleague. I tried to open it in my updated (as of last Tuesday) MS Office 2003 Pro. SP3, with its compatibilty pack. Excel Office SP3 (v11.8302.8221 SP3) failed to open it. When opening it, I saw "File conversion in progress" dialog box for a second, "Too many different cell formats" twice, and then "Converter failed to open the file." I asked another coworker who had old Office 2003 (not updated) and the compatbility pack. She got the same errors BUT was able to see the read the contents unlik...

Linked forms contd
I tried using this code from Arvin however I get a type mismatch error. I thought it may be a setting in the tables however they are both long interger. Any ideas Sub cmdOpen_Click() DoCmd.OpenForm "Memo",,,,"customerid=" & Me.customerid End Sub On the memo form set the DefaultValue of the customerid textbox to: =Forms!FirstFormName!customerid Now, if there is a matching record it opens to that record, if not, it creates one with an id equal to the one on the calling form. ...

Copying formatting from a cell whose location I have calculated.
Excel 2003 in XP: I want to copy the formatting of one of 12 cells whose location I have calculated. I could use the usual Conditional Formatting, but that only gives 3 options. I need 12. to wit: I want to copy the formatting of a cell in a row above the cell to be formatted. In a cell to the left I have calculated how many columns of offset to the desired format to be copied. The cell format I want to use is in a row above my cell, and offset by 0 to 11 cells. Any ideas? - Thanks! -- sdm From your description it appears you are only copying the format of a few cells, have you ...

paste special #10
I have somehow changed my settings, I used to be able to get many options when I clicked "past special", now I just get a dialogue box giving only two options, either unicode text or text. Also, when I right click a cell, and try to copy, it now adds multiple entries to the office clipboard, the last one being "no preview available", so a right click on a cell followed by copy, then a right click on another cell followed by paste, will no longer copy the cell. what have i changed? I bet you're copying from one workbook to another. And I bet you have two instan...

Vlookup but two rows below
Hi, I have a requirement to extract some data which under normal circumstances would be ease as the formula =VLOOKUP(C2,$R$12:$T$1010,3,0) would return the correct result if R12 contained 301 and T12 contained XYZ i.e. XYZ would be returned. However, I want to return the contents of cell T14 which is always 2 rows below the match in column R. Appreciate any help. Ta, Rob ...

Printing two booklets
I have a booklet that is only a quarter page when closed (so 4.25 x 5.5 when looking at the cover, when closed, and half page when open). This is no problem - I just cut off the blank half of the page when done printing. The problem is that I'm doing a large run of these small booklets. Is there a way to print two copies per page? So that I can take a paper cutter and cut each page down the middle, then fold each half into the appropriate booklet? Thanks! Jeff Wow wrote: > I have a booklet that is only a quarter page when closed (so 4.25 x > 5.5 when looking at the cover, w...

How can a single cell be incremented? i.e. N=N+1
I am able to move a value of 1 to a cell with in a macro. I want to be able to add a value of 1 to a cell with in a macro. Each time the cell is referenced, I wish to increment the cell by 1. I have not been able to increment the cell without getting a circular reference in my attempts to incremetn the cell. The process I use is sorting a table and wanting to increment the top cell so that the least used entries will be sorted to the top each time the macro is called. what does your macro look like? "Remel" wrote: > I am able to move a value of 1 to a cell with in a m...

Tag some cells
Hello, I have no idea if this is possible. What I want to do is to be able to tag some cells in a column and do a SUM on those tagged cells only. How can I do this? For tagging I was thinking of changing the background color of the cells to yellow for example. That's what I prefer. Then I thought of using SUMIF but how can I check the background color of a cell in the criteria? Thanks There is an example of using SUMPRODUCT and UDFs to count coloured cells at http://www.xldynamic.com/source/xld.ColourCounter.html The biggest problem is that changing a colour does not generate a w...

Allow eConnect Message Schema to provide PM 1099 Information
In GP10 users now have the ability to provide 1099 tax type, and a 1099 box on vendor classes, vendor cards and PM Transactions, but in the eConnect schema's this functionality has been left out. Because it has been left out, if applications continue to use the existing 1099 Amount field on PM transactions it will create invalid data in GP which passes validation (null 1099 tax type and null 1099 box). We need to have the ability to provide the following information through eConnect schemas: 1) taUpdateCreateVendorRcd - 1099 Tax Type - 1099 Box 2) taPMTransactionIns...

what is the format for an input cell?
I have Office - Student and Teacher Edition 2003 I am having trouble making a table ... I try to make a Data Table and try to define $E$3:$G$16 as the range in the row input cell area so I can make this a one-input data table and then I enter C$9$ for the column cell input area a pop up pops up saying that the input (row) cell reference is invalid I hope this not a bug that can only be fixed if you have the full version of the software any clues? it should be $C$9 Pumaman <Pumaman@discussions.microsoft.com> wrote in message news:D68327C9-5237-4353-8474-890677696F9...

Macro to clean empty cells
As I have had some great help from this group before, here is another request. Am looking to clean up some sheets in various workbooks in Excel 2007 Anybody able to help me out with a macro that will look for empty cells and then clear them out of all formats or hidden characters etc. that they may have but cant be seen. Will only need to run it on individual named sheets rather than on whole workbook many thanks ...

Two Companies Merging and want to Share Target Address Space
Merger happening, and on day one of Merger approval/announcement the Exec's want Shared addressed space. Target ORG Exchange 2003 Native Email Domain target.com Source ORG Exchange Mixed 2000 and 5.5 Email Domain Source.com Exec's want all users of the new company (Target ORG) to send/receive as target.com on day one. At a very high level are these the steps to accomplish this? 1). If we modify all email addresses in Source domain and add target.com as primary address. 2). Place target.com into non-authoritative mode for target.com. 3). SMTP Connector configured t...

Can I Apply Two Filters to Task View?
It seems to me that when I add a filter to a task view for something like category "does not contain" xyz, and then add right below that, category "does not contain" abc, it should show all of my tasks, but just not anyone that has xyz OR abc in the category. But, if I add the first filter, it seems to work ok, but when I add the second, it just makes all of the filtering not work. Am I missing something? when you use two of the same criteria they are OR'd together. Try using abc AND xyz as the condition (it works with some fields but may not work with all) or use ...

Retrieve last non zero value in a range of cells
Hi How do I extract the last non zero value in a range of 5 Cells for Example: A1=95 B1=93 C1=98 D1=0 E1=0 From the example above I would require to choose the value 98 from the Range declared. Some weeks it is possible for all the values to be >0 so therefore I would need the Value in E1 other weeks it may be B1 Would it be possible to return the value I require into Cell G1 in the example above? Thanks Peter Entered in G1 =LOOKUP(2,1/(A1:E1<>0),A1:E1) Gord Dibben MS Excel MVP On Wed, 8 Jun 2011 11:55:19 -0700 (PDT), Pete <fell-walker@hotmail.co.uk> wrote: >...

Excel Changes Links Improperly
We are having a problem where Excel changes links to other workbooks and breaks them. This comes up in the following scenario. Different users have access rights to different directories and have their drives mapped differently (i.e. to the directories they have access to). Sometimes a user modifies data in a file that has links to directories that don't exist for them. Of course the links don't work for them, but that's ok. What isn't ok is that when they save the file, Excel changes the links (apparently trying to make the links relative to drive where this use...

Links to jpg's in web pages (Internet Explorer)
Hi there, I'm running Windows XP Pro, SP2 and Internet Explorer 8. I've been asked to find out how to stop links to JPG's in a web page from opening up in Internet Explorer. So for example, I may have a web page open in Internet Explorer which has a link to http://www.fake-site.com/image.jpg. When I click on the link I want it to open the JPG in the Windows default application (which I've got set to Microsoft Office Photo Editor). Whatever I do, JPG's always open in Internet Explorer. For links to Word documents (for example), I can stop them opening in...

how do I UN-concatenate cells
I have a string of numbers 00633.84010.12740.0000.10101 that I need to break out into the separate components. Is the string of numbers always 5 numbers, a dot, 5 numbers, a dot, etc? If it's *always* in that format you can use a MID() function. If the separate components are between dots but irregularly spaced it becomes trickier. If it's the easy scenario, and supposing your string is in cell A1, try =MID(A1,1,5) =MID(A1,7,5) =MID(A1,13,5) etc Hi Julia, If you try Data > Text to Columns and follow the options, this may be what you are after. Regards Graham juli...

Can a formula determine if a cell is a particular colour?
Hi, I have an Excel 97 spreadsheet with heaps of rows in it, some of which have red rather than black text. Is it possible to have a formula that can show me the rows that are red so I can then sort on that column. For example, a formula something like: =if(cellcolour(A3)=red,1,0) Thanks a lot for any help. Tino, No worksheet formula, but you could create a simple UDF. You could try this technique that evolved out of previous threads between Harlan Grove and I. Create a function to get the colorindex of cells, namely Function ColorIndex(rng As Range, Optional font As Boolean = Fals...

Outlook Express and Internet Links
One of the owners of our company uses a laptop at home with Outlook Express 6. The operating system is Windows XP. He can no longer access Internet links thru Outlook Express. He can access the links thru Internet Explorer. Can you please tell me of any settings to check that would prevent him from being able to get to the links thru Outlook Express? Thanks, Sandy M Sandy M <anonymous@discussions.microsoft.com> wrote: > One of the owners of our company uses a laptop at > home with Outlook Express 6. The operating system > is Windows XP. Ask in an Outlook Express news...

paste link failing
Hi, Can any one help please, I have two work books and I link both using copy-paste special and then paste link. But when I open them about an hour latter the link no longer works. Any one have any Ideas? Thank you in advance, Sam. ...