Relative vs. absolute references 1

[karron]

Does anyone know if it is possible to highlight a section of data in a spreadsheet and make every number absolute, without having to click on number and press F4?

 

[NilsBevaart]

You can copy a complete range and paste it with paste special. Here you can specify to only paste the values.


Nils Bevaart

 

[karron]

thankyou but i need the formular to remain within the cells and also i need my references to remain the same.

 

[GlennUK]

If you want to duplicate a range, formulae included, exactly as is, then convert the formulae to text, copy, and convert both source and destination back to formulae.

Select the range to copy. Do menu command Edit/Replace, changing = to #, Replace All, which converts the formulae to text. Copy, paste in whatever location you want.

After pasting, do Edit/Replace on both the source location and destination, changing # to = and you are finished.


Hope that's what you want.

Glenn.

 

[pbrodsky]

Frequently, if you change to R1C1 notation (tools/options/general), if becomes effective to use edit/replace on the formulas to get the effect you want. eg. replace r[-4] with r17 for a range at a time.

 

[KenWright]

Try the following:-

Sub ConvFormula()
For Each Cell In Selection
Cell.Formula = Application.ConvertFormula(Cell.Formula, xlA1, xlA1, True)
Next Cell
End Sub

Regards
Ken..............

 

[SkipVought]

karron,

Excel has a really neat feature called Range Names. There is a box just aboe cell A1 called the Name Box. Here, you can name a range selection and then refer to that name in any formula.

I use Range Names almost exclusively in my spreadsheet applications. It saves alot of time and is somewhat self documenting.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Ken, very nice.

I had not noticed

Application.ConvertFormula

before. I was always converting to text, copying and converting back. This will be a real time-saver.

Definitely worth a star from me.

 

[PeterMoran]

Hi,

As an addendum to this interesting discussion can I suggest that this facility to change from relative to absolute addressing, with specific options etc etc., along with a mind boggling set of other tools, is included in ASAP Utilities - freeware! Check out:

http://www/asap-utilities.com

Good Luck!

Peter Moran
Two heads are always better than one!

 

[KenWright]

LOL - Hi Zathras - I confess - Nor had I till Chip Pearson told me how

Regards
Ken.............

 

[stoke]

outstanding Ken/Chip!
This saves me huge amounts of time.

is there a quick way to name cells / range? as i name cells by typing them individually......

 

[KenWright]

All depends on where the names are for those individual cells. Suppose you had data in A2:X2 and in A1:X1 you had the names that you wanted to use for each of those cells. Select A1:X2, do Insert / Name / Create - Create Names in 'Top Row'. You will see that yopu have the option of Top Row, Bot Row, Left Col, Right Col so that you can get them from pretty much anywhere. Works with ranges greater than single cells too.

Just as an addendum, did you also know that say you had a matrix of data in say a 10 x 10 grid, and that you had used the previous to name each row of data and each col of data, and just assuming you had named them Col1, Col2, Col3 and Row1, Row2, Row3 etc. You can then get the value of the intersection of those ranges simply by typing in a cell =Col2 Row3 (Note the space between the two names).

Regards
Ken...........