Need Some Beginner's Help

[Matt]

This is probably a basic question but I can't seem to find it in the help
menu...In Excel 2003 let's say that I have one cell that has =B1 if I use
the auto fill and drag this cell to the right I get =C1 =D1 =E1 etc... can
I configure the auto fill so that it gives me =B2 =B3 =B4 instead?

 

[Trevor Shuttleworth]

Put:

=INDIRECT("B"&COLUMN()-2)

in cell C1 and drag across

Regards

Trevor

 

[Bob Phillips]

=INDIRECT(ADDRESS(COLUMN(A1),2))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JE McGimpsey]

Assuming your formula is entered in C1:

=INDEX($B:$B,COLUMN()-2)

Adjust for whichever column you're starting in.

 

[Matt]

=INDIRECT(ADDRESS(COLUMN(A1),2))

--

Thanks Bob and Trevor!

 

[George Nicholson]

No. autofill won't work that way, but here are 2 methods to get the results
you want:

1) Put '=$B1' (w/o quotes) into a cell. (Note the $, which will make the
column reference Absolute rather than Relative).
2) Use AutoFill and drag that cell DOWN as many rows as the # of values you
need across.
3) Copy the entire range you created in 1&2
4a) Select the destination.
- Destination and Source range can't overlap since we're changing the
shape of the range
4b) Edit>PasteSpecial, check "Transpose", Click OK
5) Delete/Clear the unwanted stuff from 1&2.

2nd approach:
1) fill a horizontal set of cells with the row numbers you want. (Ex:
1,2,3,4 in Cells D1:G1).
2) Put the following into D2 & copy across to Column G
= "=B"&D1
- D2:G2 should now display the desired cell references as text.
3) Copy D2:G2
4) Select a destination, Edit>PasteSpecial>Values
5) Tab through the destination range, hitting F2 for each cell to "convert"
the "cell-reference-as-sort-of-text" to "value-of-cell-being-referenced".
(F2-Tab-F2-Tab, etc.). After this, any changes made in column B will appear
in the destination whenever the sheet is recalculated (which depends on your
Tools>Options>Calculation settings)

HTH,