Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Formula
- Thread starter bjarvis
- Start date
Storyteller
Instructor
Hello bjarvis,
If you delete E1 the formula returns an error message as you have no doubt discovered. Now, if you need just the result of the formula and not the actual formula in the cell (let's say D2) try this:
1. select D2
2. Copy
3. <Edit> select <Paste Special> then select <Values>
The result is that you paste just the value back into cell D2. You can then delete E1 without getting an error message.
Hope this helps,
MO
If you delete E1 the formula returns an error message as you have no doubt discovered. Now, if you need just the result of the formula and not the actual formula in the cell (let's say D2) try this:
1. select D2
2. Copy
3. <Edit> select <Paste Special> then select <Values>
The result is that you paste just the value back into cell D2. You can then delete E1 without getting an error message.
Hope this helps,
MO
Guest_imported
New member
- Jan 1, 1970
- 0
- 0
- 0
I need to create a cell reference formula, ie. =sheet2!b4, using a variable for the row source.
This formula points to a cell in "sheet2" in "column b" in "row 4". The following illustration obviously doesn't work, but it should provide a clearer example of what I need to do:
Given: Cell A1 = 5
="=sheet2!b" & A1
This produces the required result of "=sheet2!b5", but it treats it like a text entry and not a usable formula.
Any ideas?
Thanks.
This formula points to a cell in "sheet2" in "column b" in "row 4". The following illustration obviously doesn't work, but it should provide a clearer example of what I need to do:
Given: Cell A1 = 5
="=sheet2!b" & A1
This produces the required result of "=sheet2!b5", but it treats it like a text entry and not a usable formula.
Any ideas?
Thanks.
Storyteller
Instructor
Hello Seattle4321,
The reason you are getting a text entry is because you are concatenating (joining) the text value of "=sheet2!b" to a cell reference. Anything within two quotes is considered a literal value.
From your example I would guess that you are trying to link cells from one worksheet to another within the same workbook?
There are 3 ways to link cells in a workbook
1. Formula Link
2. Paste Link
3. Name Range Link
Procedure (using your example)
Formula Link
1. <CLick> on cell B4 (Sheet 2) type "=" (without the "
2. Then use the mouse to <Click> on Sheet 1 then <Click> on cell A1. The formula bar should read =Sheet1!A1
3. <Enter>
4. The value in Sheet2!B4 will be linked to Sheet1!A1
Note: This is a one way link only. Changes made at the source (Sheet1!A1) will affect the Destination (Sheet2!B4), but changes at the Destination do not affect the Source.
Paste Link
1. <Click> on cell A1 (Sheet1) then <Copy>
2. <Click> on cell B4 (Sheet2) then <Edit> select <Paste Special>
3. <Click> on <Paste Link> button (Bottom left corner)
Think of this cadence: What am I trying to do? Something Special - so "Paste Special" What do I want to do? - "Paste Link"
Name Range Link
1. <Click> on cell A1 (Sheet 1) then <Insert> select <Name> then <Define>
2. Give the cell a name
3. <Click> on cell B4 (Sheet 2) then <Insert> select <Name> then <Paste>
4. Select the "Name" then <OK>
Note: When using this type of linking you must link cells in the same rows. It doesn't matter what columns but it must be the same rows.
Hope this helps,
Michael Microsoft Office 2000 Master Instructor
Corel Certified Instructor WordPerfect 9
The reason you are getting a text entry is because you are concatenating (joining) the text value of "=sheet2!b" to a cell reference. Anything within two quotes is considered a literal value.
From your example I would guess that you are trying to link cells from one worksheet to another within the same workbook?
There are 3 ways to link cells in a workbook
1. Formula Link
2. Paste Link
3. Name Range Link
Procedure (using your example)
Formula Link
1. <CLick> on cell B4 (Sheet 2) type "=" (without the "
2. Then use the mouse to <Click> on Sheet 1 then <Click> on cell A1. The formula bar should read =Sheet1!A1
3. <Enter>
4. The value in Sheet2!B4 will be linked to Sheet1!A1
Note: This is a one way link only. Changes made at the source (Sheet1!A1) will affect the Destination (Sheet2!B4), but changes at the Destination do not affect the Source.
Paste Link
1. <Click> on cell A1 (Sheet1) then <Copy>
2. <Click> on cell B4 (Sheet2) then <Edit> select <Paste Special>
3. <Click> on <Paste Link> button (Bottom left corner)
Think of this cadence: What am I trying to do? Something Special - so "Paste Special" What do I want to do? - "Paste Link"
Name Range Link
1. <Click> on cell A1 (Sheet 1) then <Insert> select <Name> then <Define>
2. Give the cell a name
3. <Click> on cell B4 (Sheet 2) then <Insert> select <Name> then <Paste>
4. Select the "Name" then <OK>
Note: When using this type of linking you must link cells in the same rows. It doesn't matter what columns but it must be the same rows.
Hope this helps,
Michael Microsoft Office 2000 Master Instructor
Corel Certified Instructor WordPerfect 9
- Status
