Change relative references in formulas (in an area) into absolute

A simple Google search... https://support.microsoft.com/en-us/kb/116028

Surely that should have been the first option to try?

Hi Marjatta,

Is the F4 physically broken? Do any of the other "F" keys work?

In my search to help answer your question I found:

"F" Lock on keyboard
https://support.microsoft.com/en-us/kb/894513

Some function keys or keyboard shortcuts do not work correctly on a computer that is running Windows 7
https://support.microsoft.com/en-us/kb/975741

HP Notebook PCs - Disable The Fn + Function Key Combination on Certain Models
http://support.hp.com/us-en/document/c02035108


Personally I would try and figure out why the key isn't working properly.

I tried using the macro suggested by @cytop. I don't think it is useful if you have a formula with a mix of relative/absolute references.

Wish I had a better answer. Good luck.

Hi,


F4 works OK but changes the screen to projector etc. Some of the sites you proposed suggests to press FN smmultaneously with F4 but in my Toshiba Satellite with Windows 8.1. it just does not work (neither in a few previous ones with 8.1. - I guess they were HP)

This macro I was given is still running since the minute I gor the answer - rather slow.


I once, a few years ago, placed the same question and got a shorter and quicker macro....lost into the sewers of crashed laptops. Unfortunately.

Well, I'll keep waiting till the macro finishes my rather large area - maybe another 2 hrs?

Marjatta

rather slow.

Other than saying "a large table", you gave no indication of the amount of data to be processed. 'Large' is a subjective term. Someone more used to working with 100 rows of data might call a 1,000 row table 'large' whereas it might be a 'small' table to someone else.

With that in mind, I saw no reason to spend time writing faster, more optimised code, which might process a 'largish' table 5 seconds faster than the code on the Microsoft site.

Try this on a copy of our file.
Select single range (Large or Small). Run this Macro.
Code

Please Login or Register  to view this content.

This Will change formulas to absolute reference.
If required select other range and proceed.

Hello,


thanks to all who have tried to help me,

I tried cytop's first hint - and in something like 5 hours it had got stuck at 54% of the table (or edit-area) of 280 rows x 311columns. Indeed this is not really large – but apparently too large for the macro…

In addition to that, both this one (when tested on a small area), and the on by kvsrinivasamurthy and Tony's (1st answer/top macro – I don't find it though any more) handled the following formula correctly:

"=IF('[SEMESTER I + SEMESTER II IN 2013-2014 _2013-2014 Cohort.xlsx]INPUT SEM I'!M127="","",UPPER('[SEMESTER I + SEMESTER II IN 2013-2014 _2013-2014 Cohort.xlsx]INPUT SEM I'!M127))"

But:
"=IF('[SEMESTER I + SEMESTER II IN 2013-2014 _2013-2014 Cohort.xlsx]INPUT SEM I'!L127="INC","-",IF('[SEMESTER I + SEMESTER II IN 2013-2014 _2013-2014 Cohort.xlsx]INPUT SEM I'!L127="-","-",ROUND(UPPER('[SEMESTER I + SEMESTER II IN 2013-2014 _2013-2014 Cohort.xlsx]INPUT SEM I'!L127),0)))" the "result" becomes #VALUE! = it destroys the original formula.

Really, it seems I have to give up ;(,

Marjatta N-B

Is it possible to upload a sample file.

Hi,
thanks for sustained interest.

It is rather hard to do that - the files are just too big - one email would not easily be enough and if I send separately, the cross-references would mix up. Actually I decided finally to use quite another
process instead of a nice and "quick" macro.

I believe I can use these macros I got here for some simpler tables in future - thank you all for contributions. It seems that the complicated formulas were too much (the simpler ones did all right after all...)