▼
Posts: 10
Threads: 4
Joined: Sep 2013
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.
▼
Posts: 1,278
Threads: 44
Joined: Jul 2007
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
▼
Posts: 10
Threads: 4
Joined: Sep 2013
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
Posts: 193
Threads: 10
Joined: Mar 2008
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
▼
Posts: 10
Threads: 4
Joined: Sep 2013
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.
Posts: 3,283
Threads: 104
Joined: Jul 2005
Cyrille, Tim,
I think, Sans is looking for something like VLOOKUP in a well known PC application.
Posts: 34
Threads: 10
Joined: Oct 2013
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
▼
Posts: 193
Threads: 10
Joined: Mar 2008
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
▼
Posts: 34
Threads: 10
Joined: Oct 2013
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
Posts: 34
Threads: 10
Joined: Oct 2013
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
|