Can record a Macro that "make F4" on a cell? 2

[terzaghi]

Hi,

I have a table in one worksheet. In anoter worksheet I link the firts cell on the left on top. I erase the dollars in the formula and I drag and drop the cell so I can import in this new worksheet the table.
They are made something like:

=[Book3]Sheet1!A1
=[Book3]Sheet1!B1
.....

Now I should like to put, in every formula of this table the dollars again without going in every cell and pressing F4 three times.

Thanks in advance.

Andrea

 

[SkipVought]

terzaghi,

Use Currency cell formatting!




Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DrBowes]

I think the $ refer to absolute formatting, not currency. The $ are preventing dragging showing relative formulae?

You can use find and replace, replace $ with nothing (match entire cell to false, lookin formulas) and they will all go, and there's your relative formulae!

The find and replace is massively useful for a variety of purposes, especially if you use the wildcard * also...

DrB

 

[terzaghi]

I express better my thinking.

let's do this:

in a sheet I put this table:

A1: 100
B1: 200
A2: 1000
B2: 90


in another I put the formula: ='Sheet1'!A1
then I drag the cell down one and right one.
Now I linked the table form the first sheet and I imported the data in the second one.

I have the followings formulas:
='Sheet1'!A1 ='Sheet1'!A2
='Sheet1'B1 ='Sheet1'!B2


I would like to transform the links in theese:
='Sheet1'!$A$1 ='Sheet1'!$A$2
='Sheet1'$B$1 ='Sheet1'!$B$2

and, becouse of I want to create a re-usable macro, I want that the macro is indipendent from the actual cell you are linking.
Now I want to to this with cell A1, tomorrow with cell Z884

If you record a macro and do this:

1. click on the cell A1,
2. Press F2
3. Press F4
4. end macro.

the macro you really obtain is a macro that transofrm the formula with dollars bat really link THAT PARTICULAR cell.

I don't want that.

Thanks in advance.

Andrea

 

[terzaghi]

DrBower,

I want to do the opposite: from relative formulae I want ALL absolute formulae linking to different cells!

I can also use OFFSET but when you link this way someting like 10.000 cells Excel becomes really slow....

 

[SkipVought]

terzaghi,

Oh, ABSOLUTE CELL REFERERCE!

So tell me why you need absolute cell references in your table?


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[terzaghi]

Becouse of the fact that I have tables of 60 rows for 12 columns (months!!)
I have to build up something like 25 different of these tables every one linking to a different cell of the same groups of sheets

So having all the 60x12 links absolute would be a great thing!

 

[SkipVought]

I can give you a macro for making absolute references. But let me make some comments.

Seems like you have some common data that needs to be reported across months. This sort of reporting can be done quite nicely with pivot tables, given that the source data is in a normalized table format. BTW, across months is NOT a normalized table format, it's a reporting technique.

Having a single data source (rather than multiple copies of the same data) makes it possible to reduce maintenance and increase performance.

I would STRONGLY suggest using this professional approch rather than an unsophisticated user shortcut approch (BUT in the longrun more probelms).

Here's the code.

Select the range you want to replace and run this code...

Sub AbsCellRef()
    For Each c In Selection
        With c
            If .HasFormula Then _
                .Formula = "=" & Range(.Formula).Address
        End With
    Next
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[terzaghi]

Hi skip.

I agree with you.

Let me explain my model:
I'm making the budget. I work in a telco Company that has a very detailed model for customers. We have 4 different segments, 9 different region and 2 different technologies to reach each segment. so it makes 72 different type of customers. Each type has is own drivers (6 different numbers for ARPU) and Cost of Goods Sold (6 different Numbers) Each customer in each segment can be in different state (acquired, activated, churned, and so on) tha makes 10 different states.

So I have 72 sheets each of that dealing with a type of customer. Each sheets is a model that calculates how many customers of each type, how many revenues, how many Costs and so on. They are very complex. After that I have to summarize all the data of theese 72 excel sheets in a database to analyze them with OLAP techniques. So the fact is that your suggestion about pivot tables is not applicable in the first part of the model in which we "generate" the numbers and we use OLAP databases at the palce of pivot tables... I built up some macros that can get data from the same cell in different sheets (something like an OFFSETSHEET if you like. But the $A$1 system would be very nice.

I saw your macro. It's fine. If I have a formula like ='Sheet1'!A1, your macro transforms it to =$A$1. Nice with dollars but the reference to another sheet is lost!

Thank you anyway!!

 

[terzaghi]

Hi Skip,

I used your macro to do this..

it's a bit elaborate but it works:

Sub Absolute_Cell_Reference()
Dim c As Range
Dim Formul As String
Dim NewFormula As String
Dim i As Integer

For Each c In Selection
With c
If .HasFormula Then
i = 1
Formul = .Formula
While Mid(Formul, i, 1) <> &quot;!&quot;
NewFormula = NewFormula + Mid(Formul, i, 1)
i = i + 1
Wend
NewFormula = NewFormula + &quot;!$&quot;
i = i + 1
While Not IsNumeric(Mid(Formul, i, 1))
NewFormula = NewFormula + Mid(Formul, i, 1)
i = i + 1
Wend
NewFormula = NewFormula + &quot;$&quot;
While i < Len(Formul)
NewFormula = NewFormula + Mid(Formul, i, 1)
i = i + 1
Wend
.Formula = NewFormula
End If
End With
Next
End Sub

Thenk your for your suggestion!!!

 

[SkipVought]

terzaghi

Well then you table needs to include all those elements as well...

Segment
Region
Technology

There's NOTHING that you have stated that changes the basic approch. Each report is a query based on the complex parameter mix requirement for each report coming out of a single table or several joined tables.

72 sheets? What a MESS!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[terzaghi]

only one thing....

every one of theese sheet is very complicated so, when I'm in one of theese I cannot deal also with problems like &quot;getting the correct data from a pivot table&quot;.

The output of theese models are then put in a database and you can do all the OLAP analisys you like.

thank you again!

Andrea

 

[PatBo]

I have managed to do this with multiple workbook, sheet and cell references by using search and replace.

I searched !H and replaced with !$H$, thus transforming all references to absolutes - wonderful. Thank you DrBowes for pointing me in the right direction.

Slán agus beannacht.
Patrick Boland