HP Prime spreadsheet



#11

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.


#12

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


#13

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

#14

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


#15

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.

#16

Cyrille, Tim,

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

#17

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


#18

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


#19

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

#20

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


Possibly Related Threads…
Thread Author Replies Views Last Post
  AFTER HP-Prime update, AA1000 DOES NOT CLEAR THE SPREADSHEET Joseph Ec 0 1,478 12-06-2013, 11:28 PM
Last Post: Joseph Ec
  HP Prime: =f(x):=expression in spreadsheet app CR Haeger 1 1,692 12-05-2013, 07:53 AM
Last Post: cyrille de Brébisson
  HP Prime Spreadsheet Copy bug Michael de Estrada 1 1,800 12-03-2013, 11:34 PM
Last Post: Walter B
  HP Prime: How do I insert/delete a column/row in a spreadsheet ? Michael de Estrada 3 2,247 11-20-2013, 10:01 PM
Last Post: Michael de Estrada
  HP Prime - spreadsheet import bluesun08 1 1,560 11-19-2013, 03:21 PM
Last Post: CR Haeger
  MS advert shows spreadsheet with obvious error BruceH 3 2,154 11-14-2013, 09:50 AM
Last Post: Bill (Smithville, NJ)
  HP Prime - CAS functions in Spreadsheet App CR Haeger 6 2,422 11-11-2013, 12:37 AM
Last Post: Michael de Estrada
  HP Prime - Spreadsheet obscurity bluesun08 3 1,775 11-10-2013, 07:12 PM
Last Post: Joe Horn
  HP Prime - Spreadsheet lacks bluesun08 2 1,472 11-10-2013, 03:44 PM
Last Post: bluesun08
  HP Prime - more than a spreadsheet app so far CR Haeger 0 1,107 11-08-2013, 03:45 PM
Last Post: CR Haeger

Forum Jump: