Refering to a tab using data from a cell
 

Is there a way that I can use data from a cell to reference a worksheet?

I have two columns with data in.

The first column (called columnA) contains values which match the names of
my worksheet tabs. The second column (columnB) contains references to items
found in those tabs.

I want to be able to use the first colum to speciy which tab to look in and
the secound column to be able to use the VLOOKUP function to then grab data
relevant to that entry. I can do the second bit but the first is proving
very difficult.

I need to somehow use the data contained in a cell to reference a worksheet
tab?!

if A1 = 'Sheet1" and A2 = "C4" then use

=INDIRECT(A1&"!"&A2)

If your sheet/tab names contain spaces, you'll need single quotes around the
sheet name, i.e.,

=INDIRECT("'"A1&"'!"&A2)

"dan" wrote:

Is there a way that I can use data from a cell to reference a worksheet?

I have two columns with data in.

The first column (called columnA) contains values which match the names of
my worksheet tabs. The second column (columnB) contains references to items
found in those tabs.

I want to be able to use the first colum to speciy which tab to look in and
the secound column to be able to use the VLOOKUP function to then grab data
relevant to that entry. I can do the second bit but the first is proving
very difficult.

I need to somehow use the data contained in a cell to reference a worksheet
tab?!

Thanks for that it works brilliantly. However what I need to be able to do
is use the VLOOKUP statement to checks within a range specified by that
indirect statement. E.g. something along the lines of

=VLOOKUP(C1, ?????????C4:X15, 2, FALSE) where C1 contains the reference to
search for, in the worksheet specified by the ???????. These question marks,
I had imagined, would be replaced by your indirect statement. However I
tried this and it didn't work directly.

Please can you offer any further assistance.

Many thanks in advance

Dan

"Duke Carey" wrote:

if A1 = 'Sheet1" and A2 = "C4" then use

=INDIRECT(A1&"!"&A2)

If your sheet/tab names contain spaces, you'll need single quotes around the
sheet name, i.e.,

=INDIRECT("'"A1&"'!"&A2)

"dan" wrote:

Is there a way that I can use data from a cell to reference a worksheet?

I have two columns with data in.

The first column (called columnA) contains values which match the names of
my worksheet tabs. The second column (columnB) contains references to items
found in those tabs.

I want to be able to use the first colum to speciy which tab to look in and
the secound column to be able to use the VLOOKUP function to then grab data
relevant to that entry. I can do the second bit but the first is proving
very difficult.

I need to somehow use the data contained in a cell to reference a worksheet
tab?!

=VLOOKUP(C1, INDIRECT(D1&"C4:X15"), 2, FALSE)

--
HTH

Bob Phillips

"dan" wrote in message
...
Thanks for that it works brilliantly. However what I need to be able to
do
is use the VLOOKUP statement to checks within a range specified by that
indirect statement. E.g. something along the lines of

=VLOOKUP(C1, ?????????C4:X15, 2, FALSE) where C1 contains the reference
to
search for, in the worksheet specified by the ???????. These question
marks,
I had imagined, would be replaced by your indirect statement. However I
tried this and it didn't work directly.

Please can you offer any further assistance.

Many thanks in advance

Dan

"Duke Carey" wrote:

if A1 = 'Sheet1" and A2 = "C4" then use

=INDIRECT(A1&"!"&A2)

If your sheet/tab names contain spaces, you'll need single quotes around
the
sheet name, i.e.,

=INDIRECT("'"A1&"'!"&A2)

"dan" wrote:

Is there a way that I can use data from a cell to reference a
worksheet?

I have two columns with data in.

The first column (called columnA) contains values which match the
names of
my worksheet tabs. The second column (columnB) contains references to
items
found in those tabs.

I want to be able to use the first colum to speciy which tab to look
in and
the secound column to be able to use the VLOOKUP function to then grab
data
relevant to that entry. I can do the second bit but the first is
proving
very difficult.

I need to somehow use the data contained in a cell to reference a
worksheet
tab?!

Typo

=VLOOKUP(C1, INDIRECT("'"&D1&"'!C4:X15"), 2, FALSE)


--

HTH

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


"Bob Phillips" wrote in message
...
=VLOOKUP(C1, INDIRECT(D1&"C4:X15"), 2, FALSE)

--
HTH

Bob Phillips

"dan" wrote in message
...
Thanks for that it works brilliantly. However what I need to be able to
do
is use the VLOOKUP statement to checks within a range specified by that
indirect statement. E.g. something along the lines of

=VLOOKUP(C1, ?????????C4:X15, 2, FALSE) where C1 contains the reference
to
search for, in the worksheet specified by the ???????. These question
marks,
I had imagined, would be replaced by your indirect statement. However I
tried this and it didn't work directly.

Please can you offer any further assistance.

Many thanks in advance

Dan

"Duke Carey" wrote:

if A1 = 'Sheet1" and A2 = "C4" then use

=INDIRECT(A1&"!"&A2)

If your sheet/tab names contain spaces, you'll need single quotes
around
the
sheet name, i.e.,

=INDIRECT("'"A1&"'!"&A2)

"dan" wrote:

Is there a way that I can use data from a cell to reference a
worksheet?

I have two columns with data in.

The first column (called columnA) contains values which match the
names of
my worksheet tabs. The second column (columnB) contains references
to
items
found in those tabs.

I want to be able to use the first colum to speciy which tab to look
in and
the secound column to be able to use the VLOOKUP function to then
grab
data
relevant to that entry. I can do the second bit but the first is
proving
very difficult.

I need to somehow use the data contained in a cell to reference a
worksheet
tab?!

Thanks very much - spot on.

"Bob Phillips" wrote:

=VLOOKUP(C1, INDIRECT(D1&"C4:X15"), 2, FALSE)

--
HTH

Bob Phillips

"dan" wrote in message
...
Thanks for that it works brilliantly. However what I need to be able to
do
is use the VLOOKUP statement to checks within a range specified by that
indirect statement. E.g. something along the lines of

=VLOOKUP(C1, ?????????C4:X15, 2, FALSE) where C1 contains the reference
to
search for, in the worksheet specified by the ???????. These question
marks,
I had imagined, would be replaced by your indirect statement. However I
tried this and it didn't work directly.

Please can you offer any further assistance.

Many thanks in advance

Dan

"Duke Carey" wrote:

if A1 = 'Sheet1" and A2 = "C4" then use

=INDIRECT(A1&"!"&A2)

If your sheet/tab names contain spaces, you'll need single quotes around
the
sheet name, i.e.,

=INDIRECT("'"A1&"'!"&A2)

"dan" wrote:

Is there a way that I can use data from a cell to reference a
worksheet?

I have two columns with data in.

The first column (called columnA) contains values which match the
names of
my worksheet tabs. The second column (columnB) contains references to
items
found in those tabs.

I want to be able to use the first colum to speciy which tab to look
in and
the secound column to be able to use the VLOOKUP function to then grab
data
relevant to that entry. I can do the second bit but the first is
proving
very difficult.

I need to somehow use the data contained in a cell to reference a
worksheet
tab?!