| View previous topic :: View next topic |
| Author |
Message |
silencer
General User
Joined: 30 Jan 2005
Posts: 6
|
 Posted: Sun Jan 30, 2005 1:06 am Post subject: How to lock cell link in expression with $A$3 ? |
 |
|
Can someone tell me how to lock cell in expression with $? In MS excel it was with F4.
thx in advance |
|
| Back to top |
|
 |
bobharvey
Super User
Joined: 23 Apr 2004
Posts: 1075
Location: Lincolnshire
|
| Posted: Sun Jan 30, 2005 1:14 am Post subject: |
|
|
| go to help and search for "relative addressing". It says shift-F4 |
|
| Back to top |
|
|
8daysaweek.co.uk
Super User
Joined: 29 Nov 2003
Posts: 2130
Location: UK
|
|
| Back to top |
|
|
silencer
General User
Joined: 30 Jan 2005
Posts: 6
|
| Posted: Sun Jan 30, 2005 2:40 am Post subject: |
|
|
thank you guys, im poor english speaker, so i didnt know, how to define my question in help here or in OOo suite.  |
|
| Back to top |
|
|
BookLady
Super User
Joined: 03 Nov 2004
Posts: 727
|
| Posted: Sun Jan 30, 2005 10:33 am Post subject: |
|
|
Don't worry about it Silencer by asking again you help all the rest of us who are new.
hey James or anyone more knowledgable about spreadsheets in general. When a line is deleted, the formula in the lines referring to it get messed up. Is there a way to make the formula recognize that the new line is now the one referenced on the formula? Using either absolute or relative gets an error. |
|
| Back to top |
|
|
8daysaweek.co.uk
Super User
Joined: 29 Nov 2003
Posts: 2130
Location: UK
|
| Posted: Sun Jan 30, 2005 10:48 am Post subject: |
|
|
Hi BookLady,
I don't think I really understand your question properly, perhaps you could give an example?
BFN ,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site |
|
| Back to top |
|
|
BookLady
Super User
Joined: 03 Nov 2004
Posts: 727
|
| Posted: Mon Jan 31, 2005 11:55 am Post subject: |
|
|
Well, this is a general spreadsheet question really not just OOo been bugging me for years.
I use a spreadsheet for sales. Have formulas down the column to make a running total. If a customer decides not to take the second item it would be helpful to simply delete that row.
1 =if(C3=0;0;A2+E3)
2 =if(C4=0;0;A3+E4)
3 =if(C5=0;0;A4+E5)
4 =if(C6=0;0;A5+E6)
5 =if(C7=0;0;A6+E7)
deleting line two results in:
1 =if(C#REF!=0;0;A1+E#REF!)
the new line two becomes:
2 =IF(C4=0;0;A#REF!+E4)
3 using relative reference subsequent lines are ok
4 using absolute reference all references to the deleted line are errors
Is there any way to make the formulas recognize the new line 2?
Deleting the contents resets the formulas but looses the continuity of the column. |
|
| Back to top |
|
|
r_vinoya
Super User
Joined: 03 Dec 2003
Posts: 619
Location: Somewhere in the Philippines
|
| Posted: Mon Jan 31, 2005 5:23 pm Post subject: |
|
|
| BookLady wrote: | Well, this is a general spreadsheet question really not just OOo been bugging me for years.
I use a spreadsheet for sales. Have formulas down the column to make a running total. If a customer decides not to take the second item it would be helpful to simply delete that row.
1 =if(C3=0;0;A2+E3)
2 =if(C4=0;0;A3+E4)
3 =if(C5=0;0;A4+E5)
4 =if(C6=0;0;A5+E6)
5 =if(C7=0;0;A6+E7)
deleting line two results in:
1 =if(C#REF!=0;0;A1+E#REF!)
the new line two becomes:
2 =IF(C4=0;0;A#REF!+E4)
3 using relative reference subsequent lines are ok
4 using absolute reference all references to the deleted line are errors
Is there any way to make the formulas recognize the new line 2?
Deleting the contents resets the formulas but looses the continuity of the column. |
I have this problem since the excel days.
_________________
# : - ) |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Mon Jan 31, 2005 8:36 pm Post subject: |
|
|
| BookLady wrote: | Well, this is a general spreadsheet question really not just OOo been bugging me for years.
I use a spreadsheet for sales. Have formulas down the column to make a running total. If a customer decides not to take the second item it would be helpful to simply delete that row.
1 =if(C3=0;0;A2+E3)
2 =if(C4=0;0;A3+E4)
3 =if(C5=0;0;A4+E5)
4 =if(C6=0;0;A5+E6)
5 =if(C7=0;0;A6+E7)
deleting line two results in:
1 =if(C#REF!=0;0;A1+E#REF!)
the new line two becomes:
2 =IF(C4=0;0;A#REF!+E4)
3 using relative reference subsequent lines are ok
4 using absolute reference all references to the deleted line are errors
Is there any way to make the formulas recognize the new line 2?
Deleting the contents resets the formulas but looses the continuity of the column. |
Like 8days, I'd request a full example please. That is, not just a numbered list, but your full spreadsheet involving his part to see not a list of formulas, but where they arel ocated, and vlaues in each of the reference cells. Otherwise I'm guessing. [Some might complain that's what I always do.] 
Thanks.
David. |
|
| Back to top |
|
|
r_vinoya
Super User
Joined: 03 Dec 2003
Posts: 619
Location: Somewhere in the Philippines
|
| Posted: Mon Jan 31, 2005 10:58 pm Post subject: |
|
|
| David wrote: | Like 8days, I'd request a full example please. That is, not just a numbered list, but your full spreadsheet involving his part to see not a list of formulas, but where they arel ocated, and vlaues in each of the reference cells. Otherwise I'm guessing. [Some might complain that's what I always do.]
|
Take this simple example:
on A10 type =B9
drag A10 up to A15
delete ROW12
You'll get REF! error on ROW12.
I think booklady wants new ROW12 becomes =B11... instead it becomes =B$REF!
_________________
# : - ) |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Tue Feb 01, 2005 6:30 am Post subject: |
|
|
| r_vinoya wrote: | Take this simple example:
on A10 type =B9
drag A10 up to A15
delete ROW12
You'll get REF! error on ROW12.
I think booklady wants new ROW12 becomes =B11... instead it becomes =B$REF!
|
Thanks!
In your example:
First take values [any initial values will do for setting up] in B9:B14 and move them, before deleting the row, to say D1 and down, or to any remote position, so that they do not overlap rows used for the formulas, and no apparent problem; references will change automatically to suit. OR, set it up that way to begin with. Now, in B9 put =D1, in B10 put = D2 [or drag down to copy] etc. Then the sheet will appear as needed for printout I think. Now delete a row without problem, or am I still missing the proper reference? [still on medication here, so not thinking straight ...just enjoying the exchange if you don't mind.]
I've often found, no matter the spreadsheet, that there is as much or more need to "work around" rather than have direct application to need. It's the nature of the beast.
David. |
|
| Back to top |
|
|
BookLady
Super User
Joined: 03 Nov 2004
Posts: 727
|
| Posted: Tue Feb 01, 2005 2:54 pm Post subject: |
|
|
Klutzy workaround David and not convenient when you have a customer standing there breathing at you. But perhaps there is something that might be done with a macro.
rvinoya has the right idea. This is a peculiarity clear back to Lotus days.
Unfortunately I don't know how to make screen shots with OOo or how to post them here. But all you really need to do is enter the first line of fomula and copy it down several lines. No need to enter data. Then just delete one line and you will see the error references.
This also occurs if there is a formula to total a column in a cell at the head of the column and then the bottom rows of that column are deleted. The column total formula still looks for the deleted lines.
Hmmm just tried doing a "move cells up" but that also screws up formula. Oh well this has been an irritation for a long time. But one of these days.... |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Tue Feb 01, 2005 3:39 pm Post subject: |
|
|
| BookLady wrote: | Klutzy workaround David and not convenient when you have a customer standing there breathing at you. But perhaps there is something that might be done with a macro.
|
No problem. Go for it.
David. |
|
| Back to top |
|
|
8daysaweek.co.uk
Super User
Joined: 29 Nov 2003
Posts: 2130
Location: UK
|
| Posted: Wed Feb 02, 2005 6:40 am Post subject: |
|
|
Booklady,
Sorry for the delay
Any solution would really depend on a full example, what you are really calculating.
I think that any formula which specifically references a cell that is deleted will show an error. This is a protection in some ways.
However, I have uploaded a spreadsheet which gives a possible solution for certain type of spreadsheet calculations:
http://www.8daysaweek.co.uk/forums/viewtopic.php?p=114#114
The solution really is to try to concentrate on calculating the values, rather than basing the formula on the result of a previous formula.
HTH ,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site |
|
| Back to top |
|
|
BookLady
Super User
Joined: 03 Nov 2004
Posts: 727
|
| Posted: Wed Feb 02, 2005 10:25 am Post subject: |
|
|
Thanks James . This is getting way beyond the orginal intent of this thread. And your upload will take some studying. So it will take a while for me to figure that one out.
If there was an easy answer it would have been touted about long ago.
Thanks David. If a macro can not be made from the built in key copier it is beyond me. Maybe if I hang around looking wistfully  someone will get curious and work on it . |
|
| Back to top |
|
|
|
