Formula Help (or autofill)

[RichC]

I'm trying to figure out how to have Excel autofill something for me.

.......A..........B........C.......D........E..........F.........G.........H........
1....X..............................X................................X................
2
3....X...........X........X
4
5

Okay, in row 1, I have some values, but there's only something in every
third value. I'd like to take out some of the empty space further down
the worksheet, so it's easier to see, such as in row 3. How do I have
Excel autofill the entire row 3, instead of me having to type in those
values manually. In other words, A3 should return A1, B3 should return
D1, C3 should return G1. With every successive cell in row 3, the
formula should increase by 3 columns (in A). I've tried using the fill
handle, but it doesn't return what I want it to return.

 

[Dave Peterson]

If this is just to view the data, I think I'd take a different approach.

You could use a couple of views to hide/show your columns.

First, make a view that you consider the "normal" view.

View|Custom View|Add button (and give it a nice name).

Then hide those in-between columns. (Just click on the first column to hide and
ctrl-click on the subsequent.) Then do the View|Custom view stuff and give it a
different nice name.

Then you can swap between them by using View|Custom View|Show.

You could even use a macro to hide the columns, but I think I'd do it manually
just in case there are exceptions to the every third column rule:

Option Explicit
Sub testme01()

Dim iCol As Long
Dim dummyRng As Range
Dim LastCol As Long
With ActiveSheet
Set dummyRng = .UsedRange
LastCol = .UsedRange.Columns(.UsedRange.Columns.Count).Column
.Columns(1).Resize(, LastCol).Hidden = True
For iCol = 1 To LastCol
If iCol Mod 3 = 1 Then
.Columns(iCol).Hidden = False
End If
Next iCol

End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[RichC]

Unfortunately, I have to do some calculations on the values that appear
every third row. Bringing them down just makes the calculation a
little easier. The view is a good idea, though. I can probably use it
elsewhere on my spreadsheet.

 

[Tom Ogilvy]

in A3
=OFFSET($A$1,0,(COLUMN()-1)*3)

drag fill to the right.

--
Regards,
Tom Ogilvy