Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
Add a data series dynamically to a named range? | Charts and Charting in Excel | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) |