Vlookup - Sheet Name in Cell

I have created a cell with all of the tab names within that spreadsheet. I want to use vlookup such that the sheet name will be in the referenced cell. I also need to know how to include the sheet range?

ex. vlookup(A1,(sheet named in cell-including range),5,false).

On Monday, May 14, 2018 at 9:29:39 PM UTC-4, wrote:
Vlookup - Sheet Name in Cell

I have created a cell with all of the tab names within that spreadsheet. I want to use vlookup such that the sheet name will be in the referenced cell. I also need to know how to include the sheet range?

ex. vlookup(A1,(sheet named in cell-including range),5,false).

On Monday, May 14, 2018 at 9:29:39 PM UTC-4, wrote:
Vlookup - Sheet Name in Cell

I have created a cell with all of the tab names within that spreadsheet. I want to use vlookup such that the sheet name will be in the referenced cell. I also need to know how to include the sheet range?

ex. vlookup(A1,(sheet named in cell-including range),5,false).

Update: I've simplified this such that there are two sheets used for the vlookup with the same range. Hence all I need it to do is to something like this:

vlookup(A1,'PROS All'!A:Z,5,false) ***I would only need to change the sheet name from 'PROS' to 'CONS' by referencing another cell to get the difference in the sheet name.

On Tuesday, 15 May 2018 13:29:39 UTC+12, wrote:
Vlookup - Sheet Name in Cell

I have created a cell with all of the tab names within that spreadsheet. I want to use vlookup such that the sheet name will be in the referenced cell. I also need to know how to include the sheet range?

ex. vlookup(A1,(sheet named in cell-including range),5,false).

You might be able to use the ADDRESS() and/or INDIRECT() functions. INDIRECT allows you to build a reference as a string and then reference that cell.

On Tuesday, 15 May 2018 13:06:39 UTC+1, wrote:
On Monday, May 14, 2018 at 9:29:39 PM UTC-4, wrote:
Vlookup - Sheet Name in Cell

I have created a cell with all of the tab names within that spreadsheet. I want to use vlookup such that the sheet name will be in the referenced cell. I also need to know how to include the sheet range?

ex. vlookup(A1,(sheet named in cell-including range),5,false).

Update: I've simplified this such that there are two sheets used for the vlookup with the same range. Hence all I need it to do is to something like this:

vlookup(A1,'PROS All'!A:Z,5,false) ***I would only need to change the sheet name from 'PROS' to 'CONS' by referencing another cell to get the difference in the sheet name.

If there are only two sets of data, then I think the simplest way would be to have a reference cell, into which you enter P or C. then the formula is
=if(refcell="P",vlookup(A1,'PROS All'!A:Z,5,false),vlookup(A1,'CONS All'!A:Z,5,false))

If there is a long list of sheet names, them maybe have the Sheet name &"!"&range in column B alongside.
Then the formula would be
=vlookup(A1,Indirect(B2),5,0) where you amend B2 to the row containing your sheet name and range