none
Static magic cell or cells in excel RRS feed

  • Question

  • Has anything like a magic cell been created in excel, which remains in a fixed central position (say row 12) of some column, so that when items pass through the cell via scrolling (either up or down), the data is processed in some way by whatever function is assigned to that magic cell.

    This could be useful in language analysis in a more developed form.

     
    Tuesday, November 8, 2016 8:46 AM

Answers

  • Hi Edward,

    Thanks for your help on this. It would seem that Excel is purely a mathematical tool, and using it for language analysis is fine but it's graphics abilities are limited to what it is designed to do.

    If the row function could in some way be used to report the current smallest row number on the current active screen, then I think I could use the Indirect and Address functions to achieve what I want. But even then the action of scrolling does not "action" the program, excel is actioned by pressing "enter".

    The fact that "1 space per click" is not possible, probably would not matter so much.

    But Thanks anyway,

    Mark

    • Marked as answer by mrk1952 Sunday, November 13, 2016 2:38 AM
    Saturday, November 12, 2016 6:40 PM

All replies

  • Hi mrk,

    >> Has anything like a magic cell been created in excel

    What do you mean by magic cell? As far as I know, there is only one type cell in excel. What do you mean by pass through cell via scrolling? How did you want to change the cell values, by mouse or keyboard?

    What do you mean by assigned function? Do you mean Excel formula which you enter in Excel cells?

    Do you develop with Excel by C#/VBA? What do you mean by developed form? More information about your issue and screen shot would be much helpful.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 9, 2016 5:44 AM
  • Hello Edward,

    Thanks for responding to this question. The reason behind this inquiry comes from an interest I have in language and in particular the form of ancient greek used by the writers of the New Testament. There are thousands of books on the subject. There is a lot of software out there mainly presenting the English translations but also in the original greek. But often expensive and taking the traditional approach, emphasizing grammar forms and memorizing form endings etc.

    I have been studying NT greek for about fifteen months and now am able to blunder along in John's gospel. I write down verses here and there, reread them the next day, and without wasting time memorizing words I never see, I am accumulating words I see often, which are important to know if you want to read NT greek.

     So I now have a excel file of John's gospel which has 15644 rows of data.    "Column E" has greek words - one word per row - of the NT based on the 1904 Nestle greek compilation.

    "Column G" has the parsing - ie grammatical classification - of the word in "E".

    "Column I" has the standard lexical form (lemma) of the word in "E".

    There is other data in other columns which I won't go into. But as I read (slowly) through John's gospel, I scroll down through the file and am able to refer to the parse in "G" or the lemma in "I" which with the experience I have now is becoming useful to me. If I still get stumped I refer to a Greek/English interlinear which lies open on my desk.

    When I read from the excel file, I must read vertically down "column E". Excel does not have any functions which can "map" the lines of the physical screen to a cell and visa versa.

    But say I froze the first three lines as people often do in excel for headers etc and started the data section from row 4. Now say I could define a magic cell (mask is a better description) upon E4, which had an associated text function which read whatever text object there was in E4 and placed it in say B2.Then I define another magic cell on E5 which reads E5 and places in C2. Again for E6 and places in D2 etc up to say E13 which is places in K2. These magic cells (masks) remain static - ie frozen a bit like header rows - and do not move with scrolling. Now as I scroll down or up, I read the greek in "Row 2' columns B to K, as a moving neon sign.

    This would be quite cool. I won't go further but you could add colour to indicate grammar or define something on "column G" for "row3" to do that.  It would be natural for a group to read together a horizontal text, rather than vertical. Also the font size on "row 2" could be increased for easier reading. This might all seem a lot for a little.

     I hope this defines what I mean. My logic may be flawed.

     Thanks,

     Mark.  

       



    • Edited by mrk1952 Wednesday, November 9, 2016 10:27 AM
    Wednesday, November 9, 2016 10:10 AM
  • Hi mrk,

    >> But say I froze the first three lines as people often do in excel for headers etc and started the data section from row 4

    For this function, it seems you could achieve this by Excel built-in function.

    >> Now say I could define a magic cell (mask is a better description) upon E4, which had an associated text function which read whatever text object there was in E4 and placed it in say B2

    For this, could you achieve this with Excel built-in function? Could you design your Excel sheet like what you want? Based on your description, it seems you want to set values for B2 by E4, what is your issue that could not achieve this? Will the formula not work for you by setting "=E4" for B2?

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 10, 2016 8:43 AM
  • Hello Edward,

    I can freeze panes to set aside an area B2 to K2 … and assign values to these cells simply by defining the content of B2 as “=E4” and C2 as “=E5”, …… , K2 as “=E13”. Filling the cells E4 to E13 with say the numbers 1 to 10 will cause these numbers to appear across above in B2 to K2.

    But scrolling up one space, does not cause the value in E5 to move into B2, E6 into C2, …. , E14 (ie blank) into K2, because B2, C2 .. etc take the values in E4, E5,.. which still exist even though they might have moved off the screen.

    So I need to find a way to define the contents B2 etc, maybe via "Row(E4) + Scroll” etc, where scroll is a function which returns a number based on the amount of scrolling up or down. I have noticed that each click of the mouse wheel causes a movement of four spaces. This must be able to be set to other values, maybe in preferences. For my use it would need to be 1 space per click.

    Thanks,

    Mark.










    • Edited by mrk1952 Saturday, November 12, 2016 2:56 AM
    Friday, November 11, 2016 4:31 AM
  • Hi mrk,

    >> For my use it would need to be 1 space per click.

    I think it is impossible by Excel Object model. There is no event in Excel for “1 space per click” and scroll up and down.

    In other words, there is not event for us to change the values in E5 while you scroll up and down.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Saturday, November 12, 2016 8:21 AM
  • Hi Edward,

    Thanks for your help on this. It would seem that Excel is purely a mathematical tool, and using it for language analysis is fine but it's graphics abilities are limited to what it is designed to do.

    If the row function could in some way be used to report the current smallest row number on the current active screen, then I think I could use the Indirect and Address functions to achieve what I want. But even then the action of scrolling does not "action" the program, excel is actioned by pressing "enter".

    The fact that "1 space per click" is not possible, probably would not matter so much.

    But Thanks anyway,

    Mark

    • Marked as answer by mrk1952 Sunday, November 13, 2016 2:38 AM
    Saturday, November 12, 2016 6:40 PM