is there a formula that changes a specific cell to whatever I click on in that sheet.

[decarabas]

I am trying to find a formula that makes a particular cell whatever I click on in the sheet. eg A1 would always equal whatever cell I click on in the sheet and change as I click different cells. Does this exist? This is not a joke post. I want to quickly calculate shipping charges. I have all the data in a sheet. So when I for example click on 10 pounds in zone 1 I go across and see $12.67, then I input that number in A1 and the rest is formulated to do the rest. I wondered if there was a formula that would change A1 to 12.67 automatically instead of me having to input it manually. Hope this clarifies and thanks for the tips. Sorry for the messy start.

[decarabas]

I'll take that as a no.

[yay_excel]

If the active cell is the cell you want to change to whatever is clicked, press the = key and then click the cell you wish to change it to? I'm not sure I understand the question...

[ChemistB]

You could set up VBA code such that a double click will change the contents of a specific cell. There is no formula that will do this.

[jujuwillis]

Could be you want to use a vlookup function. Attach the raw data it always gives a little clearer insight to want you want.

Say you have a product called Product A which is £60.00 and it is being delivered to Zone 1, Zone 1 = £10.00 or possibly Zone 2 = £20.00, you want it fast so there is an additional charge and then you want to calculate that with a few other calculations, discounts etc??

If you change it to Zone 2 it would automatically change the amount to £20.00

Write the formula you want in words such as

Product code x zone delivery - discount + post speed = total amount + vat = grand total.

so figure wise it would be £60.00 plus £10.00 minus discount (say 5%) plus postal cost = subtotal. Subtotal times VAT = Grand Total.

Is that along the lines you were making your enquiry?

Have patience the people that help here are fanastic - I just do simple.

[decarabas]

Thanks for the feedback. I am attaching the sheet I am referring to. I will try to simplify the example as it's perhaps not totally clear yet. I want cell A5 to change to whatver value that I am currently clicking on. For example when I click on cell F16, which the value is 11.76, I want cell A5 to then automatically equal 11.76. When I click on cell H26, which is 13.65, I want A5 to then change to 13.65. I really hope this makes more sense. Or perhaps I am just asking too much and am super lazy.

[ChemistB]

Right click on the tab and "View code"
Paste this into the text area

Please Login or Register  to view this content.

Close VBA editor (no need to save)
Click and see. Is that what you are looking for?

[jujuwillis]

Wow ChemistB, well impressed with that....adding this to my memory bank. Decarabas, told you they were fanastic.

[decarabas]

OMG I love you! Not literally but damn near. Thanks ))

[TomOhio]

Right click on the tab and "View code"
Paste this into the text area

Please Login or Register  to view this content.

Close VBA editor (no need to save)
Click and see. Is that what you are looking for?

I wanted exactly the same thing. Thanks. Now... can you do it with a formula in cell A5 rather than using VBA?

[WHER]

Now... can you do it with a formula in cell A5 rather than using VBA?

Click in a cell somewhere and hit the F9 button.