can this be done? is it a question of replacing the FALSE part with the next
VLOOKUP formula?
Thanks.
Keith
--
Biff
Microsoft Excel MVP
"keithobro" <keit...@discussions.microsoft.com> wrote in message
news:8F76EC12-423D-4DDB...@microsoft.com...
Tyro
"keithobro" <keit...@discussions.microsoft.com> wrote in message
news:8A479FD6-BD41-4791...@microsoft.com...
I guess that means the answer to my question is no?
>Why?
The information you provide will determine what kind of suggestions you'll
get.
Make a list of your sheet names and give this list a defined name:
B1:B7 = list of sheet names = defined name WSList
A1 = lookup value
Try this array formula** :
=VLOOKUP(A1,INDIRECT("'"&INDEX(WSList,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSList&"'!B1:B7"),A1)>0,0))&"'!A:B"),2,0)
Assumes the table_array on each sheet is in the range A:B.
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
--
Biff
Microsoft Excel MVP
"keithobro" <keit...@discussions.microsoft.com> wrote in message
news:8A479FD6-BD41-4791...@microsoft.com...
=VLOOKUP(A1,INDIRECT("'"&INDEX(WSList,MATCH(TRUE,COUNTIF(INDIRECT("'"&WSList&"'!A:A"),A1)>0,0))&"'!A:B"),2,0)
--
Biff
Microsoft Excel MVP
"T. Valko" <biffi...@comcast.net> wrote in message
news:O%23Y1q1lV...@TK2MSFTNGP02.phx.gbl...
I think I understand. Will try it out when I get to work today.
If i understand correctly, my first task is to list the sheet names, but
where should I put this?
Keith
Suppose your lookup_value was in cell A1. Typically, you'd want the result
of the lookup formula next to the lookup_value so you would probably enter
the lookup formula in cell B1. You can put the list of sheet names anywhere.
If you're using this on some sort of form or report then you'd probably want
the list of sheet names outside of the report or form.
--
Biff
Microsoft Excel MVP
"keithobro" <keit...@discussions.microsoft.com> wrote in message
news:7A83164A-8150-4F8A...@microsoft.com...
Afraid I don't really understand.
Let's say my 7 sheets are named on their tabs:
Alt
Bel
Met
Pan
Pet
Swi
How do I start?
Sorry to be so "dim"!
keith
vlookup across multiple sheets.xls 20kb
Select an employee name from the drop down in cell A2 on Sheet1.
Lookup the employee's name and return that employee's manager. The
employee's name could be on any of 6 sheets (but will only appear on 1).
Sheet1 B2 returns the employee's manager
Sheet1 C2 returns the sheet name where that employee's info can be found
--
Biff
Microsoft Excel MVP
"keithobro" <keit...@discussions.microsoft.com> wrote in message
news:8B800840-1554-4201...@microsoft.com...
I have a similar issue, i have already posted this question but the person
answering my question hadnt done this before so couldnt provide much
guidance, and since you have can you please provide some guidance?
my previous email:
> > Hi, I have the following 2 formulae and i have been desperately trying
to
> > > make them work but the dont!
> > >
> > > I am looking up the value of column D in 1 worksheet to look across 10/20
> > > different sheets (which are all named) and then return the results as "1,0"
> > > when done into another sheet. So i am just looking up names from column D of
> > > sheet"names" and then looking across 10 sheets, again in the same column - D (dont want it to return anything other than just the name, which will tell me if that is present anywhere witin the 20worksheets.)
> > >
> > > These are the formulae i have tried. the 1st works on the sheets being
> > > called sheets 1,2 etc and not being renamed and the 2nd formula allows you to
> > > name the sheets then refer to them seperatly. i would like to use the 2nd as
> > > i all my sheets are named.
> > >
> > > =VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(INDIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B1)>0,0)&"!d2:d100"),1,0)
> > >
> > >
> > >
> > > =VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)>0,0))&"!d2:d100"),1,0)
> > >
> > >
> > > I either get the response N/A or VALUE. i dont know if i have got all the
> > > referenes correct, maybe thats why its not working.
> > >
> > > maybe the "d2:d100" is incorrect as i repeat it in the formula, but this is
> > > the range where the lookup should look in.
> > >
> > > I hope im making sense.
> > >
> > > Thanks for your help.
please help!!
--
Biff
Microsoft Excel MVP
"Gemz" <Ge...@discussions.microsoft.com> wrote in message
news:DFC2375A-5C70-4AE1...@microsoft.com...