get data from a cell in each named range
 

I have several named ranges, all the same shape and size.

In another workbook I want to pull through the data from the cell on the 3rd
row, 4th column of each named range.

I don't want to refer to the absolute cell reference as they might get moved
about.

Is this something that can be done easily?

Thank you

get data from a cell in each named range
 

Instead of using cells(1,1) use your range name such as mine here selects 3rd
row 4th column of my range:

Set myrng = Range("A1", "D10")
myrng.Select

MsgBox (myrng(3, 4))




--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Shazzer" wrote:

I have several named ranges, all the same shape and size.

In another workbook I want to pull through the data from the cell on the 3rd
row, 4th column of each named range.

I don't want to refer to the absolute cell reference as they might get moved
about.

Is this something that can be done easily?

Thank you

get data from a cell in each named range
 

Hi

=INDEX(RangeName,3,4)


Arvi Laanemets



"Shazzer" wrote in message
...
I have several named ranges, all the same shape and size.

In another workbook I want to pull through the data from the cell on the
3rd
row, 4th column of each named range.

I don't want to refer to the absolute cell reference as they might get
moved
about.

Is this something that can be done easily?

Thank you

get data from a cell in each named range
 

Hi again

I missed that all source ranges are in external workbooks - I didn't check
this out, but probably you can't refer to ranges in closed external
workbooks, and I'm not sure that INDEX works with them as source too.

Easiest will be to have a hidden sheet in every of source workbooks, where
the value from range is calculated in certain cell, using p.e. formula from
my previous posting. Then you can use simply link formulas to get those
values into your summary workbook.


Arvi Laanemets


"Arvi Laanemets" wrote in message
...
Hi

=INDEX(RangeName,3,4)


Arvi Laanemets



"Shazzer" wrote in message
...
I have several named ranges, all the same shape and size.

In another workbook I want to pull through the data from the cell on the
3rd
row, 4th column of each named range.

I don't want to refer to the absolute cell reference as they might get
moved
about.

Is this something that can be done easily?

Thank you

get data from a cell in each named range
 

Absolutely brilliant!! This will save me so much time. Thank you v much
indeed

"Arvi Laanemets" wrote:

Hi

=INDEX(RangeName,3,4)


Arvi Laanemets



"Shazzer" wrote in message
...
I have several named ranges, all the same shape and size.

In another workbook I want to pull through the data from the cell on the
3rd
row, 4th column of each named range.

I don't want to refer to the absolute cell reference as they might get
moved
about.

Is this something that can be done easily?

Thank you

get data from a cell in each named range
 

Oh dear. The references don't update when you drag down the way they do with
normal cell references. Any ideas?!?

Thank you

"Arvi Laanemets" wrote:

Hi again

I missed that all source ranges are in external workbooks - I didn't check
this out, but probably you can't refer to ranges in closed external
workbooks, and I'm not sure that INDEX works with them as source too.

Easiest will be to have a hidden sheet in every of source workbooks, where
the value from range is calculated in certain cell, using p.e. formula from
my previous posting. Then you can use simply link formulas to get those
values into your summary workbook.


Arvi Laanemets


"Arvi Laanemets" wrote in message
...
Hi

=INDEX(RangeName,3,4)


Arvi Laanemets



"Shazzer" wrote in message
...
I have several named ranges, all the same shape and size.

In another workbook I want to pull through the data from the cell on the
3rd
row, 4th column of each named range.

I don't want to refer to the absolute cell reference as they might get
moved
about.

Is this something that can be done easily?

Thank you

get data from a cell in each named range
 

Hi

Have you set calculation to manual?

Or do you mean that some user works with external workbook, and you don't
see changes immediately - don't worry, you won't. Links to external
workbooks are refreshed, when you open the workbook (you have to confirm the
refreshing), or manually from EditLinks menu.

Arvi Laanemets


"Shazzer" wrote in message
...
Oh dear. The references don't update when you drag down the way they do
with
normal cell references. Any ideas?!?

Thank you

"Arvi Laanemets" wrote:

Hi again

I missed that all source ranges are in external workbooks - I didn't
check
this out, but probably you can't refer to ranges in closed external
workbooks, and I'm not sure that INDEX works with them as source too.

Easiest will be to have a hidden sheet in every of source workbooks,
where
the value from range is calculated in certain cell, using p.e. formula
from
my previous posting. Then you can use simply link formulas to get those
values into your summary workbook.


Arvi Laanemets


"Arvi Laanemets" wrote in message
...
Hi

=INDEX(RangeName,3,4)


Arvi Laanemets



"Shazzer" wrote in message
...
I have several named ranges, all the same shape and size.

In another workbook I want to pull through the data from the cell on
the
3rd
row, 4th column of each named range.

I don't want to refer to the absolute cell reference as they might
get
moved
about.

Is this something that can be done easily?

Thank you

get data from a cell in each named range
 

Hiya. No I mean when I copy the formula to the next cell. i.e. if you copy
=a2 down 1 cell it will calculate =a3 but when you copy 1,2 down it doesnt
update to 1,3.

Sorry, hope that makes sense now.

"Arvi Laanemets" wrote:

Hi

Have you set calculation to manual?

