Re: [users] Re: Calc function that returns the value of the current cell?
* Mark Knecht <[EMAIL PROTECTED]> [050207, 18:39]:
> On 2/5/07, Ennio-Sr <[EMAIL PROTECTED]> wrote:
[big cut]
Hi Mark,
in the end it seems that this one should fully meet your goals: it works
for the ws-test-table (with 2 sheets) I created.
Hope it works as well for you.
Good luck!
Ennio
rem
rem Ennio-Sr (alias nasrl.laili) fecit
rem February 7, 2007
rem After selecting a cell in Sheet1, its contents (or that of the cell
rem in another sheet it refers to using an 'absolute address') is
rem copied in Sheet1.$A$1; then the cursor goes back to the starting
rem cell.
rem
sub copyCrtCellToA1andComeBack
rem get the active cell (the one you click at) and its address:
oActiveCell = ThisComponent.getCurrentSelection()
rem *** Following an example in A.Pitonyak_Macro.sxw
'Listing 6.8: Cell address in a readable form using CellAddressConversion.
' oActiveCell = ThisComponent.getCurrentSelection()
oConv =
ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
oConv.Address = oActiveCell.getCellAddress
ca$= oConv.UserInterfaceRepresentation 'cell address
fca$= oConv.PersistentRepresentation 'full cell address
' Print ca$ ' oConv.UserInterfaceRepresentation
' Print fca$ ' oConv.PersistentRepresentation
rem ***
oDoc = ThisComponent.CurrentController.Frame
dim dispatcher as object
dim Selection as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = fca$
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
end sub
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ](°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Re: Calc function that returns the value of the current cell?
* Mark Knecht <[EMAIL PROTECTED]> [050207, 18:39]: > On 2/5/07, Ennio-Sr <[EMAIL PROTECTED]> wrote: [...] > Hi Ennio, > First, and again, thanks for your help on this. > > [...] > 1) The macro moves the current location of the active cell to the cell > [...] See my previous reply (I'm trying to work on Pitonyaks function ...) > > 2) Since my main spreadsheet page is a roll-up of a spreadsheet that > is put out each week the values I see in my spreadsheet on sheet 1 are > actually values on other sheets. My front sheet has values in it such > as: =12_8_06.C43 where 12_8_06 is the name of another sheet in the > file and C43 is the cell number on that page I want to see on this > main page. As the macro I presume you're talking about the macro I called 'cptoa1' ? > sits right now it doesn't copy what I see in > my main spreadsheet but rather copies the equation that references the > other page. Can you see how to copy the contents of the other page at > the location in the current cells instead of the equation in the > current cell? > May be you have to un-mark the 'Formulas' box under the Display column in Tools/Options/Calc/View? Mind you that reference to formulas contained in same sheet/other sheets must be to their 'absolute address', otherwise you get a different value in cell $A$1. So, in you main spreadsheet page you should use =$12_8_06.$C$43 Please, confirm my assumption is correct. Ciao, Ennio. -- [Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?// Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ](°|°) [Why use Win$ozz (I say) if ... "even a fool can do that. )=( Do something you aren't good at!" (as Henry Miller used to say) ] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Re: Calc function that returns the value of the current cell?
Ennio-Sr wrote: > > * Mark Knecht <[EMAIL PROTECTED]> [050207, 18:39]: >> On 2/5/07, Ennio-Sr <[EMAIL PROTECTED]> wrote: >> > [ snip ] >> >> Hi Ennio, >> First, and again, thanks for your help on this. >> >> OK, I've installed your macro and figured out how to bind it to a >> key. CTRL+A seems to be a default assignment to select all cells in >> the spreadsheet so I bound it to CTRL+SHIFT+A and it seems to >> function. However I have two problems: >> >> 1) The macro moves the current location of the active cell to the cell >> being written. The defeats the main thing I wanted which was to not >> move away from the location I'm getting the value from. Can you think >> of how to modify the macro so that it pastes the contents of the cell >> I'm in into $A$1 but remains at the cell I'm in and doesn't move? >> > > Hi Mark, > this 'revised' macro would give you the possibility to stick to the cell > you are into, but it lacks a function I've not been able to figure out, > as I explain in the macro itself (see bottom) > >> 2) Since my main spreadsheet page is a roll-up of a spreadsheet that > > I'll examine this point later to see if I can help: be it clear that I'm > no expert but just curious and have some time to spare ... ;-) > > Perahps somebody will help update Andrew Pitonyak function to get the > readable cell address to return to. > > Regards, > Ennio > > > > Hi, Ennio. I had to wrack the memory cells because I no longer use copyRange. The following copies the selected range to A1 of the sheet stipulated with oDesSheet - indexing starts at 0 (zero). Cheers. '- - - code - - - Sub copySelecttoFC 'Copies current selection to A1 (First cell) of nominated sheet Dim oSelect as Object, oSelSheet as Object, oDesSheet as Object, tDesto, tSelect oSelect = ThisComponent.getCurrentSelection ' the current selection oSelSheet = oSelect.getSpreadsheet ' the sheet containing the current selection tSelect = oSelect.RangeAddress ' the range address of the selection oDesSheet = ThisComponent.getSheets.getByIndex( 0 ) ' The sheet to which the selection is to be copied - here, the first sheet is the destination tDesto = oDesSheet.getCellByPosition( 0, 0 ).CellAddress ' A1 of the destination sheet oSelSheet.copyRange( tDesto, tSelect ) 'copies range to A1 of destination sheet End Sub '- - - end - - - -- View this message in context: http://www.nabble.com/Calc-function-that-returns-the-value-of-the-current-cell--tf3167106.html#a8838387 Sent from the openoffice - users mailing list archive at Nabble.com. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Re: Calc function that returns the value of the current cell?
* Mark Knecht <[EMAIL PROTECTED]> [050207, 18:39]:
> On 2/5/07, Ennio-Sr <[EMAIL PROTECTED]> wrote:
> > [ snip ]
>
> Hi Ennio,
> First, and again, thanks for your help on this.
>
> OK, I've installed your macro and figured out how to bind it to a
> key. CTRL+A seems to be a default assignment to select all cells in
> the spreadsheet so I bound it to CTRL+SHIFT+A and it seems to
> function. However I have two problems:
>
> 1) The macro moves the current location of the active cell to the cell
> being written. The defeats the main thing I wanted which was to not
> move away from the location I'm getting the value from. Can you think
> of how to modify the macro so that it pastes the contents of the cell
> I'm in into $A$1 but remains at the cell I'm in and doesn't move?
>
Hi Mark,
this 'revised' macro would give you the possibility to stick to the cell
you are into, but it lacks a function I've not been able to figure out,
as I explain in the macro itself (see bottom)
> 2) Since my main spreadsheet page is a roll-up of a spreadsheet that
I'll examine this point later to see if I can help: be it clear that I'm
no expert but just curious and have some time to spare ... ;-)
Perahps somebody will help update Andrew Pitonyak function to get the
readable cell address to return to.
Regards,
Ennio
REM * BASIC *
sub cptoa1
rem get the active cell (the one you click at) and its address:
oActiveCell = ThisComponent.getCurrentSelection()
oCellAddress=oActiveCell.getCellAddress()
sh=oCellAddress.sheet
col=oCellAddress.column
row=oCellAddress.row
rem
oDoc = ThisComponent.CurrentController.Frame
dim dispatcher as object
dim Selection as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"
'
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
rem *
rem As the cell address is expressed numerically, we need convert them in
rem readable address: unfortunately, Andrew Pitonyak functions
rem (see bottom) do not seem to work: this is a mere 'very poor'
rem workaround just to test the macro
if col=0 then
col="A"
endif
if col=1 then
col=B
endif
row=row+1
s$=col+row
'print s$ 'ok it works!
rem ***
args2(0).Value = s$
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
end sub
rem
rem AND THESE ARE THE OLD A.PITONYAK FUNCTIONS THAT NEED UPDATING
rem Listing 6.8: Cell address in a readable form using CellAddressConversion.
rem oActiveCell = ThisComponent.getCurrentSelection()
rem oConv =
ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
rem oConv.Address = oActiveCell.getCellAddress
rem Print oConv.UserInterfaceRepresentation
rem Print oConv.PersistentRepresentation
'sub andrew1
'Given a cell, extract the normal looking address of a cell
'First, the name of the containing sheet is extracted.
'Second, the column number is obtained and turned into a letter
'Lastly, the row is obtained. Rows start at 0 but are displayed as 1
rem dim the_cell as object
rem Function PrintableAddressOfCell(the_cell As Object) As String
rem PrintableAddressOfCell = "Unknown"
rem If Not IsNull(the_cell) Then
remPrintableAddressOfCell = the_cell.getSpreadSheet().getName + ":" + _
rem ColumnNumberToString(the_cell.CellAddress.Column) +
(the_cell.CellAddress.Row+1)
rem End If
rem End Function
' Columns are numbered starting at 0 where 0 corresponds to A
' They run as A-Z,AA-AZ,BA-BZ,...,IV
' This is esentially a question of how do you convert a Base 10 number to
' a base 26 number.
' Note that the_column is passed by value!
' Function ColumnNumberToString(ByVal the_column As Long) As String
' Dim s$
'Save this so I do NOT modify the parameter.
'This was an icky bug that took me a while to find
'Do while the_column >= 0
' s$ = Chr(65 + the_column MOD 26) + s$
' the_column = the_column \ 26 - 1
'Loop
'ColumnNumberToString = s$
' End Function
'end sub
rem ==
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ](°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]
-
To unsubscribe, e-mail: [EMAI
Re: [users] Re: Calc function that returns the value of the current cell?
On 2/5/07, Ennio-Sr <[EMAIL PROTECTED]> wrote:
Hi Mark,
* Mark Knecht <[EMAIL PROTECTED]> [040207, 16:46]:
> On 2/4/07, Ennio-Sr <[EMAIL PROTECTED]> wrote:
> >Hi Mark,
> >
> >* Mark Knecht <[EMAIL PROTECTED]> [030207, 17:20]:
> >[...]
This is not what you would like but seems to be a better workaround:
1. in the conditional formatting, set the 'equal to' condition to cell
$A$1
2. Copy this macro into your Calc sheet and assign it a keystroke
sub cptoa1
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
end sub
Suppose you assign the "CTRL+A" keystroke to the macro: now, as you
navigate your ss, whatever the cell you are in at the moment, if you
press CTRL+A that particular cell contents will be copied to cell $A$1
(or any other of your choice, to be indicated in the last but third of
my macro) thus firing up the condit. formatting.
It worked in my small test ss ... ;)
Cheers,
Ennio.
Hi Ennio,
First, and again, thanks for your help on this.
OK, I've installed your macro and figured out how to bind it to a
key. CTRL+A seems to be a default assignment to select all cells in
the spreadsheet so I bound it to CTRL+SHIFT+A and it seems to
function. However I have two problems:
1) The macro moves the current location of the active cell to the cell
being written. The defeats the main thing I wanted which was to not
move away from the location I'm getting the value from. Can you think
of how to modify the macro so that it pastes the contents of the cell
I'm in into $A$1 but remains at the cell I'm in and doesn't move?
2) Since my main spreadsheet page is a roll-up of a spreadsheet that
is put out each week the values I see in my spreadsheet on sheet 1 are
actually values on other sheets. My front sheet has values in it such
as: =12_8_06.C43 where 12_8_06 is the name of another sheet in the
file and C43 is the cell number on that page I want to see on this
main page. As the macro sits right now it doesn't copy what I see in
my main spreadsheet but rather copies the equation that references the
other page. Can you see how to copy the contents of the other page at
the location in the current cells instead of the equation in the
current cell?
Anyway, I'm learning a bit so I'll do some study on this. Hopefully
I can figure it all out. Your work has been helpful. Thanks!
Cheers,
Mark
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Re: Calc function that returns the value of the current cell?
Hi Mark,
* Mark Knecht <[EMAIL PROTECTED]> [040207, 16:46]:
> On 2/4/07, Ennio-Sr <[EMAIL PROTECTED]> wrote:
> >Hi Mark,
> >
> >* Mark Knecht <[EMAIL PROTECTED]> [030207, 17:20]:
> >[...]
This is not what you would like but seems to be a better workaround:
1. in the conditional formatting, set the 'equal to' condition to cell
$A$1
2. Copy this macro into your Calc sheet and assign it a keystroke
sub cptoa1
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
end sub
Suppose you assign the "CTRL+A" keystroke to the macro: now, as you
navigate your ss, whatever the cell you are in at the moment, if you
press CTRL+A that particular cell contents will be copied to cell $A$1
(or any other of your choice, to be indicated in the last but third of
my macro) thus firing up the condit. formatting.
It worked in my small test ss ... ;)
Cheers,
Ennio.
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ](°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (as Henry Miller used to say) ]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Re: Calc function that returns the value of the current cell?
Hi, Mark. How about Window - Split to keep the first row (or rows) at the top of the current screen. Yes you would have to take your cursor off the current cell, but you wouldn't need to scroll away from the region where it is visible. (I'm not up to doing macros right now and this might get you along until you can come up with the solution you envision.) Donald. Mark Knecht wrote: On 2/4/07, Ennio-Sr <[EMAIL PROTECTED]> wrote: Hi Mark, * Mark Knecht <[EMAIL PROTECTED]> [030207, 17:20]: [...] > Thanks for the responses. Sorry but I see I wasn't clear enough. > Let me write a bit more. > [snip] Let's say I want to see every instance on INTC, so now I have to scroll up, reposition my cursor, type INTC, and then go back down to where I was in the array to see where else INTC appears. This is what I already do. [snip] At least that's what I see in my head. It all works already except for the part where I click on a cell. Thanks again for the response. I hope that maybe someone will come up with a solution over the next few days as it would save me scrolling around quite a bit. Cheers, Mark - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Re: Calc function that returns the value of the current cell?
Johnny Andersson-3 wrote: > > I just tried to write a macro, ACellF (ActiveCellFormula) for it. It > didn't > work at all... > > Function ACellF() As String > ACellF=ThisComponent.getCurrentSelection.getFormula() > End Function > > > I entered =ACELLF() in a cell and all that showed up was "=ACELLF()", > regardless which cell was selected. However, the function worked when > called > in a macro, but obviously not as a cell function. If someone knows why it > doesn't work, I would be happy to know too... > > I also XRayed it and it showed the formula of the currently selected cell. > > Johnny Andersson > > Hi, Johnny. Such a function obtains its value at the time of entry of the formula - i.e. when you enter =ACELLF() in the cell. At that moment, the current selection is the cell in which you enter the formula. To get the formula to recalculate, you need to use CTRL+SHIFT+F9 - one limitation of such user-defined functions. You will also find that the formula generates an error message when the spreadsheet is loaded - possibly because "thiscomponent" does not then exist - a serious limitation. -- View this message in context: http://www.nabble.com/Calc-function-that-returns-the-value-of-the-current-cell--tf3167106.html#a8799733 Sent from the openoffice - users mailing list archive at Nabble.com. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Re: Calc function that returns the value of the current cell?
On 2/4/07, Ennio-Sr <[EMAIL PROTECTED]> wrote: Hi Mark, * Mark Knecht <[EMAIL PROTECTED]> [030207, 17:20]: [...] > Thanks for the responses. Sorry but I see I wasn't clear enough. > Let me write a bit more. > > I'm not looking for mouse hot spots, although that would be very > cool. In my case I have completely selected the cell either using my > keyboard or the mouse. At this point the 'Name Box' in the upper left > shows the cell's address saying 'B1' or 'C5'. What I need is that as I > move the selected cell, using the keyboard arrow keys for instance, I > will go from B1 to B2 to C3 to C4 to C5. As I make each of those steps > I need a specific cell in my spreadsheet to show me the value in the > cell I'm at as I pass through them. > Isn't this value (in case it is a value and not a formula) already shown in the cell next to what you call 'Name Box'? (on the bar appearing just before the SS) > Here's the setup. I have a list of 100 stocks which gets updated > each week. In my spreadsheet I have built an array of each week's list > residing in a column with increasing weeks going to the right. For 26 > weeks I have 26 columns of 100 stocks. Elsewhere in my spreadsheet I > have a cell where I can type into it an arbitrary stock symbol. Using > conditional formatting the spreadsheet then highlights every > occurrence of that stock's symbol in the array. This is great and > helps me see how each stock has been moving in the list as the weeks > progress. However, it's a bit difficult and slow since I have to > always go back to that one cell to enter a stock symbol and then have > to scroll up/down/left/right to see the whole array as this array > continues to grow to the right week after week. > > What I would like to do is to select any cell in the main array of > stocks and have the contents of that cell put in my main selection > cell, which will then cause the main array conditional formatting to > show me all the other cells with the same symbol. > > Maybe you can think of an easier way to do this? > I'm not sure to have fully understood what you are attempting to do; anyway, just in case I did get it right, you could try this sort of workaround: 1. in the conditional formatting, set the 'equal to' condition to cell $A$1 2. reserve the first raw in the ssheet to contain such chain, starting from cella A1: =B1, =C1, =D1 (so cell Xn-1 would be =Xn) Supposing you're looking at the 26th week and want to change your stock symbol, you would only have to put a new symbol in in the top raw of that column: this will get copied to all cells of the raw, including $A$1 which triggers the conditional formatting. HTH Regards, Ennio. Hi Ennio, Thanks for the response. What you are suggesting is possible (I think!) but it still involves going away from the cell I am looking at to type a value into one of these top cells. Imagine that you have a large array. It's 1000 rows by 1000 columns. Maybe you are visually looking somewhere in the middle of this large array. Now, you want to know all locations in the large array that have the value you are currently looking at. Let's say I want to see every instance on INTC, so now I have to scroll up, reposition my cursor, type INTC, and then go back down to where I was in the array to see where else INTC appears. This is what I already do. What I would *like* to do, if it is possible, would be to see INTC in the table visually and just click my mouse there selecting that cell. Having selected that cell this function I'm asking about would know the contents of the cell and then my same conditional formatting would then show me all the locations in the array that have the symbol INTC. This way I never navigate away from the location I'm looking at. Click on any location and all other locations with the same symbol light up with a green background. At least that's what I see in my head. It all works already except for the part where I click on a cell. Thanks again for the response. I hope that maybe someone will come up with a solution over the next few days as it would save me scrolling around quite a bit. Cheers, Mark - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Re: Calc function that returns the value of the current cell?
Hi Mark, * Mark Knecht <[EMAIL PROTECTED]> [030207, 17:20]: [...] > Thanks for the responses. Sorry but I see I wasn't clear enough. > Let me write a bit more. > > I'm not looking for mouse hot spots, although that would be very > cool. In my case I have completely selected the cell either using my > keyboard or the mouse. At this point the 'Name Box' in the upper left > shows the cell's address saying 'B1' or 'C5'. What I need is that as I > move the selected cell, using the keyboard arrow keys for instance, I > will go from B1 to B2 to C3 to C4 to C5. As I make each of those steps > I need a specific cell in my spreadsheet to show me the value in the > cell I'm at as I pass through them. > Isn't this value (in case it is a value and not a formula) already shown in the cell next to what you call 'Name Box'? (on the bar appearing just before the SS) > Here's the setup. I have a list of 100 stocks which gets updated > each week. In my spreadsheet I have built an array of each week's list > residing in a column with increasing weeks going to the right. For 26 > weeks I have 26 columns of 100 stocks. Elsewhere in my spreadsheet I > have a cell where I can type into it an arbitrary stock symbol. Using > conditional formatting the spreadsheet then highlights every > occurrence of that stock's symbol in the array. This is great and > helps me see how each stock has been moving in the list as the weeks > progress. However, it's a bit difficult and slow since I have to > always go back to that one cell to enter a stock symbol and then have > to scroll up/down/left/right to see the whole array as this array > continues to grow to the right week after week. > > What I would like to do is to select any cell in the main array of > stocks and have the contents of that cell put in my main selection > cell, which will then cause the main array conditional formatting to > show me all the other cells with the same symbol. > > Maybe you can think of an easier way to do this? > I'm not sure to have fully understood what you are attempting to do; anyway, just in case I did get it right, you could try this sort of workaround: 1. in the conditional formatting, set the 'equal to' condition to cell $A$1 2. reserve the first raw in the ssheet to contain such chain, starting from cella A1: =B1, =C1, =D1 (so cell Xn-1 would be =Xn) Supposing you're looking at the 26th week and want to change your stock symbol, you would only have to put a new symbol in in the top raw of that column: this will get copied to all cells of the raw, including $A$1 which triggers the conditional formatting. HTH Regards, Ennio. -- [Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?// Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ](°|°) [Why use Win$ozz (I say) if ... "even a fool can do that. )=( Do something you aren't good at!" (as Henry Miller used to say) ] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Re: Calc function that returns the value of the current cell?
I just tried to write a macro, ACellF (ActiveCellFormula) for it. It didn't work at all... Function ACellF() As String ACellF=ThisComponent.getCurrentSelection.getFormula() End Function I entered =ACELLF() in a cell and all that showed up was "=ACELLF()", regardless which cell was selected. However, the function worked when called in a macro, but obviously not as a cell function. If someone knows why it doesn't work, I would be happy to know too... I also XRayed it and it showed the formula of the currently selected cell. Johnny Andersson
Re: [users] Re: Calc function that returns the value of the current cell?
Mark Knecht wrote: > > On 2/3/07, Victor Chapman <[EMAIL PROTECTED]> wrote: >> Mark Knecht wrote: >> > Hi, >> > First post here. I'm a newish Open Office user and not much of an >> > Excel user. I'm looking for a function in the library that would >> > return the value of the current cell, much like what I see at the top >> > in the input line. I've been searching through the docs but can't seem >> > to spot it. I need a specific cell in my spreadsheet to essentially >> > track where ever I place my mouse. >> >> Let me see if I understand this. For example, when you move the mouse >> pointer over cell E7 you want to see the contents of that cell. If that >> is the case, I don't think it can be done. I don't think the cells are >> mapped as a series of "hot spots." > > Hi Victor & John, >Thanks for the responses. Sorry but I see I wasn't clear enough. > Let me write a bit more. > >I'm not looking for mouse hot spots, although that would be very > cool. . > >Here's the setup. I have a list of 100 stocks which gets updated > each week. In my spreadsheet I have built an array of each week's list > residing in a column with increasing weeks going to the right. For 26 > weeks I have 26 columns of 100 stocks. Elsewhere in my spreadsheet I > have a cell where I can type into it an arbitrary stock symbol. Using > conditional formatting the spreadsheet then highlights every > occurrence of that stock's symbol in the array. This is great and > helps me see how each stock has been moving in the list as the weeks > progress. However, it's a bit difficult and slow since I have to > always go back to that one cell to enter a stock symbol and then have > to scroll up/down/left/right to see the whole array as this array > continues to grow to the right week after week. > >What I would like to do is to select any cell in the main array of > stocks and have the contents of that cell put in my main selection > cell, which will then cause the main array conditional formatting to > show me all the other cells with the same symbol. > >Maybe you can think of an easier way to do this? > >I hope that clarifies a bit. > > Thanks very much, > Mark > >> >> > >> > If I have to take it in two steps by first getting the address of >> > the current cell and then somehow getting the contents of that address >> > that would be fine also. >> > >> > Thanks in advance for any help you can provide. >> > >> > Cheers, >> > Mark >> > > The spreadsheet I formerly used had a function called CELLPOINTER. It could be used with the same parameters as the CELL function, similar to Calc's CELL function. It would reveal, for example, the contents of the cell currently occupied by the cell pointer (or cursor, if you prefer). There is, as far as I've been able to discover, no comparable function in Calc. It would be possible to design such a function for your own use with script but user-defined functions have severe shortcomings, unless you go the whole way and write an add-in. I use a different setup to you. I have a sheet dedicated to each stock and each day's trading summary for the stock is appended to the table of daily trading. At the end of each week, a weekly summary is extracted and appended to a table of weekly trading. The tables were originally constructed that way so that I could easily insert charts using the data. On the first sheet, I have a table which contains a row for each stock. The row contains, in the main, formulas calculating various results based on the daily table or data in that row itself. The first column contains the stock code which can act as a reference for formulas in that row. The result is that, generally speaking, I only need to look at the individual stock tables when considering a transaction. "mouse hot spots", whatever they are, may be better. I suggest you think about constructing a table with formulas which reveal the information you require. HTH -- View this message in context: http://www.nabble.com/Calc-function-that-returns-the-value-of-the-current-cell--tf3167106.html#a8790601 Sent from the openoffice - users mailing list archive at Nabble.com. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Re: Calc function that returns the value of the current cell?
On 2/3/07, Victor Chapman <[EMAIL PROTECTED]> wrote: Mark Knecht wrote: > Hi, > First post here. I'm a newish Open Office user and not much of an > Excel user. I'm looking for a function in the library that would > return the value of the current cell, much like what I see at the top > in the input line. I've been searching through the docs but can't seem > to spot it. I need a specific cell in my spreadsheet to essentially > track where ever I place my mouse. Let me see if I understand this. For example, when you move the mouse pointer over cell E7 you want to see the contents of that cell. If that is the case, I don't think it can be done. I don't think the cells are mapped as a series of "hot spots." Hi Victor & John, Thanks for the responses. Sorry but I see I wasn't clear enough. Let me write a bit more. I'm not looking for mouse hot spots, although that would be very cool. In my case I have completely selected the cell either using my keyboard or the mouse. At this point the 'Name Box' in the upper left shows the cell's address saying 'B1' or 'C5'. What I need is that as I move the selected cell, using the keyboard arrow keys for instance, I will go from B1 to B2 to C3 to C4 to C5. As I make each of those steps I need a specific cell in my spreadsheet to show me the value in the cell I'm at as I pass through them. Here's the setup. I have a list of 100 stocks which gets updated each week. In my spreadsheet I have built an array of each week's list residing in a column with increasing weeks going to the right. For 26 weeks I have 26 columns of 100 stocks. Elsewhere in my spreadsheet I have a cell where I can type into it an arbitrary stock symbol. Using conditional formatting the spreadsheet then highlights every occurrence of that stock's symbol in the array. This is great and helps me see how each stock has been moving in the list as the weeks progress. However, it's a bit difficult and slow since I have to always go back to that one cell to enter a stock symbol and then have to scroll up/down/left/right to see the whole array as this array continues to grow to the right week after week. What I would like to do is to select any cell in the main array of stocks and have the contents of that cell put in my main selection cell, which will then cause the main array conditional formatting to show me all the other cells with the same symbol. Maybe you can think of an easier way to do this? I hope that clarifies a bit. Thanks very much, Mark > > If I have to take it in two steps by first getting the address of > the current cell and then somehow getting the contents of that address > that would be fine also. > > Thanks in advance for any help you can provide. > > Cheers, > Mark - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [users] Re: Calc function that returns the value of the current cell?
Victor Chapman wrote: > > Let me see if I understand this. For example, when you move the mouse > pointer over cell E7 you want to see the contents of that cell. If that > is the case, I don't think it can be done. I don't think the cells are > mapped as a series of "hot spots." Well, here's an idea? How about a Macro that automatically writes the contents of which cell is selected when it is selected? Could you write a macro to trigger on that event? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[users] Re: Calc function that returns the value of the current cell?
Mark Knecht wrote: > Hi, > First post here. I'm a newish Open Office user and not much of an > Excel user. I'm looking for a function in the library that would > return the value of the current cell, much like what I see at the top > in the input line. I've been searching through the docs but can't seem > to spot it. I need a specific cell in my spreadsheet to essentially > track where ever I place my mouse. Let me see if I understand this. For example, when you move the mouse pointer over cell E7 you want to see the contents of that cell. If that is the case, I don't think it can be done. I don't think the cells are mapped as a series of "hot spots." > > If I have to take it in two steps by first getting the address of > the current cell and then somehow getting the contents of that address > that would be fine also. > > Thanks in advance for any help you can provide. > > Cheers, > Mark - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
