How can I do a lookup to a specific cell on multiple worksheets?

[Ray Stubblefield]

I have a summary sheet where I want to lookup a number that is referenced the
same on multiple sheets. I desire to have the summary look to particular tab
names and return the amount in that particular cell.
Any ideas please?

[Bob Phillips]

Do you mean

=Sheet1!A10

--

HTH

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


"Ray Stubblefield" <[email protected]> wrote in
message news:[email protected]...
> I have a summary sheet where I want to lookup a number that is referenced
the
> same on multiple sheets. I desire to have the summary look to particular
tab
> names and return the amount in that particular cell.
> Any ideas please?

[Don Guillett]

this maybe.
=sum(sheet1:sheet21!a1)

--
Don Guillett
SalesAid Software
[email protected]
"Ray Stubblefield" <[email protected]> wrote in
message news:[email protected]...
> I have a summary sheet where I want to lookup a number that is referenced
the
> same on multiple sheets. I desire to have the summary look to particular
tab
> names and return the amount in that particular cell.
> Any ideas please?

[Ray Stubblefield]

The situation is more complex. I am trying to create a summary sheet that
looks up results contained in a specific cell across approx. 100 sheets. I
need to lookup based on a specific reference number that is contained on both
sheets.

I am currently working on the TREED function using either VLOOKUP or
INDEX,MATCH formulas.

"Don Guillett" wrote:

> this maybe.
> =sum(sheet1:sheet21!a1)
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Ray Stubblefield" <[email protected]> wrote in
> message news:[email protected]...
> > I have a summary sheet where I want to lookup a number that is referenced
> the
> > same on multiple sheets. I desire to have the summary look to particular
> tab
> > names and return the amount in that particular cell.
> > Any ideas please?
>
>
>

[Debra]

Were you able to figure this out? I am looking to do the same thing...i have
30 sheets that are all templates with different information on each sheet but
want to compare on a total sheet the information from each tab same cell...

"Ray Stubblefield" wrote:

> The situation is more complex. I am trying to create a summary sheet that
> looks up results contained in a specific cell across approx. 100 sheets. I
> need to lookup based on a specific reference number that is contained on both
> sheets.
>
> I am currently working on the TREED function using either VLOOKUP or
> INDEX,MATCH formulas.
>
> "Don Guillett" wrote:
>
> > this maybe.
> > =sum(sheet1:sheet21!a1)
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "Ray Stubblefield" <[email protected]> wrote in
> > message news:[email protected]...
> > > I have a summary sheet where I want to lookup a number that is referenced
> > the
> > > same on multiple sheets. I desire to have the summary look to particular
> > tab
> > > names and return the amount in that particular cell.
> > > Any ideas please?
> >
> >
> >

[raystub]

Hi, Debra!
Let me relay the help that I got. It did work with a little tweaking.
I am sending the thread of the conversation that worked for me. Okay?
Peo Sjoblom is a great resource.
Don't forget to go through it backwards!
XXXXX
I don't see any problems with that, if you would have asked that is what I
would have suggested..
I always use dummy sheets when doing things like this (I even put one at the
start as well)

=VLOOKUP(D6,THREED(Start:Stop!$J$19:$K$19),2,0)

The zero at the end tells it to look for an exact match while omitting it or
using 1 (or TRUE for 1 and FALSE for 0)
will look for the next largest value that is less than the lookup value,
also the array must be sorted in ascending order
so the first sheet must have the lowest value etc. By using 0 or FALSE it
can be sorted in any order
Good luck


--
Regards,

Peo Sjoblom
XXXXX

