Make active cell populate value in another cell

[schaefer921]

Hi guys,
I already have this working on my spreadsheet to make D3 populate wherever my active cell is using:

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you have a low post count. --6StringJazzer

But i want to add a range to it.
How can i make it so that if i click on any cell in a range from A4:A40, have that cells value populate to D3.

The reason i need the range is so when click away from the range, all my formulas based off of D3 will either change or try to recalculate which slows everything down and since i have a massive amount of data being pulled from D3 it starts to circular reference and so on...

Thanks
Matt

[AlphaFrog]

Please Login or Register  to view this content.

[schaefer921]

That works perfectly, thanks!

[AlphaFrog]

You're welcome. Thanks for the feedback.

Also, you could populate D3 without using a named range.
Range("D3").Value = Target.Value

[schaefer921]

is there a negative side to the named range? the fastest possible way would be preferred. I already changed over all my arrays and vlookups to Index Match.

On a side note would excel calculate faster if i entered all my 250ish Index match formulas as VBA instead of just function formulas?
I am also running 4 of these guys in separate worksheets =IF(ISNUMBER(SEARCH('Vendor Database'!$C$1,B2)),MAX($A$1:A1)+1,0) to provide a count for my Index Match. Would that be faster as VBA, everything used to be lightning fast, then i added a worksheet of payments, around 60,000 rows of data and now its processing it slowly..