Or do you mean that some user works with external workbook, and you don't
see changes immediately - don't worry, you won't. Links to external
workbooks are refreshed, when you open the workbook (you have to confirm the
refreshing), or manually from EditLinks menu.

Arvi Laanemets


"Shazzer" wrote in message
...
Oh dear. The references don't update when you drag down the way they do
with
normal cell references. Any ideas?!?

Thank you

"Arvi Laanemets" wrote:

Hi again

I missed that all source ranges are in external workbooks - I didn't
check
this out, but probably you can't refer to ranges in closed external
workbooks, and I'm not sure that INDEX works with them as source too.

Easiest will be to have a hidden sheet in every of source workbooks,
where
the value from range is calculated in certain cell, using p.e. formula
from
my previous posting. Then you can use simply link formulas to get those
values into your summary workbook.


Arvi Laanemets


"Arvi Laanemets" wrote in message
...
Hi

=INDEX(RangeName,3,4)


Arvi Laanemets



"Shazzer" wrote in message
...
I have several named ranges, all the same shape and size.

In another workbook I want to pull through the data from the cell on
the
3rd
row, 4th column of each named range.

I don't want to refer to the absolute cell reference as they might
get
moved
about.

Is this something that can be done easily?

Thank you

get data from a cell in each named range
 

Hi

Of course it doesn't. A2 will be a cell reference. 1 and 2 in my formula are
constants. To make them depend on cell position, you have to replace them
with formulas, which return wanted number at right position. P.e.
=INDEX(RangeName,ROW(),COLUMN())


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Shazzer" wrote in message
...
Hiya. No I mean when I copy the formula to the next cell. i.e. if you
copy
=a2 down 1 cell it will calculate =a3 but when you copy 1,2 down it doesnt
update to 1,3.

Sorry, hope that makes sense now.

"Arvi Laanemets" wrote:

Hi

Have you set calculation to manual?

Or do you mean that some user works with external workbook, and you don't
see changes immediately - don't worry, you won't. Links to external
workbooks are refreshed, when you open the workbook (you have to confirm
the
refreshing), or manually from EditLinks menu.

Arvi Laanemets


"Shazzer" wrote in message
...
Oh dear. The references don't update when you drag down the way they
do
with
normal cell references. Any ideas?!?

Thank you

"Arvi Laanemets" wrote:

Hi again

I missed that all source ranges are in external workbooks - I didn't
check
this out, but probably you can't refer to ranges in closed external
workbooks, and I'm not sure that INDEX works with them as source too.

Easiest will be to have a hidden sheet in every of source workbooks,
where
the value from range is calculated in certain cell, using p.e.
formula
from
my previous posting. Then you can use simply link formulas to get
those
values into your summary workbook.


Arvi Laanemets


"Arvi Laanemets" wrote in message
...
Hi

=INDEX(RangeName,3,4)


Arvi Laanemets



"Shazzer" wrote in message
...
I have several named ranges, all the same shape and size.

In another workbook I want to pull through the data from the cell
on
the
3rd
row, 4th column of each named range.

I don't want to refer to the absolute cell reference as they
might
get
moved
about.

Is this something that can be done easily?

Thank you

get data from a cell in each named range
 

Ok thanks. I don't think I can use this method after all but I really
appreciate all your help.

Sharon

"Arvi Laanemets" wrote:

Hi

Of course it doesn't. A2 will be a cell reference. 1 and 2 in my formula are
constants. To make them depend on cell position, you have to replace them
with formulas, which return wanted number at right position. P.e.
=INDEX(RangeName,ROW(),COLUMN())


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Shazzer" wrote in message
...
Hiya. No I mean when I copy the formula to the next cell. i.e. if you
copy
=a2 down 1 cell it will calculate =a3 but when you copy 1,2 down it doesnt
update to 1,3.

Sorry, hope that makes sense now.

"Arvi Laanemets" wrote:

Hi

Have you set calculation to manual?

Or do you mean that some user works with external workbook, and you don't
see changes immediately - don't worry, you won't. Links to external
workbooks are refreshed, when you open the workbook (you have to confirm
the
refreshing), or manually from EditLinks menu.

Arvi Laanemets


"Shazzer" wrote in message
...
Oh dear. The references don't update when you drag down the way they
do
with
normal cell references. Any ideas?!?

Thank you

"Arvi Laanemets" wrote:

Hi again

I missed that all source ranges are in external workbooks - I didn't
check
this out, but probably you can't refer to ranges in closed external
workbooks, and I'm not sure that INDEX works with them as source too.

Easiest will be to have a hidden sheet in every of source workbooks,
where
the value from range is calculated in certain cell, using p.e.
formula
from
my previous posting. Then you can use simply link formulas to get
those
values into your summary workbook.


Arvi Laanemets


"Arvi Laanemets" wrote in message
...
Hi

=INDEX(RangeName,3,4)


Arvi Laanemets



"Shazzer" wrote in message
...
I have several named ranges, all the same shape and size.

In another workbook I want to pull through the data from the cell
on
the
3rd
row, 4th column of each named range.

I don't want to refer to the absolute cell reference as they
might
get
moved
about.

Is this something that can be done easily?

Thank you