[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
patrickkay77
Newbie

Joined: 04 Feb 2012
Posts: 4

 Posted: Sat Feb 04, 2012 7:05 am    Post subject: HELP!!! cell value dependent on another cell! 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
jrkrideau
Super User

Joined: 08 Aug 2005
Posts: 6732

Posted: Sat Feb 04, 2012 10:45 am    Post subject: Re: HELP!!! cell value dependent on another cell!

 patrickkay77 wrote: 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
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
scsisys

Joined: 17 Dec 2009
Posts: 248

 Posted: Sat Feb 04, 2012 10:46 am    Post subject: 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)) scsisysLast edited by scsisys on Sat Feb 04, 2012 3:10 pm; edited 1 time in total
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Sat Feb 04, 2012 11:12 am    Post subject: 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
patrickkay77
Newbie

Joined: 04 Feb 2012
Posts: 4

 Posted: Sat Feb 04, 2012 5:20 pm    Post subject: 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
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Sat Feb 04, 2012 5:44 pm    Post subject: 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
ozzie

Joined: 29 Jul 2010
Posts: 397
Location: victoria

 Posted: Sat Feb 04, 2012 7:21 pm    Post subject: 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).
patrickkay77
Newbie

Joined: 04 Feb 2012
Posts: 4

 Posted: Sun Feb 05, 2012 5:08 am    Post subject: Thanks Ozzie I will do this in the next couple of days!_________________Patrick - Clarksons Edinburgh
patrickkay77
Newbie

Joined: 04 Feb 2012
Posts: 4

 Posted: Mon Feb 06, 2012 3:59 am    Post subject: 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! _________________Patrick - Clarksons Edinburgh
ken johnson
Super User

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

Posted: Mon Feb 06, 2012 6:21 am    Post subject:

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
_________________
ozzie

Joined: 29 Jul 2010
Posts: 397
Location: victoria

Posted: Mon Feb 06, 2012 8:53 pm    Post subject:

Patrick

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).

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
_________________
jacklinmor
Newbie

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

 Posted: Fri Jun 15, 2012 1:39 am    Post subject: You make following three coding layers :- 1. Database layer 2. Business layer 3. Presentation layer
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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