Noncontigous Cells

[Mahmoud Metwally]

Dear
I want to make referene from from sheet to another in non-contigous cells
( like.A11, A21,A31,A41,....)
When I drage the cell, it just refrence contigous cells ( like
A11,A12,A13,....)
So, what is the function that I use to do that?

 

[Ron de Bruin]

If I understand you correct you can use formulas in "Sheet2" like this
=Sheet1!A11
=Sheet1!A21

Select both cells and copy down

 

[Bob Phillips]

I am reading this somewhat differently from Ron, and if I understand
correctly, you need to reference each explicitly, such as

=SUM(Sheet2!A1,Sheet2!A10)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Max]

Yet another take <g> on the post ..

Assuming you want to extract what's in Sheet1's A11, A21, A31 ...
(i.e. what's in every 10 cells down from/inclusive A11 in Sheet1)

In Sheet2
-------------

In any starting cell, say A2?

Put in A2: =OFFSET(Sheet1!$A$1,ROW(A1)*10-10+10,)
Copy down as desired

A2 will return what's in Sheet1's A11
A3 will return what's in Sheet1's A21
A3 will return what's in Sheet1's A31
and so on ...

Adapt to suit .. e.g. :

Change "ROW(A1)*10-10" to "ROW(A1)*20-20"
if the interval is 20 instead of 10

and adjust the "+10" (just an arithmetic adjustment)
depending on where the start cell in Sheet1
(in the case above, A11) is located

 

[Max]

If I understand you correct you can use formulas
in "Sheet2" like this
=Sheet1!A11
=Sheet1!A21

Select both cells and copy down

Got these results when I did that, Ron <g>

=Sheet1!A13
=Sheet1!A23
=Sheet1!A15
=Sheet1!A25
etc

 

[Ron de Bruin]

Sorry : Forget my answer

 

[Ron de Bruin]

Hi Max

I just got home from the hospital.
Small operation (I think they also destroy my brains <vbg>)

 

[Max]

... (I think they also destroy my brains <vbg>)

LOL ! Wish you a speedy recovery, Ron <bg>

 

[Max]

Oops, typo, sorry ..

Line

A3 will return what's in Sheet1's A31

should read as:

 

[Ron de Bruin]

LOL ! Wish you a speedy recovery, Ron <bg>
Thanks Max

I have a lot of pain but I am glad I am home.

 

[Alan Beban]

Dear
I want to make referene from from sheet to another in non-contigous cells
( like.A11, A21,A31,A41,....)
When I drage the cell, it just refrence contigous cells ( like
A11,A12,A13,....)
So, what is the function that I use to do that?

Hold down the Ctrl key and select each the noncontiguous cells; click on
the Name Box in the upper right portion of your sheet; insert a name
(e.g., noncontig) and hit the Enter key. You can then enter something
like, e.g.,

=SUM(noncontig) to get the sum of the values in the noncontiguous cells.

Alan Beban

 

[Alan Beban]

Hold down the Ctrl key and select each the noncontiguous cells; click on
the Name Box in the upper right portion of your sheet; insert a name
(e.g., noncontig) and hit the Enter key. You can then enter something
like, e.g.,

=SUM(noncontig) to get the sum of the values in the noncontiguous cells.

Alan Beban

Oops! The Name Box is in the upper left.

Sorry,
Alan Beban