Formula to return the reference of a range of cells

[tiptoe]

I need to build a formula that finds a text reference in a vertical
list, e.g finds "Feb" in a list of months. Having found the text, I
need the formula to return in text format the cell references of a
range of a cells located in the same row as the found text and
immediately to the left, e.g if "Feb" is found in cell A2 the formula
would return the range reference (not the values) of the next 10 cells
in the same row, in this case, A3:J2

I've been looking at Address, Index, Match etc. but I can't figure it
out.

I'd appreciate any suggestions.

Bob
Nottingham UK

 

[Niek Otten]

Hi Bob,

Your question is not too precise. Is "Feb" the result of a formatted date or is it a text value? And lots more of questions.
Post back with exact details of what you did, your formulas, values, expected results and what you got instead, etc.
In the meantime, look here:

http://www.cpearson.com/excel/TablesAndLookups.aspx

and make sure you look at all the details that might have to do with your case

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I need to build a formula that finds a text reference in a vertical
| list, e.g finds "Feb" in a list of months. Having found the text, I
| need the formula to return in text format the cell references of a
| range of a cells located in the same row as the found text and
| immediately to the left, e.g if "Feb" is found in cell A2 the formula
| would return the range reference (not the values) of the next 10 cells
| in the same row, in this case, A3:J2
|
| I've been looking at Address, Index, Match etc. but I can't figure it
| out.
|
| I'd appreciate any suggestions.
|
| Bob
| Nottingham UK

 

[tiptoe]

I need to build a formula that finds a text reference in a vertical
list, e.g finds "Feb" in a list of months. Having found the text, I
need the formula to return in text format the cell references of a
range of a cells located in the same row as the found text and
immediately to the left, e.g if "Feb" is found in cell A2 the formula
would return the range reference (not the values) of the next 10 cells
in the same row, in this case, A3:J2

I've been looking at Address, Index, Match etc. but I can't figure it
out.

I'd appreciate any suggestions.

Bob
Nottingham UK

Got this to work:

ADDRESS(MATCH(eurosdate,'Data Sheet (2)'!AH7:AH18,0)+6,35) & ":"&
ADDRESS(MATCH(eurosdate,'Data Sheet (2)'!AH7:AH18,0)+6,54)

In my worksheet this returns $AA$8:$BB$8

 

[tiptoe]

Hi Bob,

Your question is not too precise. Is "Feb" the result of a formatted date or is it a text value? And lots more of questions.
Post back with exact details of what you did, your formulas, values, expected results and what you got instead, etc.
In the meantime, look here:

http://www.cpearson.com/excel/TablesAndLookups.aspx

and make sure you look at all the details that might have to do with your case

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I need to build a formula that finds a text reference in a vertical
| list, e.g finds "Feb" in a list of months. Having found the text, I
| need the formula to return in text format the cell references of a
| range of a cells located in the same row as the found text and
| immediately to the left, e.g if "Feb" is found in cell A2 the formula
| would return the range reference (not the values) of the next 10 cells
| in the same row, in this case, A3:J2
|
| I've been looking at Address, Index, Match etc. but I can't figure it
| out.
|
| I'd appreciate any suggestions.
|
| Bob
| Nottingham UK

Nick,

My ultimate aim is to build a chart that refers to values in a range
of cells held in a row on another worksheet. Normally this would be
straightforward by manually editing the source data in the chart and
pointing to the range of cells. However, over time the range of cells
used to provide the values for the chart will change and I am looking
for a method that will do this automatically.

I thought if I could produce a formula that identifies the cells when
the change I could simply paste that formula into the Chart's source
data values field. Of course, I've tried it but it will not accept it.

For example, where the source worksheet is named Data Sheet(2) and the
range of cell are $AI$8:$BB$8 I've come up with a formula that returns
the cell range:

=ADDRESS(MATCH(eurosdate,'Data Sheet (2)'!AH7:AH18,0)+6,35) & ":"&
ADDRESS(MATCH(eurosdate,'Data Sheet (2)'!AH7:AH18,0)+6,54)

This returns: $AI$8:$BB$8

Pasting the formula in the chart source data values field did not
work, I then tried:

= 'Data Sheet'! & ADDRESS(MATCH(eurosdate,'Data Sheet (2)'!
AH7:AH18,0)+6,35) & ":"& ADDRESS(MATCH(eurosdate,'Data Sheet (2)'!
AH7:AH18,0)+6,54)

No luck with this either.

Hope this makes sense to you as it's driving me crazy!

Bob

 

[T. Valko]

Nick,

My ultimate aim is to build a chart that refers to values in a range
of cells held in a row on another worksheet. Normally this would be
straightforward by manually editing the source data in the chart and
pointing to the range of cells. However, over time the range of cells
used to provide the values for the chart will change and I am looking
for a method that will do this automatically.

I thought if I could produce a formula that identifies the cells when
the change I could simply paste that formula into the Chart's source
data values field. Of course, I've tried it but it will not accept it.

For example, where the source worksheet is named Data Sheet(2) and the
range of cell are $AI$8:$BB$8 I've come up with a formula that returns
the cell range:

=ADDRESS(MATCH(eurosdate,'Data Sheet (2)'!AH7:AH18,0)+6,35) & ":"&
ADDRESS(MATCH(eurosdate,'Data Sheet (2)'!AH7:AH18,0)+6,54)

This returns: $AI$8:$BB$8

Pasting the formula in the chart source data values field did not
work, I then tried:

= 'Data Sheet'! & ADDRESS(MATCH(eurosdate,'Data Sheet (2)'!
AH7:AH18,0)+6,35) & ":"& ADDRESS(MATCH(eurosdate,'Data Sheet (2)'!
AH7:AH18,0)+6,54)

No luck with this either.

Hope this makes sense to you as it's driving me crazy!

Bob

I don't think you can do it the way you're trying to but it sounds like you
could do it using dynamic data sources.

Everything you always wanted to know about charts and then some:

http://PeltierTech.com