h2g2 The Hitchhiker's Guide to the Galaxy: Earth Edition

Anyone any idea how I'd change relative references into absolute references

i.e.: =SUM(A4,A10)
into: =SUM($A$4,$A$10)

Now, I can do it one formula at a time by entering each formula and pressing F4 - the trouble is, I've a page half a mile long to do that in and I'd appreciate a way of doing it automatically for a selected number of boxes.

Heellllppp!!!

macros should do the trick, although u might have to be a bit careful..

Any chance of being a little more specific?

*listens up cos she has tried and failed to make a macro "move" to another part of the sheet before now....*

1. Tools -> Macro -> Record New Macro
2. Assign it a shortcut key (say Ctrl + W)
3. Turn Relative referencing on (This is for the macro only)
4. Go through the steps to convert all references in the current cell to make them absolute (i.e. F2 to edit the current cell, F4 to make the references absolute, move the cursor to the next place in the current cell, F4, repeat until current cell is done. Hit enter. this will move the cursor to the next cell in the same column. move to the next cell if required)
5. Stop recording macro
6. Hold down Ctrl+W till you've gone through all your cells

Warning.... im assuming that all your sums are entered in the same way...

Are you trying to edit cells on the same row or column?
If you are editing cells with sums in different rows and columns, is the number items added different for each total?

Also what is the purpose for changing to absolute references?

You really want to know what I'm trying to do?

Ok, I've an excel file with about 30 tabs... each one is identical in layout but has different contents (figures) and is not formatted for printing (it's designed to be used on the screen.

Some of the data from each tab has to be sent to a customer at the end of the month - whilst some of it is confidential to our company.

So, what I've done is added an additional tab, and built a table referencing each cell in the one of the first 30 tabs.

I now want to take this table and copy it, further down the spreadsheet, and using find & replace I can replace the reference to the first of the 30 tabs with the reference for the second...

Repeat this thirty times and I have a _very_ long spreadsheet - formatted for printing (to a PDF) with an extraction of the information entered into the other 30 tabs.

The trouble is, in the first table, to avoid having to manually enter the reference in each cell (10 rows by 50 columns of figures) I've used relative references so I can just copy/paste them...

Now, if I leave them as relative references and try to copy the entire table and dump it lower down the spreadsheet - Excel tries to be a smart alec and re-directs all my references to an empty space well under the data I want it to pick up.



In pictures...

Sheet 1 includes

A B C D
E F G H

Sheet 2

I J K L
M N O P

etc...

Then, my final sheet for the customer looks like

Sheet 31

A B _ D
E F _ H

I J _ L
M N _ P

I type in the reference on Sheet 31 under the 'A' and it looks something like
="sheet 1"!A1

I then copy it and paste it into the spaces under the other characters
(B, C, D, E, F, G, H) - delete the formulae from the third column and hey presto, I've got the first table...

Then comes the problem...

The easiest way to do what I want to do is

copy:
A B _ D
E F _ H

and paste it further down the sheet
search for "sheet 1" and replace by "sheet 2"

and it should look for the same data from sheet 2

HOWEVER, coz I used relative references, what I actually get, instead of
="sheet 2"!A1

I actually get

="sheet 2"!A51

Hence the reason for changing the references from relative to absolute before copying and pasting the whole table


Sorry you asked now?

No, not yet.
What you want to do is copy the cell and use 'paste special.'
Then select 'link.'
That will link the cell on the last sheet to the cell on the refered tab. When you copy and paste the table to another part of the sheet it will still reference the link-cell.

Let me know if I explained that enough.

Nope, but that could have something to do with me having EXCEL in French

Ok, what I did was

The contents of Cell A10 is: =A1
Copy Cell A10
Paste to Cell A20 - I get a result of: =A11

Now, when I press the 'paste special' option...

In the bottom right corner of the popup is a button marked "coller avec liaison" (Paste with link)... If I use that I get a result of =$A$10


The trouble is, what I'm actually looking for is the pasted version to come back as =A1 again

Hope this isn't too late, but I have was away from the internet for the last 5 days.

Now if cell A10 = Cell A1 then when you get =$A$10, you get =$A$1
When you print the results you will see the same value, so there would be no need to make the reference of the new cell show =A1.