Ubuntu
openoffice.org package

Spreadsheet Formula

Asked by Roficimo

In Sheet 1 I have a cell (A20) that is the difference of all the cells above it. In Sheet three I have two columns, Column A2:162 is a descending list of numbers, Column B2:162 is text that I want referenced. Now, how do I get cell F15 to display the text from Sheet3.B# when Sheet1.A20 equals Sheet.3A#?

I.e. Sheet1.A20 equals 170. Sheet3.A2 = 170 and Sheet3.B2 = T20+T20+DB. Now when Sheet1.A20 changes to 130 and Sheet3.A34 = 130 how do I get cell Sheet1.F15 to display Sheet3.B34?

The problem I seem to be having is making sure the logic carries through all the array of numbers in the rows in varying sheets.

If this is confusing, please let me know, I would like to resolve this issue and would be glad to clarify as best I could.

Question information

Language:
English Edit question
Status:
Solved
For:
Ubuntu openoffice.org Edit question
Assignee:
No assignee Edit question
Solved by:
Roficimo
Solved:
Last query:
Last reply:
Revision history for this message
Thomas Kluyver (takluyver) said : 		#1

It's a bit confusing, but I think I follow what you mean. The function you're looking for is VLOOKUP. I think the form you want is (in F15 on Sheet 1):

=VLOOKUP(A20;Sheet3.A2:B162;2)

That says: Look up the value of A20 in the first column of Sheet3.A2:B162 (it always looks up in the first column, for some reason), and return the value in the second column (denoted by the 2 at the end).

Revision history for this message
Roficimo (jrifis) said : 		#2

Thomas,

Thank you for your help. While your patch didn't solve the problem, it led me to what did. I neglected to enter "False" for the sort order. But thank you.