Excel - referencing columns/rows in worksheets programatically

roves1

Hi,

I have two worksheets in Excel 97, 1st one has a column of values, second one has the same values in a row (referenced from the column in 1st worksheet).

Is there any way to reference the column values across a row automatically without manually typing the reference in, so I can just copy and paste (drag) the incrementing reference.

Also, would like to have a column head of 1/1/10, 1/2/10 etc, is there a way of adding a complete month by dragging this across the column (if you drag it, it adds a day and you can't add 31.x days because of the changing days/month).

Thankyou very much for all the replies, both problems now solved.

I Had syntax/referencing problems with more4me's solution but it pointed me in the right direction. Got there eventually after taking out the ($A$1&) and forgetting about the worksheet2! in a1, which it didn't accept.

john87_2

dates:

If you type 1/1/2010 in the first cell, 1/2/2010 in the next cell.
Select both cells then grab the bottom right of the selection and drag.
This works in rows or columns.

KingL

roves1 wrote: »

I have two worksheets in Excel 97, 1st one has a column of values, second one has the same values in a row (referenced from the column in 1st worksheet).

Is there any way to reference the column values across a row automatically without manually typing the reference in, so I can just copy and paste (drag) the incrementing reference.

It's not exactly what you're after, but if the values in the the column and the row are to be identical, you can copy the column and then edit>paste special>values & check the "transpose" box (and if necessary, afterwards, paste special>formats [with transpose checked]).

You'll get values rather than cell references, but it'll look the same....

More4me

Here is a formula based solution
Assuming you have your column values in Sheet1 A1:10
and you want to transpose them on Sheet2 B1:K1, I have deliberately left out row A to enter sheet reference.
In cell A1 in Sheet2 type Sheet1! or what ever worksheetname!
at cell A2 (sheet2) enter following formula
=INDIRECT($A$1&ADDRESS(ROW($A$1)+0,(COLUMN(($A$1)))))
At cell B2 (Sheet2) enter following formula
=INDIRECT($A$1&ADDRESS(ROW($A$1)+COLUMN(A1),(COLUMN(($A$1)))))
Copy above formula across to K1.

ScratchandSniff-->[X]

john87 wrote: »

dates:

If you type 1/1/2010 in the first cell, 1/2/2010 in the next cell.
Select both cells then grab the bottom right of the selection and drag.
This works in rows or columns.

Or drag autofill on any one date with RMB and do fill months

ScratchandSniff-->[X]

KingL wrote: »

It's not exactly what you're after, but if the values in the the column and the row are to be identical, you can copy the column and then edit>paste special>values & check the "transpose" box (and if necessary, afterwards, paste special>formats [with transpose checked]).

You'll get values rather than cell references, but it'll look the same....

If you have (free for non-commercial use) ASAP Utils (different version for Excel 97, look at bottom of download menu) try from menu choose ASAP Utilities » Range » Transpose data with formulas...