The Museum of HP Calculators

HP Forum Archive 21

 HP Prime spreadsheetMessage #1 Posted by Sans on 7 Oct 2013, 9:38 a.m. Hi, On page 204 of HP_Prime_UG explains how to use external references in spreadsheets using Spreadsheet.CR. Also, on page 200 explains the cell references and says that you can specify ranges. However, I'd like to know if it is possible to search for a value within a range of cells to use it depending its value. I have tried several options inside a program modifying the Spreadsheet."CR" but I can't get it. Sorry for my english.

 Re: HP Prime spreadsheetMessage #2 Posted by Tim Wessman on 7 Oct 2013, 11:08 a.m.,in response to message #1 by Sans You can search a range ad the range is just a list. A1:B3 might return { {1,2,3},{4,5,6} for example, but there is no direct way to do this without manually creating a small formula using POS for example and some conditional steps. Can you please explain in a bit more detail so I can understand better? TW

 Re: HP Prime spreadsheetMessage #3 Posted by Sans on 7 Oct 2013, 12:10 p.m.,in response to message #2 by Tim Wessman Thanks for the quick reply and congratulations for this excellent product. I'll try to explain it better. Suppose I have the following spreadsheet: hp A B C 1 a1 b1 c1 2 a2 b2 c2 3 a3 b3 c3 What I want to know is if is possible to find a value or text in any cell to extract the values of others. For example: Defining "nn" as a local var, if nn=Spreadsheet.ai (here is the question) then Spreadsheet.bi ... etc

 Re: HP Prime spreadsheetMessage #4 Posted by cyrille de Brébisson on 8 Oct 2013, 1:51 a.m.,in response to message #1 by Sans Hello, I am sorry, but I have not understood what you are trying to do, however, here is a hint that might help you. the Cell(Row,Col) function gives you access to the Value of a Cell in the sheet. This can allow you to create a program along the lines of: export search(Str,r1,r2,c1,c2) begin local r, c; for r:= r1 to r2 do for c:= c1 to c2 do if instring(string(Cell(r,c)), Str) then return char(c+64)+r; end; end; end; return 0; end; then search("3", 1, 5, 1, 5) will search for anything that contains 3 in the A1:E5 range and return the cell number. If you want to look in the Formula, use Cell(r,c,1) Cyrille

 Re: HP Prime spreadsheetMessage #5 Posted by Sans on 8 Oct 2013, 9:01 a.m.,in response to message #4 by cyrille de Brébisson I'm sorry for my bad explanation. I am neither mathematician nor programmer, just a humble engineer. Thank you very much. With a few little changes is what I needed.

 Re: HP Prime spreadsheetMessage #6 Posted by Marcus von Cube, Germany on 8 Oct 2013, 11:20 a.m.,in response to message #4 by cyrille de Brébisson Cyrille, Tim, I think, Sans is looking for something like VLOOKUP in a well known PC application.

 Re: HP Prime spreadsheetMessage #7 Posted by Joseph Ec on 8 Oct 2013, 12:22 p.m.,in response to message #4 by cyrille de Brébisson Hi Friend, Great work done to create the HP Prime, congrats!! Please I am developping an App for numerical methods, I want to give the iterations listed in a Spreadsheet, but I would like to chage the column names or headers (A B C...) for (iteration a p b fx error ...) OR (iteration x1 x2 fx1 fx1 error...) for other method, so I need the COLUMN HEADERS REFERENCE FROM A PROGRAM. Thank you so much and best regards joseph

 Re: HP Prime spreadsheetMessage #8 Posted by cyrille de Brébisson on 9 Oct 2013, 1:43 a.m.,in response to message #7 by Joseph Ec Hello, Do you mean that you want to get a A1:C3 for example as an input and you want to deconstruct it as R1=1 C1=1 R2=3 C2=3 ? I am sorry that there is no build in facility to do so. The only solution that I Can think of would be by transfroming to string and analyzing the string... If you want to see what is stored in a Row/Colomn header then you can use Cell(0,Col,n) or Cell(Row,0,n) to get access to it (formulas, and all meta data). depending on n, you get access to different data: -1: all attributes. If the cell has nothing defined, returns -1, else return a list of 11 objects. 0: value (read only, you can not set the cell value) 1: formula 2: name 3: number format: Standard = 0, Fixed = 1, Scientific = 2, Engineering = 3, unspecified = –1 4: number of decimal places: 1 to 11, or unspecified = –1 5: font: -1: 0 to 6, unspecified = –1 6: background color: cell fill color (color, or 32786 if unspecified) 7: foreground color: contents color (color, or 32786 if unspecified) 8: horizontal alignment: Left = 0, Center = 1, Right = 2 , unspecified = –1 9: vertical alignment: Top = 0, Center = 1, Bottom = 2, unspecified = –1 10: show strings in quotes: Yes = 0, No = 1, unspecified = –1 11: textbook mode (as opposed to algebraic mode): Yes = 0, No = 1, unspecified = –1 Cyrille

 Re: HP Prime spreadsheetMessage #9 Posted by Joseph Ec on 9 Oct 2013, 12:29 p.m.,in response to message #8 by cyrille de Brébisson Thanks Cyrille, Sorry for my before explanation... OK, better with an example (suppose A1 has 1) then: Cell(1,1,-1) returns {1,"",-1,-1,-1,32768,32768,-1,-1,-1,-1 } that is OK BUT Cell(0,1,-1) returns 0 and I do not get the meta data where I hope to find the "A" column header. Days ago I tried Cell(0,1):="anytext" but it doesn´t chage the column header. Some bug there ? Best regards!! jose

 Re: HP Prime spreadsheetMessage #10 Posted by Joseph Ec on 11 Oct 2013, 1:11 a.m.,in response to message #8 by cyrille de Brébisson Hi Cyrille, Please Your help with reseting the Spreadsheet within a program to clear all data in it (clear as [Shift][Esc] but from a program ) Or how to clear a only cell, thanks and best regards!! jose

Go back to the main exhibit hall