HELP!!! cell value dependent on another cell!

patrickkay77 · Newbie Joined: 04 Feb 2012 Posts: 4

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

scsisys · OOo Enthusiast Joined: 17 Dec 2009 Posts: 164

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

Villeroy · 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 http://forum.openoffice.org

patrickkay77 · Newbie Joined: 04 Feb 2012 Posts: 4

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 · 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 http://forum.openoffice.org

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

Thanks Ozzie I will do this in the next couple of days!
_________________
Patrick - Clarksons Edinburgh

patrickkay77 · Newbie Joined: 04 Feb 2012 Posts: 4

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

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

Try...

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

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

jacklinmor · 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