Click one cell and the value goes to a nominated cell

[Shane-001]

Hi all,

Yeh I'm new to spreadsheets. What I want to do is put one cells value into a nominated cell. I have a grid with loads of cells and numbers. Now, the nominated cell affects other cells (not the grid) because of the formulas I have entered. In fact this is a complicated question to ask so I will start with how do I take a snapshot of my spreadsheet so I can show you exactly what I mean. I apologies in advance.

Thank you.

[robleyd]

[Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire and post the link here. The link also contains information on how to anonymise your document if it contains confidential information.

Make sure you describe (in the uploaded file, or in the related post) exactly what you are trying to achieve.

It may not answer your current question, but as a newcomer to spreadsheets you should read [Tutorial] Ten concepts that every Calc user should know.

[Shane-001]

Example.ods

(10.73 KiB) Downloaded 97 times

Hi. Thanks for the quick reply. I have made an example of what I am looking for.

[Villeroy]

RangeValidation.ods

(12.88 KiB) Downloaded 86 times

[Shane-001]

Hi Villeroy,

Thank you for your quick reply and your help. I sort of get an idea where your coming from but I am a extreme novice. I'm not sure where to put the maths on my example I sent or how to go about it. I think if my example spreadsheet was used with the formula it would be a better picture.

I thank you for your time.

[Lupp]

This is a problematic case for helpers for some reasons.
You told you are "new to spreadsheets".
An experienced user will expect you therefore to
start with the basics
(Learning by doing? Only on level zero - or for user very decisive about this and ready to spend time "whatever needed".)
to read the introductional parts of a related guide
(I also am not really fond of it. However, it's a bad idea of nowadays to never go this way.)
to probably ask for how to understand something you found there.
Bingo!

Instead of what a newcomer is expected to do you ask a question (roughly) on the graduate level.
In addition one where
-1- the experienced user will doubt if there is a serious use case. There is much playing around with sheets in the world.
-2- a literal solution requires programming - and has many disadvantages. (He knows, you don't)
-3- an explanation of the details is really time-consuming.

Since I sometimes feel interested in such questions more as I should regarding my time, I did a playful solution taking your "design" seriously.
I sketched it with LibreOffice 6.4.2. and it cannot work in the same version in AOO due to a problem with the "FormulaResultType". Therfore I reworked it slightly for use in AOO.
Now I will attach the adapted version here, but you won't get an extra tutorial by me about how to work with it.

[Villeroy]

You want to enter one value out of a distinct selection of values. The "validation by cell range" feature is the one designed to allow this. It accepts a vector as source range, a single row or column. So I transformed your 2D-range of cells into one column of cells and used the resulting column as validation cell range in column A so each cell in column A lets you select one of the given values as its cell value. If this is not good enough, you propably need another application or write your own one.
There won't be any problem with your formulas, no matter which formulas.
The validation list is linked to the cells in column L.
The cells in column L are linked to your green cells.
Your green cells with the formulas might be linked to other cells.
Whenever you update those other cells, the whole chain of formulas will be updated including the validation list in column A but not including the already entered constant values in column A.

[Lupp]

Well, I was in the mood to write a few lines of code, but I wouldn't like to have read my post above as just being a fancy or a pipe-dream.
The "literal" request taken as the basis was from cell C14 in the example sheet supplied by the OQer:

So in a nutshell for example....How can I make when I click on C5 (2.36) appear as the number 2.36 in cell D11? And I need to do this for hundreds of green cells but only 1 at a time.

I made a cell style to define the concept of "green cells" more precisely and assigned a Sub starting with an evaluation of the CellStyle (name) to the Event 'Selection Changed'. That was it.

Of course, I would dissuade from doing it this way - except there is a very specific reason I don't know of. IF SOMEONE wanted to actually go that way, additional coding will be needed to get something flawessly working in the real world.

[Shane-001]

Thank you everyone for your time. But I think this formula is almost impossible for me to do. I don't think any spreadsheet would understand what I am trying to achieve. I think the best way for this to go is to contact companies that build mathematical web sites. I am not interested in learning Excel or OpenOffice, just ONE calculation is all I needed and my spreadsheet is ready for the market. I have learned the basic formulas but that's as far as it goes with me. However, I don't necessarily need the calculation as it can be put in manually. It's only a few digits. It was more of a short cut to look more impressive. And just to inform you about my spreadsheet, the main calculation is Poisson. It's to do with football tables and the Poisson odds. After reading some of your comments I don't want you to think your efforts were in vain. But I was blinded with science.

Once again thank you all.

[MrProgrammer]

Hi, and welcome to the forum.

These numbers in green are produced from another formula. With clicking on any of these cells, how can I make it appear in the yellow cell (D11) as the number you see in the cell

This is very easy if you press a function key instead of clicking. Use a recorded macro with three steps:
• Edit → Copy
• Click cell D11
• Edit → Paste
Give your macro a name and save it. Normally one saves macros to My Macros, however if you intend to distribute the spreadsheet you will want to save it in the spreadsheet.
Calc Macros Guide

Now use the Tools → Customize → Keyboard dialog to assign a keyboard shortcut to your macro. Navigate to it with Category/Function. I chose key F3 and used the Modify button.
Setting up and Customizing Calc Guide

Now I select one of the cells in C5:E9, press F3, and it's copied to D11. It takes less than five minutes to record a macro and assign a keyboard shortcut. No programming is needed. This worked immediately.

I am not interested in learning … OpenOffice

You'll need to learn enough about OpenOffice to record a macro and assign a keyboard shortcut. If you're not willing to do that you can forget about this project, unless you pay someone to do this work. However doing it yourself is really simple.

my spreadsheet is ready for the market

Your customers cannot run Calc from a website. They need to install OpenOffice if they want to use a Calc spreadsheet. It is free to all and runs on Windows, Mac, and Linux.

[Shane-001]

Thank you MrProgrammer. Okay, that will give me something to work on.

Many thanks again.

[Shane-001]

Yeh, it's not working for me. Me and spreadsheets don't get on. I did say in this thread that I would go to a website to build this calculation. I was not intending to run Calc on a website but something totally different. I can't perform one simple and final calculation so it's time to move on. I will just be using OpenOffice to design what I want the website to look like. I am out of my depth here.

Thanks all.

[Villeroy]

This is because you try to do the fifth step after the first instead of the second step. The first spreadsheet I used to use in the late 80ies had less features but came on 3 floppies together with a book of 200+ pages. It was mandatory to read the first 50 pages at least before you understood what the program expected you to do. On the other 150+ pages you could learn what the program could do for you. A spreadsheet is a simplified programming language. It is NOT intuitive.