"Ray Stubblefield" <[email protected]> wrote in message
news:[email protected]...
> I tried this and discovered a few problems that were self inflicted. I
> rectified them as follows:
> My VLOOKUP formula did not contain the ,0) at the end. I am not familiar
> with that!
>
> I believe the problem with the file structure arose where there was no
> worksheet M305. This made it create a link to look in the currect
directory
> for a file that did not exist. To fix this where an M305 sheet is not
needed,
> I intend to create a hidden sheet named "stop" as the last sheet and refer
to
> it at the end within my formula.
> =VLOOKUP(D6,THREED('M010:Stop'!$J$19:$K$19),2,0)
> If you see any danger in theis approach, please let me know!
> Your help is so very much appreciated, Peo. Thank you very much!
>XXXXX
>
> "Peo Sjoblom" wrote:
>
> > This works for me
> >
> > =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2,0)
> >
> > or are you using this from another workbook
> >
> > =VLOOKUP(D6,THREED('[test.xls]M010:M305'!$J$19:$K$19),2,0)
> >
> > replace test.xls with the name of the workbook
> >
> > The other workbook needs to be open or you'll get a REF error when you
try
> > to calculate it
> >
> > I am assuming here that the sheets are named M010:M305
> >
> >
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> >XXXXX
> > "Ray Stubblefield" <[email protected]> wrote in
> > message news:[email protected]...
> > > Good morning, Peo.
> > > I am having difficulty.
> > > Following your lead, I tried:
> > > =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2)
> > >
> > > It immediately changes to
> > > =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2)
> > >
> > >
> > > Upon copying down my list of search criteria, I get the following:
> > > =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) = 208,000
> > > =VLOOKUP(D7,THREED('M010:[M305]M305'!$J$19:$K$19),2) =208,000
> > > =VLOOKUP(D8,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124
> > > =VLOOKUP(D9,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124
> > > There are no worksheets is this particular workbook that the reference
can
> > > search, so it is returning the previous result.
> > >
> > > I tried:
> > >
> >
=INDEX(THREED('M010:M305'!$J$19:$K$19),MATCH(D6,THREED('M010:M305'!$J$19)0),
> > 2)
> > >
> > > It immediately changes to:
> > >
> >
=INDEX(THREED('M010:[M305]M305'!$J$19:$K$19),MATCH(D6,THREED('M010:[M305]M30
> > 5'!$J$19)0),2)
> > >
> > > but, happily, it does give me the correct results. However, upon
saving
> > and
> > > closing, the cells contain:
> > >
> >
=INDEX(THREED('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19:$K$19),MATCH(D6,THREE
> > D('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19),0),2)
> > >
> > > This is not a valid reference to the worksheet, but only to the
directory
> > > containing that worksheet.
> > >
> > > I do appreciate your help with this, Peo!
> > >
> > > Ray Stubbleefield
> > >
XXXXX
> > > "Peo Sjoblom" wrote:
> > >
> > > > There is no built in lookuop that will work over multiple sheets, I
> > would
> > > > recommend using Laurent Longre's excellent Morefunc that can be
> > downloaded
> > > > here
> > > >
> > > > http://longre.free.fr/english/
> > > >
> > > >
> > > > descriptions here
> > > >
> > > > http://www.rhdatasolutions.com/morefunc/
> > > >
> > > > In your case it would be
> > > >
> > > > =VLOOKUP(Lookup_value,THREED( etc
> > > >
> > > >
> > > > Regards,
> > > >
> > > > Peo Sjoblom
> > > >
XXXXX
> > > > "Ray Stubblefield" wrote:
> > > >
> > > > > I want to create a summary sheet that will lookup a particular
cells
> > value on
> > > > > multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19)
based
> > upon a
> > > > > cell next to it ($I$19) that will match the criteria on the
summary
> > sheet
> > > > > (e.g. w1, w2, w3).
> > > > > I have tried VLOOKAllSheets but when there are other similar
workbooks
> > open,
> > > > > it doesn't work right.
> > > > > Anyone? I appreciate your help.
> >
> >
> >





"Debra" wrote:

> Were you able to figure this out? I am looking to do the same thing...i have
> 30 sheets that are all templates with different information on each sheet but
> want to compare on a total sheet the information from each tab same cell...
>
> "Ray Stubblefield" wrote:
>
> > The situation is more complex. I am trying to create a summary sheet that
> > looks up results contained in a specific cell across approx. 100 sheets. I
> > need to lookup based on a specific reference number that is contained on both
> > sheets.
> >
> > I am currently working on the TREED function using either VLOOKUP or
> > INDEX,MATCH formulas.
> >
> > "Don Guillett" wrote:
> >
> > > this maybe.
> > > =sum(sheet1:sheet21!a1)
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > [email protected]
> > > "Ray Stubblefield" <[email protected]> wrote in
> > > message news:[email protected]...
> > > > I have a summary sheet where I want to lookup a number that is referenced
> > > the
> > > > same on multiple sheets. I desire to have the summary look to particular
> > > tab
> > > > names and return the amount in that particular cell.
> > > > Any ideas please?
> > >
> > >
> > >

[raystub]

Good Morning!
I was sucessful in this.
I am attaching the thread of the most helpful guidance. Don't forget to go
through it backwards.
Let me know if you need additional help. Also, Peo Sjoblom is a great
resource!
Good luck!

I don't see any problems with that, if you would have asked that is what I
would have suggested..
I always use dummy sheets when doing things like this (I even put one at the
start as well)

=VLOOKUP(D6,THREED(Start:Stop!$J$19:$K$19),2,0)

The zero at the end tells it to look for an exact match while omitting it or
using 1 (or TRUE for 1 and FALSE for 0)
will look for the next largest value that is less than the lookup value,
also the array must be sorted in ascending order
so the first sheet must have the lowest value etc. By using 0 or FALSE it
can be sorted in any order
Good luck


--
Regards,

Peo Sjoblom


"Ray Stubblefield" <[email protected]> wrote in message
news:[email protected]...
> I tried this and discovered a few problems that were self inflicted. I
> rectified them as follows:
> My VLOOKUP formula did not contain the ,0) at the end. I am not familiar
> with that!
>
> I believe the problem with the file structure arose where there was no
> worksheet M305. This made it create a link to look in the currect
directory
> for a file that did not exist. To fix this where an M305 sheet is not
needed,
> I intend to create a hidden sheet named "stop" as the last sheet and refer
to
> it at the end within my formula.
> =VLOOKUP(D6,THREED('M010:Stop'!$J$19:$K$19),2,0)
> If you see any danger in theis approach, please let me know!
> Your help is so very much appreciated, Peo. Thank you very much!
>
>
> "Peo Sjoblom" wrote:
>
> > This works for me
> >
> > =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2,0)
> >
> > or are you using this from another workbook
> >
> > =VLOOKUP(D6,THREED('[test.xls]M010:M305'!$J$19:$K$19),2,0)
> >
> > replace test.xls with the name of the workbook
> >
> > The other workbook needs to be open or you'll get a REF error when you
try
> > to calculate it
> >
> > I am assuming here that the sheets are named M010:M305
> >
> >
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> > "Ray Stubblefield" <[email protected]> wrote in
> > message news:[email protected]...
> > > Good morning, Peo.
> > > I am having difficulty.
> > > Following your lead, I tried:
> > > =VLOOKUP(D6,THREED('M010:M305'!$J$19:$K$19),2)
> > >
> > > It immediately changes to
> > > =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2)
> > >
> > >
> > > Upon copying down my list of search criteria, I get the following:
> > > =VLOOKUP(D6,THREED('M010:[M305]M305'!$J$19:$K$19),2) = 208,000
> > > =VLOOKUP(D7,THREED('M010:[M305]M305'!$J$19:$K$19),2) =208,000
> > > =VLOOKUP(D8,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124
> > > =VLOOKUP(D9,THREED('M010:[M305]M305'!$J$19:$K$19),2) =5,124
> > > There are no worksheets is this particular workbook that the reference
can
> > > search, so it is returning the previous result.
> > >
> > > I tried:
> > >
> >
=INDEX(THREED('M010:M305'!$J$19:$K$19),MATCH(D6,THREED('M010:M305'!$J$19)0),
> > 2)
> > >
> > > It immediately changes to:
> > >
> >
=INDEX(THREED('M010:[M305]M305'!$J$19:$K$19),MATCH(D6,THREED('M010:[M305]M30
> > 5'!$J$19)0),2)
> > >
> > > but, happily, it does give me the correct results. However, upon
saving
> > and
> > > closing, the cells contain:
> > >
> >
=INDEX(THREED('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19:$K$19),MATCH(D6,THREE
> > D('G:\TAX\2004\ZZZZ\M010:[M399]M399'!$J$19),0),2)
> > >
> > > This is not a valid reference to the worksheet, but only to the
directory
> > > containing that worksheet.
> > >
> > > I do appreciate your help with this, Peo!
> > >
> > > Ray Stubbleefield
> > >
> > > "Peo Sjoblom" wrote:
> > >
> > > > There is no built in lookuop that will work over multiple sheets, I
> > would
> > > > recommend using Laurent Longre's excellent Morefunc that can be
> > downloaded
> > > > here
> > > >
> > > > http://longre.free.fr/english/
> > > >
> > > >
> > > > descriptions here
> > > >
> > > > http://www.rhdatasolutions.com/morefunc/
> > > >
> > > > In your case it would be
> > > >
> > > > =VLOOKUP(Lookup_value,THREED( etc
> > > >
> > > >
> > > > Regards,
> > > >
> > > > Peo Sjoblom
> > > >
> > > > "Ray Stubblefield" wrote:
> > > >
> > > > > I want to create a summary sheet that will lookup a particular
cells
> > value on
> > > > > multiple sheets (averaging 58 sheets) in a workbook (e.g. $J$19)
based
> > upon a
> > > > > cell next to it ($I$19) that will match the criteria on the
summary
> > sheet
> > > > > (e.g. w1, w2, w3).
> > > > > I have tried VLOOKAllSheets but when there are other similar
workbooks
> > open,
> > > > > it doesn't work right.
> > > > > Anyone? I appreciate your help.
> >
> >
> >



"Debra" wrote:

> Were you able to figure this out? I am looking to do the same thing...i have
> 30 sheets that are all templates with different information on each sheet but
> want to compare on a total sheet the information from each tab same cell...
>
> "Ray Stubblefield" wrote:
>
> > The situation is more complex. I am trying to create a summary sheet that
> > looks up results contained in a specific cell across approx. 100 sheets. I
> > need to lookup based on a specific reference number that is contained on both
> > sheets.
> >
> > I am currently working on the TREED function using either VLOOKUP or
> > INDEX,MATCH formulas.
> >
> > "Don Guillett" wrote:
> >
> > > this maybe.
> > > =sum(sheet1:sheet21!a1)
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > [email protected]
> > > "Ray Stubblefield" <[email protected]> wrote in
> > > message news:[email protected]...
> > > > I have a summary sheet where I want to lookup a number that is referenced
> > > the
> > > > same on multiple sheets. I desire to have the summary look to particular
> > > tab
> > > > names and return the amount in that particular cell.
> > > > Any ideas please?
> > >
> > >
> > >