Noncontigous Cells

  • Thread starter Thread starter Mahmoud Metwally
  • Start date Start date
M

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?
 
If I understand you correct you can use formulas in "Sheet2" like this
=Sheet1!A11
=Sheet1!A21

Select both cells and copy down
 
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)
 
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
 
Ron de Bruin said:
If I understand you correct you can use formulas
in "Sheet2" like this
=Sheet1!A11
=Sheet1!A21

Select both cells and copy down
Click to expand...

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

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

I just got home from the hospital.
Small operation (I think they also destroy my brains <vbg>)
 
LOL ! Wish you a speedy recovery, Ron <bg>
Thanks Max

I have a lot of pain but I am glad I am home.
 
Mahmoud said:
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?
Click to expand...

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 said:
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
Click to expand...
Oops! The Name Box is in the upper left.

Sorry,
Alan Beban
 
Back
Top