Quick & dirtyway to input/convert cell referances in formula where the ref is made/used

[dlow]

Hi,

Just throwing it out there, I thought that there was an easy way to convert a cell ref to its unevaluated value in the formula its being used in. Just asking cause I have about 30 monster formulas with cell referances that I need to change they look like this right now:

Please Login or Register  to view this content.

As always any help is appreciated & TIA

[Pete_UK]

So you want to change AI3, and AJ3 and AK3 (etc.)? To some other column references, or change the row references? Or something else? Please be more explicit.

Pete

[dlow]

The above formula looks like this with all the values from the referanced cells data in it.

Please Login or Register  to view this content.

I thought; & I could be mistaken, that we could highlight a cell ref & use some keyboard shortcut to convert the ref to values in the formula. I am probably confused, but throwing it out there none the less

Thx Pete

[Pete_UK]

I don't know of any keyboard shortcut that will do that for you.

Pete

[dlow]

Please be more explicit

ok, cell A1 has a formula in it 2+2, well I use cell A1 as a ref in another cell: B1
& I also used about 9 other cell refs in B1. So now that I am happy with everything I want to destroy the refs & replace the unevaluated data, so for my example it would be replacing A1 with its contents 2+2 & so on.

sorry for the confusion, & thx again

[dlow]

haha yeah, you must have posted while I was writting the above, ok thx I guess Ill just VBA it.

[dlow]

Create a Module & smack this little diddy in there.

Please Login or Register  to view this content.

Then you can do something like this:

Please Login or Register  to view this content.

from whatever cell & the result will look something like this:

Please Login or Register  to view this content.

Trim the fat with this:

Please Login or Register  to view this content.

Result is perfection mixed with sugar & spice & everything nice.

Please Login or Register  to view this content.

Top that off with a little concatenation statement & now everything updates in realtime. No worries. :D

Where I found the info & more info on GetFormula

Marking this off as Solved, but if you have a better way then please by all means do share.