HP Forums

Full Version: HP Prime spreadsheet
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

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.

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

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

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

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.

Cyrille, Tim,

I think, Sans is looking for something like VLOOKUP in a well known PC application.

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

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

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

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