Is it possible to use the Column function in a formula?!

[Lucilla]

Hi,

I am try to do what I supposed to be a "normal" thing, but I am in truble!

is there a way in excel to specify the reference of a cell in another sheet with a formula and not with fixed columns or rows?

In the specific I have two sheets and I would like to link the cell using this "formula":

Sheet2!C1=Sheet1! ((COLUMN(C1)+3)/2) & "1"

that means

• in cell C1 of Sheet2 takes the value in Sheet1 in cell (3+3)/2 -> 3->C
• in cell E1 of Sheet2 takes the value in Sheet1 in cell (5+3)/2 -> 4->D
• in cell G1 of Sheet2 takes the value in Sheet1 in cell (7+3)/2 -> 5->E
• ...

the point is that I don't know how to make the column reference a formula and not a specified value.
I would like to NOT use macros...

is it possible!?

Thanks a lot!

 

[FormR]

One way maybe:

=INDIRECT("Sheet1!"&ADDRESS(1,(COLUMN(C1)+3)/2))

 

[pjmorris]

Hi All,

Didn't know about 'Address' until FormR's post, but see it could also be used in this form: =INDIRECT(ADDRESS(1,(COLUMN(C1)+3)/2,,,"Sheet2"))

Thanks FormR.

Regards

 

[pgc01]

Hi

Another option, in Sheet2!C1 (or Sheet2!E1, or Sheet2!G1)


=INDEX(Sheet1!1:1,(COLUMN()+3)/2)

 

[Lucilla]

You Guys are Genius!!!!

Thanks a lot, I knew I should come in this forum earlier instead of become creazy for all the morning!!
All the three solutions work perfectly!
Thanks!