What is the simplest way to enter a sequence of values such as =SUM
(A1:A100),=SUM (B1:B100),=SUM (C1:C100), etc. into a COLUMN?

What is the simplest way of converting simultaneously such values to
($a$1:$a$100) etc.?

I could only find a slow and clumsy method by putting the formula in a row,
right dragging, then using F4 on each individual cell before using paste
special with transpose.

Roger PB

Hi Roger,

What is the simplest way to enter a sequence of values such as =SUM
(A1:A100),=SUM (B1:B100),=SUM (C1:C100), etc. into a COLUMN?


=SUM(OFFSET($A$1,0,ROW()-1,1,100))

Assuming you start on row 1. If starting on e.g. row 2:

=SUM(OFFSET($A$1,0,ROW()-2,1,100))

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Roger

For this you can use VBA macro(s)

Here are 4 macros. Use whichever you deem appropriate.

For your example, Sub Absolute() would be the one.

Sub Absolute()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula(Cell.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

Sub AbsoluteRow()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula(Cell.Formula, _
xlA1, xlA1, xlAbsRowRelColumn)
Next
End Sub

Sub AbsoluteCol()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula(Cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
Next
End Sub

Sub Relative()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula(Cell.Formula, _
xlA1, xlA1, xlRelative)
Next
End Sub


Gord Dibben Excel MVP

On Wed, 23 Mar 2005 07:38:56 GMT, "Roger PB" wrote:

What is the simplest way of converting simultaneously such values to
($a$1:$a$100) etc.?

I could only find a slow and clumsy method by putting the formula in a row,
right dragging, then using F4 on each individual cell before using paste
special with transpose.

Roger PB

Oh dear, is there nothing simpler?

I tried Jan Karel's solution.But first I ran into the problem that for some
reason my version of Excel demands German syntax, i.e. it does not
understand commas and demands semi.colons. Similarly, it demands dates to be
formatted tt, mm, jj, rather than dd, mm,yy, and to date I have found no
way to change this. However, the help screens use English syntax!

Anyway,having entered the modified formula, when I dragged it down the
column, it simply repeated itself, and did not adjust for b, c, etc.

I am a beginner as regards VBA programming, which is why I sent my query to
this newsgroup rather than the excel programming group. But Gord Dibben's
solutions I find useful ,especially as the syntax regarding changing
absolute to relative formulas or vice versa.

Hi Roger,

I tried Jan Karel's solution.But first I ran into the problem that for some
reason my version of Excel demands German syntax, i.e. it does not
understand commas and demands semi.colons.

I know about the semicolons, I have to use them too (Dutch settings). I simply
assumed English in this case.

There is a number of translation tools to translate functions from English to
German, one of them is simply a list. Find xlMenufundict.zip at:

www.jkp-ads.com/Download.htmxlMenufundict

Similarly, it demands dates to be
formatted tt, mm, jj, rather than dd, mm,yy, and to date I have found no
way to change this. However, the help screens use English syntax!

What does this have to do with your original query?
Excel uses the dat starting letters of your local language setting on Regional
settings for the formatting string. So if you want them to be english, you need
to set your regional settings to english.

Anyway,having entered the modified formula, when I dragged it down the
column, it simply repeated itself, and did not adjust for b, c, etc.

But it should POINT to the right ranges, the $A$1 part SHOULD not change, it is
the ROW part that adjusts the range.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Hi Jan Karel,

Manuy thanks for pointing me in the right direction. And for
Findxlmenufundict. zip.

The dates problem relates to my original query in that both the German
syntax and ttddjj settings stemmed from the regional settings. I have
succeeded in changing these to English, thanks to your tip.

I have now also got the formula to work: The top row entry reads
=SUM(OFFSET($A$1,0,ROW()-1,27,1))
and sums A1:A27. I found it rather odd that the formula looks identical
fthroughout the column, but it works.

In the Help menu I found the syntax
OFFSET(reference,rows,cols,height,width)
and two examples
=OFFSET(C3,2,3,1,1) Displays the value in cell F5 (0)
=SUM(OFFSET(C3:E5,-1,0,3,3)) Sums the range C2:E4 (0)

But nowhere could I find what ROW() signifies.

Does $A$1,0,ROW(),represents A1 with no row offset- the zero value- and a
column offset of ROW()-1?
If this is equal to 0, am I right in assuming that ROW() has the same value
as the row that it is placed in, i.e. 1 in row 1, 2 in row 2 etc ?

Many thanks for your patience and help.

Roger PB

Hi Roger,

But nowhere could I find what ROW() signifies


ROW() returns the rownumber of the cell the function resides in, so
=ROW()

in Cell A63 returns 63.

Since I used the ROW() function as the column argument for the offset,
dragging the formula down forces an increase in the column argument of
the offset function. SO in fact when you move down one row, the formula
in that row points one column further to the right than the one
immediately above.

Clear as mud?

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com