Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 15th 18, 02:29 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2018
Posts: 3
Default Vlookup - Sheet Name in Cell

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).

  #2   Report Post  
Old May 15th 18, 12:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2018
Posts: 3
Default Vlookup - Sheet Name in Cell

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).


  #3   Report Post  
Old May 15th 18, 01:06 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: May 2018
Posts: 3
Default Vlookup - Sheet Name in Cell

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.

  #4   Report Post  
Old May 15th 18, 08:16 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2018
Posts: 5
Default Vlookup - Sheet Name in Cell

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.
  #5   Report Post  
Old May 17th 18, 09:53 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2016
Posts: 12
Default Vlookup - Sheet Name in 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
take sheet name from a cell in VLOOKUP Gaurav[_5_] Excel Worksheet Functions 3 October 6th 09 04:17 PM
vlookup where sheet name=cell value DDawson Excel Programming 2 September 29th 08 12:17 PM
Vlookup a part of a cell value in another sheet Salman Excel Worksheet Functions 0 March 22nd 06 07:56 AM
Vlookup a part of a cell value in another sheet Salman Excel Worksheet Functions 0 March 22nd 06 07:52 AM
Using a cell reference of a sheet in Vlookup crazybass2 Excel Worksheet Functions 3 August 12th 05 07:51 PM


All times are GMT +1. The time now is 05:25 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017