OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

HELP!!! cell value dependent on another cell!

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
patrickkay77
Newbie
Newbie


Joined: 04 Feb 2012
Posts: 4

PostPosted: Sat Feb 04, 2012 7:05 am    Post subject: HELP!!! cell value dependent on another cell! Reply with quote

Crying or Very sad please help i have made a spreadsheet but have just hit a snag! the spreadsheet is to determine metal weights depending on ring size! i have a table on the same sheet with all the weights by ring size! what i would like is for one cell to refer to a cell in the table depending on what ring size is entered in another cell! sorry if this is confusing but its the best way i can describe what i am trying to do! if you can help me that would be very much appreciated! Thanks in advance!
_________________
Patrick - Clarksons Edinburgh
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Sat Feb 04, 2012 10:45 am    Post subject: Re: HELP!!! cell value dependent on another cell! Reply with quote

patrickkay77 wrote:
Crying or Very sad please help i have made a spreadsheet but have just hit a snag! the spreadsheet is to determine metal weights depending on ring size! i have a table on the same sheet with all the weights by ring size! what i would like is for one cell to refer to a cell in the table depending on what ring size is entered in another cell! sorry if this is confusing but its the best way i can describe what i am trying to do! if you can help me that would be very much appreciated! Thanks in advance!


I think you want something like LOOKUP(). Have a look at the Lookup function in Help.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
scsisys
OOo Advocate
OOo Advocate


Joined: 17 Dec 2009
Posts: 248

PostPosted: Sat Feb 04, 2012 10:46 am    Post subject: Reply with quote

Assumed your table is set up as follows;
Col. D = ring size
Col. E = weight

Assumed your input cell (for size) is Col. A
Assumed your needed data (weight) is Col. B

In cell B2, enter the following:

=IF(ISERROR(VLOOKUP(A2;D2:E6;2;0));"";VLOOKUP(A2;D2:E6;2;0))

scsisys


Last edited by scsisys on Sat Feb 04, 2012 3:10 pm; edited 1 time in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Feb 04, 2012 11:12 am    Post subject: Reply with quote

Having your table in A1:B12, sorted in ascending order by sizes in column A and the actual size to look up in C1:

LOOKUP(C1;$A$1:$B$12) returns the value in B at the last position where some value in A is smaller than or equal to C1.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
patrickkay77
Newbie
Newbie


Joined: 04 Feb 2012
Posts: 4

PostPosted: Sat Feb 04, 2012 5:20 pm    Post subject: Reply with quote

I will put this strait I am an absolute beginner at calc so I haven't got a clue what those last post ment is there any way of me being able the send some one the file to look at?
_________________
Patrick - Clarksons Edinburgh
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Feb 04, 2012 5:44 pm    Post subject: Reply with quote

A forum can not teach you how to use a software tool. It is impossible to pick up each and every beginner at point zero.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 397
Location: victoria

PostPosted: Sat Feb 04, 2012 7:21 pm    Post subject: Reply with quote

http://www.mediafire.com/
Is a free file hosting site - upload your file there and post the file address back here. No doubt someone will look at for you.

http://wiki.services.openoffice.org/wiki/Documentation/OOo3_User_Guides/OOo3.3_User_Guide_Chapters
Here you will find free documentation to get started on learning spreadsheets or any thing else related to Open Office
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
patrickkay77
Newbie
Newbie


Joined: 04 Feb 2012
Posts: 4

PostPosted: Sun Feb 05, 2012 5:08 am    Post subject: Reply with quote

Thanks Ozzie I will do this in the next couple of days!
_________________
Patrick - Clarksons Edinburgh
Back to top
View user's profile Send private message
patrickkay77
Newbie
Newbie


Joined: 04 Feb 2012
Posts: 4

PostPosted: Mon Feb 06, 2012 3:59 am    Post subject: Reply with quote

http://www.mediafire.com/?lul803ib0a5cr7c
This is the link to the spread sheet i am working on.

what it is i am wanting is: if I was to change the value in cell C,2 to (for example) ring size 'M' then the value that is in cell EW,3 will be placed in cell J,2 and so on for all ring sizes and metals if any one can help me with this it would be very much appreciated! Wink
_________________
Patrick - Clarksons Edinburgh
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Mon Feb 06, 2012 6:21 am    Post subject: Reply with quote

Try...
Code:
=IF($C2="";"";INDEX(ER$3:FJ$32;MATCH($B2;EQ$3:EQ$32;0);MATCH($C2;ER$2:FJ$2;0)))

The table with values is for Platinum so this formula should be pasted into I2, not J2 where the heading is Palladium, not Platinum.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 397
Location: victoria

PostPosted: Mon Feb 06, 2012 8:53 pm    Post subject: Reply with quote

Patrick

Thoughts on Platinum/Palladium.

This only applies if each and every table is EXACTLY the same size.(ie same headers in the same order and same rows names in the same order) Let me repeat that EXACTLY.

Then by highlighting the area ER3:FJ32 and typing Platinum in the name box (the box to the left in the formula toolbar),this could now be substituted in Ken's formula (though it will still work as is).

Code:
=IF($C2="";"";INDEX(Platinum;MATCH($B2;EQ$3:EQ$32;0);MATCH($C2;ER$2:FJ$2;0)))


Because of the exactness of your tables (!!??-ie I haven't checked this, though on a glance this appears so) then Ken,s formula can again be used again 'as is' in the Palladium table providing you have named an area by the above method to cover area DW3:EO32 (note that these areas do not include column and row names).

Code:
=IF($C2="";"";INDEX(Palladium;MATCH($B2;EQ$3:EQ$32;0);MATCH($C2;ER$2:FJ$2;0)))


and so on........

You should play around with this on a copy of your original until you are comfortable with what is happening by looking at what these formula do-

Code:
=MATCH($B2;EQ$3:EQ$32;0)


Code:
=MATCH($C2;ER$2:FJ$2;0)


Code:
=INDEX(ER$3:FJ$32;1;6)


Note 1- is if you click the down arrow in the name box the listed names can be seen, clicked and will transport you straight to the table without the painful scrolling.

Note 2- menu insert > names > define is where you can look at to do any adjusting of named areas.

Hope this hasn't confused you too much. cheers
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
jacklinmor
Newbie
Newbie


Joined: 15 Jun 2012
Posts: 3
Location: United Kingdom

PostPosted: Fri Jun 15, 2012 1:39 am    Post subject: Reply with quote

You make following three coding layers :-
1. Database layer
2. Business layer
3. Presentation layer
Back to top
View user's profile Send private message Yahoo Messenger
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group