Hi, I have a list of data which is set out as a name in cell a1 (say j
bloggs) and cells b1 to g1 contains data that needs to go into a chart,at the
moment I plan to open a sheet for each name and plaste link the whole list on
each sheet and build a chart on each sheet by sourcing the data from the list
against each name. the problem is if they change the order of the names by
adding one or removing one, I will have to rebuild all the links, is there a
way I can pick up the data by locating the name and picking up the data
linked in that row and either pasting it to a fixed place on the sheet where
the chart would pick it up, or load direct into chart.
Can any one help please, although it sounds like atall order.

Thanks in advance.

Hi

I would set up 2 named ranges, one for the whole set of data on your master
sheet, and one for the column of names on your Master sheet.

(Replace the word Master, with whatever your sheet name is)

InsertNameDefine name MyData Refers to
=Master!$A$1:INDEX(Master!$G:$G,COUNTA(Master!$A:$ A))
InsertNameDefine name Names Refers to = Master!$A:$A

The first named range will be Dynamic, and will grow if more data is added
to the sheet.

Then on each sheet, assuming you have placed the name in A1, enter the
following in B1 and copy across to G1

=INDEX(MyData,MATCH($A$1,Names,0),COLUMN())

--
Regards
Roger Govier

"wildauk" wrote in message
...
Hi, I have a list of data which is set out as a name in cell a1 (say j
bloggs) and cells b1 to g1 contains data that needs to go into a chart,at
the
moment I plan to open a sheet for each name and plaste link the whole list
on
each sheet and build a chart on each sheet by sourcing the data from the
list
against each name. the problem is if they change the order of the names by
adding one or removing one, I will have to rebuild all the links, is there
a
way I can pick up the data by locating the name and picking up the data
linked in that row and either pasting it to a fixed place on the sheet
where
the chart would pick it up, or load direct into chart.
Can any one help please, although it sounds like atall order.

Thanks in advance.

Roger, Am I right to asume that the first formula goes into A1 in the next
sheet and the second formula goes into B1,C1D1 ect.in the same sheet, I have
tried that and the name was picked up in A! but B1,C1,D1 just has (#name?) in
them ,no data.

Please forgive me being so thick

Barry.

"Roger Govier" wrote:

Hi

I would set up 2 named ranges, one for the whole set of data on your master
sheet, and one for the column of names on your Master sheet.

(Replace the word Master, with whatever your sheet name is)

InsertNameDefine name MyData Refers to
=Master!$A$1:INDEX(Master!$G:$G,COUNTA(Master!$A:$ A))
InsertNameDefine name Names Refers to = Master!$A:$A

The first named range will be Dynamic, and will grow if more data is added
to the sheet.

Then on each sheet, assuming you have placed the name in A1, enter the
following in B1 and copy across to G1

=INDEX(MyData,MATCH($A$1,Names,0),COLUMN())

--
Regards
Roger Govier

"wildauk" wrote in message
...
Hi, I have a list of data which is set out as a name in cell a1 (say j
bloggs) and cells b1 to g1 contains data that needs to go into a chart,at
the
moment I plan to open a sheet for each name and plaste link the whole list
on
each sheet and build a chart on each sheet by sourcing the data from the
list
against each name. the problem is if they change the order of the names by
adding one or removing one, I will have to rebuild all the links, is there
a
way I can pick up the data by locating the name and picking up the data
linked in that row and either pasting it to a fixed place on the sheet
where
the chart would pick it up, or load direct into chart.
Can any one help please, although it sounds like atall order.

Thanks in advance.

Hi

No, I assumed you would have the name of the person whose data you wanted in
A1 of the relevant sheet.
The formula goes in B1, and is copied across to G1

--
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi

I would set up 2 named ranges, one for the whole set of data on your
master sheet, and one for the column of names on your Master sheet.

(Replace the word Master, with whatever your sheet name is)

InsertNameDefine name MyData Refers to
=Master!$A$1:INDEX(Master!$G:$G,COUNTA(Master!$A:$ A))
InsertNameDefine name Names Refers to = Master!$A:$A

The first named range will be Dynamic, and will grow if more data is added
to the sheet.

Then on each sheet, assuming you have placed the name in A1, enter the
following in B1 and copy across to G1

=INDEX(MyData,MATCH($A$1,Names,0),COLUMN())

--
Regards
Roger Govier

"wildauk" wrote in message
...
Hi, I have a list of data which is set out as a name in cell a1 (say j
bloggs) and cells b1 to g1 contains data that needs to go into a chart,at
the
moment I plan to open a sheet for each name and plaste link the whole
list on
each sheet and build a chart on each sheet by sourcing the data from the
list
against each name. the problem is if they change the order of the names
by
adding one or removing one, I will have to rebuild all the links, is
there a
way I can pick up the data by locating the name and picking up the data
linked in that row and either pasting it to a fixed place on the sheet
where
the chart would pick it up, or load direct into chart.
Can any one help please, although it sounds like atall order.

Thanks in advance.