Change workbook sheet reference using cell A1 to change a vairable

Hi,

Sorry for posting this one again, but I had 2 questions in my last post and
only one was answered. I thought my other question might be getting skipped
because there is an answer associated with it.

I am using the formula below and others like it to extract certain data from
a single workbook into several different workbooks. I have multiple sets of
information that are all on different sheets, but the workbook name and cell
location remain the same.

Instead of changing the "345KV CANIFF STEPHENS" portion of my formula 20
times per new workbook, I would like to be able to use a formula that would
get the sheet name from cell A1. How can I do this?

='[345KV AND 120KV FOR ITC UPDATED 11-7-03.xls]345KV CANIFF STEPHENS'!$N5

Thanks,

Reed

Hi Reed,

=INDIRECT("='[345KV AND 120KV FOR ITC UPDATED 11-7-03.xls]" & A1 & "'!$N5")

not tested, so I hope I have got all of the quotes and so correct.

But beware, it only works when the other workbook is open, don't know if
that is an issue for you.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Reed" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Sorry for posting this one again, but I had 2 questions in my last post
and
> only one was answered. I thought my other question might be getting
skipped
> because there is an answer associated with it.
>
> I am using the formula below and others like it to extract certain data
from
> a single workbook into several different workbooks. I have multiple sets
of
> information that are all on different sheets, but the workbook name and
cell
> location remain the same.
>
> Instead of changing the "345KV CANIFF STEPHENS" portion of my formula 20
> times per new workbook, I would like to be able to use a formula that
would
> get the sheet name from cell A1. How can I do this?
>
> ='[345KV AND 120KV FOR ITC UPDATED 11-7-03.xls]345KV CANIFF STEPHENS'!$N5
>
> Thanks,
>
> Reed
>
>

The formula gave me an invalid cell reference error. I tried to adjust the
quotes, spaces, etc., but still no luck.

Thanks,

Mike

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
Hi Reed,

=INDIRECT("='[345KV AND 120KV FOR ITC UPDATED 11-7-03.xls]" & A1 & "'!$N5")

not tested, so I hope I have got all of the quotes and so correct.

But beware, it only works when the other workbook is open, don't know if
that is an issue for you.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Reed" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Sorry for posting this one again, but I had 2 questions in my last post
and
> only one was answered. I thought my other question might be getting
skipped
> because there is an answer associated with it.
>
> I am using the formula below and others like it to extract certain data
from
> a single workbook into several different workbooks. I have multiple sets
of
> information that are all on different sheets, but the workbook name and
cell
> location remain the same.
>
> Instead of changing the "345KV CANIFF STEPHENS" portion of my formula 20
> times per new workbook, I would like to be able to use a formula that
would
> get the sheet name from cell A1. How can I do this?
>
> ='[345KV AND 120KV FOR ITC UPDATED 11-7-03.xls]345KV CANIFF STEPHENS'!$N5
>
> Thanks,
>
> Reed
>
>

I left an equals sign in. Try this instead

=INDIRECT("'[345KV AND 120KV FOR ITC UPDATED 11-7-03.xls]" & A1 & "'!$N5")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Reed" <[email protected]> wrote in message
news:[email protected]...
> The formula gave me an invalid cell reference error. I tried to adjust
the
> quotes, spaces, etc., but still no luck.
>
> Thanks,
>
> Mike
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> Hi Reed,
>
> =INDIRECT("='[345KV AND 120KV FOR ITC UPDATED 11-7-03.xls]" & A1 &
"'!$N5")
>
> not tested, so I hope I have got all of the quotes and so correct.
>
> But beware, it only works when the other workbook is open, don't know if
> that is an issue for you.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Reed" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > Sorry for posting this one again, but I had 2 questions in my last post
> and
> > only one was answered. I thought my other question might be getting
> skipped
> > because there is an answer associated with it.
> >
> > I am using the formula below and others like it to extract certain data
> from
> > a single workbook into several different workbooks. I have multiple
sets
> of
> > information that are all on different sheets, but the workbook name and
> cell
> > location remain the same.
> >
> > Instead of changing the "345KV CANIFF STEPHENS" portion of my formula 20
> > times per new workbook, I would like to be able to use a formula that
> would
> > get the sheet name from cell A1. How can I do this?
> >
> > ='[345KV AND 120KV FOR ITC UPDATED 11-7-03.xls]345KV CANIFF
STEPHENS'!$N5
> >
> > Thanks,
> >
> > Reed
> >
> >
>
>
>

Works great!! Thanks!


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
I left an equals sign in. Try this instead

=INDIRECT("'[345KV AND 120KV FOR ITC UPDATED 11-7-03.xls]" & A1 & "'!$N5")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Reed" <[email protected]> wrote in message
news:[email protected]...
> The formula gave me an invalid cell reference error. I tried to adjust
the
> quotes, spaces, etc., but still no luck.
>
> Thanks,
>
> Mike
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> Hi Reed,
>
> =INDIRECT("='[345KV AND 120KV FOR ITC UPDATED 11-7-03.xls]" & A1 &
"'!$N5")
>
> not tested, so I hope I have got all of the quotes and so correct.
>
> But beware, it only works when the other workbook is open, don't know if
> that is an issue for you.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Reed" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > Sorry for posting this one again, but I had 2 questions in my last post
> and
> > only one was answered. I thought my other question might be getting
> skipped
> > because there is an answer associated with it.
> >
> > I am using the formula below and others like it to extract certain data
> from
> > a single workbook into several different workbooks. I have multiple
sets
> of
> > information that are all on different sheets, but the workbook name and
> cell
> > location remain the same.
> >
> > Instead of changing the "345KV CANIFF STEPHENS" portion of my formula 20
> > times per new workbook, I would like to be able to use a formula that
> would
> > get the sheet name from cell A1. How can I do this?
> >
> > ='[345KV AND 120KV FOR ITC UPDATED 11-7-03.xls]345KV CANIFF
STEPHENS'!$N5
> >
> > Thanks,
> >
> > Reed
> >
> >
>
>
>