I need a couple formulas to search a range of sheets on my workbook.

The first one needs to be able to seach through a range of sheets (exp.
sheet 20 to sheet 27) and find my value then I want it to display the name of
the sheet it is on.

Second is to seach a range of sheets then display a value of a specific cell
on that sheet it is found on.

Thank you for the help!

jtinne wrote:
I need a couple formulas to search a range of sheets on my workbook.

The first one needs to be able to seach through a range of sheets (exp.
sheet 20 to sheet 27) and find my value then I want it to display the name of
the sheet it is on.

For that you need a VB Script.



Second is to seach a range of sheets then display a value of a specific cell
on that sheet it is found on.

Thank you for the help!

VLOOKUP doesn't "search" in the way that "Find" searches. It looks up
data in a table that meet certain criteria. In other words it returns a
value of a cell where that cell POSITION matches criteria that you have set.

What EXACTLY are you trying to achieve with this?


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

I'm trying to find where a specific item number is located within the range
of sheets. The name of the sheet is the location I need to know. In another
cell I want be able to look up that specific item number and return the
contents of a specific cell in the sheet that the item number is found in.

"Gordon" wrote:

jtinne wrote:
I need a couple formulas to search a range of sheets on my workbook.

The first one needs to be able to seach through a range of sheets (exp.
sheet 20 to sheet 27) and find my value then I want it to display the name of
the sheet it is on.

For that you need a VB Script.



Second is to seach a range of sheets then display a value of a specific cell
on that sheet it is found on.

Thank you for the help!

VLOOKUP doesn't "search" in the way that "Find" searches. It looks up
data in a table that meet certain criteria. In other words it returns a
value of a cell where that cell POSITION matches criteria that you have set.

What EXACTLY are you trying to achieve with this?


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

jtinne wrote:
I'm trying to find where a specific item number is located within the range
of sheets. The name of the sheet is the location I need to know. In another
cell I want be able to look up that specific item number and return the
contents of a specific cell in the sheet that the item number is found in.

"Gordon" wrote:


jtinne wrote:

I need a couple formulas to search a range of sheets on my workbook.

The first one needs to be able to seach through a range of sheets (exp.
sheet 20 to sheet 27) and find my value then I want it to display the name of
the sheet it is on.

For that you need a VB Script.



Second is to seach a range of sheets then display a value of a specific cell
on that sheet it is found on.

Thank you for the help!

VLOOKUP doesn't "search" in the way that "Find" searches. It looks up
data in a table that meet certain criteria. In other words it returns a
value of a cell where that cell POSITION matches criteria that you have set.

What EXACTLY are you trying to achieve with this?


Well in that case VLOOKUP will not do what you want. I think you need to
investigate VB Scripts to do this

--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

Hi

i'm with Gordon on this one - vlookup will not do this for you ... you'll
need to use VBA code to cycle through the sheets looking for the item number
and then return the sheet name - which can then be used in a vlookup for
related data.

Cheers
JulieD


"jtinne" wrote in message
...
I'm trying to find where a specific item number is located within the
range
of sheets. The name of the sheet is the location I need to know. In
another
cell I want be able to look up that specific item number and return the
contents of a specific cell in the sheet that the item number is found in.

"Gordon" wrote:

jtinne wrote:
I need a couple formulas to search a range of sheets on my workbook.

The first one needs to be able to seach through a range of sheets (exp.
sheet 20 to sheet 27) and find my value then I want it to display the
name of
the sheet it is on.

For that you need a VB Script.



Second is to seach a range of sheets then display a value of a specific
cell
on that sheet it is found on.

Thank you for the help!

VLOOKUP doesn't "search" in the way that "Find" searches. It looks up
data in a table that meet certain criteria. In other words it returns a
value of a cell where that cell POSITION matches criteria that you have
set.

What EXACTLY are you trying to achieve with this?


--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

Okay I understand vlookup will not work and based on all the responses I've
have recieved, I need to use VBE. I have never used that before and don't
even know where to begin. If anymore can offer some tips, please do.

Thank You.

"jtinne" wrote:

I need a couple formulas to search a range of sheets on my workbook.

The first one needs to be able to seach through a range of sheets (exp.
sheet 20 to sheet 27) and find my value then I want it to display the name of
the sheet it is on.

Second is to seach a range of sheets then display a value of a specific cell
on that sheet it is found on.

Thank you for the help!

Hi

you can use code along the following lines

Sub FindAcrossSheets()
Dim wks As Worksheet
Dim sname As String

i = 20
For Each wks In Worksheets
If UCase(wks.Name) = "SHEET" & i And i < 28 Then
With wks.Cells
Set c = .Find("AAAAA")
If Not c Is Nothing Then
Sheets("Sheet1").Range("A1").Value = c.Worksheet.Name
Exit Sub
End If
End With
i = i + 1
End If
Next wks

End Sub


---
this code looks for the text AAAAA in any worksheet (with sheet names of
Sheet20 to Sheet 27) and if found puts the sheet name into Sheet1 cell A1

to use this code, right mouse click on a sheet tab, choose view code, in the
vbe window choose insert / module - copy & paste the code in there. Change
AAAAA to what you're looking for

switch back to the workbook ALT & F11 and choose tools / macro / macros -
find "FindAcrossSheets" and press RUN.

Hope this helps
Cheers
JulieD


"jtinne" wrote in message
...
Okay I understand vlookup will not work and based on all the responses
I've
have recieved, I need to use VBE. I have never used that before and don't
even know where to begin. If anymore can offer some tips, please do.

Thank You.

"jtinne" wrote:

I need a couple formulas to search a range of sheets on my workbook.

The first one needs to be able to seach through a range of sheets (exp.
sheet 20 to sheet 27) and find my value then I want it to display the
name of
the sheet it is on.

Second is to seach a range of sheets then display a value of a specific
cell
on that sheet it is found on.

Thank you for the help!

I was hoping fo something more simple. Multiple people will be using this
work book. I was hoping to find something were someone could just go to one
page and enter say number "2136" and it would pull up information on that
item, for instance the location, description ect.

I'm basing what I'm trying do on a vlookup program I did looking locations
by the location number.

Thank You

"JulieD" wrote:

Hi

you can use code along the following lines

Sub FindAcrossSheets()
Dim wks As Worksheet
Dim sname As String

i = 20
For Each wks In Worksheets
If UCase(wks.Name) = "SHEET" & i And i < 28 Then
With wks.Cells
Set c = .Find("AAAAA")
If Not c Is Nothing Then
Sheets("Sheet1").Range("A1").Value = c.Worksheet.Name
Exit Sub
End If
End With
i = i + 1
End If
Next wks

End Sub


---
this code looks for the text AAAAA in any worksheet (with sheet names of
Sheet20 to Sheet 27) and if found puts the sheet name into Sheet1 cell A1

to use this code, right mouse click on a sheet tab, choose view code, in the
vbe window choose insert / module - copy & paste the code in there. Change
AAAAA to what you're looking for

switch back to the workbook ALT & F11 and choose tools / macro / macros -
find "FindAcrossSheets" and press RUN.

Hope this helps
Cheers
JulieD


"jtinne" wrote in message
...
Okay I understand vlookup will not work and based on all the responses
I've
have recieved, I need to use VBE. I have never used that before and don't
even know where to begin. If anymore can offer some tips, please do.

Thank You.

"jtinne" wrote:

I need a couple formulas to search a range of sheets on my workbook.

The first one needs to be able to seach through a range of sheets (exp.
sheet 20 to sheet 27) and find my value then I want it to display the
name of
the sheet it is on.

Second is to seach a range of sheets then display a value of a specific
cell
on that sheet it is found on.

Thank you for the help!

jtinne wrote:
I was hoping fo something more simple. Multiple people will be using this
work book. I was hoping to find something were someone could just go to one
page and enter say number "2136" and it would pull up information on that
item, for instance the location, description ect.

I'm basing what I'm trying do on a vlookup program I did looking locations
by the location number.

Thank You

That sounds more like a database query rather than a spreadsheet search
- have you considered importing the spreadsheet data into Access?

--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

We don't use Access.

"Gordon" wrote:

jtinne wrote:
I was hoping fo something more simple. Multiple people will be using this
work book. I was hoping to find something were someone could just go to one
page and enter say number "2136" and it would pull up information on that
item, for instance the location, description ect.

I'm basing what I'm trying do on a vlookup program I did looking locations
by the location number.

Thank You

That sounds more like a database query rather than a spreadsheet search
- have you considered importing the spreadsheet data into Access?

--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

jtinne wrote:
We don't use Access.


Don't "use" it, or "haven't got it"?

--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

haven't got it

"Gordon" wrote:

jtinne wrote:
We don't use Access.


Don't "use" it, or "haven't got it"?

--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

Hi

AFAIK you won't get anything more "simple", however you can customise my
code to receive entry from a cell (instead of the AAAAA) and then once the
user has entered the data - give them a find button which launches the code.

will this do? if you'ld like help adjusting the code to meet this
requirement, just let me know the sheet name & cell reference where your
users will be typing in 2136. OR we can link it to an input box which asks
the user what they want to look for when they press the find button ...

Cheers
JulieD

"jtinne" wrote in message
...
I was hoping fo something more simple. Multiple people will be using this
work book. I was hoping to find something were someone could just go to
one
page and enter say number "2136" and it would pull up information on that
item, for instance the location, description ect.

I'm basing what I'm trying do on a vlookup program I did looking locations
by the location number.

Thank You

"JulieD" wrote:

Hi

you can use code along the following lines

Sub FindAcrossSheets()
Dim wks As Worksheet
Dim sname As String

i = 20
For Each wks In Worksheets
If UCase(wks.Name) = "SHEET" & i And i < 28 Then
With wks.Cells
Set c = .Find("AAAAA")
If Not c Is Nothing Then
Sheets("Sheet1").Range("A1").Value = c.Worksheet.Name
Exit Sub
End If
End With
i = i + 1
End If
Next wks

End Sub


---
this code looks for the text AAAAA in any worksheet (with sheet names of
Sheet20 to Sheet 27) and if found puts the sheet name into Sheet1 cell A1

to use this code, right mouse click on a sheet tab, choose view code, in
the
vbe window choose insert / module - copy & paste the code in there.
Change
AAAAA to what you're looking for

switch back to the workbook ALT & F11 and choose tools / macro / macros -
find "FindAcrossSheets" and press RUN.

Hope this helps
Cheers
JulieD


"jtinne" wrote in message
...
Okay I understand vlookup will not work and based on all the responses
I've
have recieved, I need to use VBE. I have never used that before and
don't
even know where to begin. If anymore can offer some tips, please do.

Thank You.

"jtinne" wrote:

I need a couple formulas to search a range of sheets on my workbook.

The first one needs to be able to seach through a range of sheets
(exp.
sheet 20 to sheet 27) and find my value then I want it to display the
name of
the sheet it is on.

Second is to seach a range of sheets then display a value of a
specific
cell
on that sheet it is found on.

Thank you for the help!

Okay, where the number they will enter to search for will be on sheet name
"Item Search" in cell A2. In B2 of the same page I would like the name of
the sheet it is found on displayed. In C2 I would like what is entered in
merged cell 3C-F of the page found on to display. If that is not possible
for C2 to display I will have to do without.

The sheets I need to search through are named K21-K37, K57 and K89

Also in the future more sheets maybe added, what would need to be done add
those sheets to the search?

If the number they are searching for is in a merged cell, will that effect
anything as well as how the cell is formatted?

Thank You

"JulieD" wrote:

Hi

AFAIK you won't get anything more "simple", however you can customise my
code to receive entry from a cell (instead of the AAAAA) and then once the
user has entered the data - give them a find button which launches the code.

will this do? if you'ld like help adjusting the code to meet this
requirement, just let me know the sheet name & cell reference where your
users will be typing in 2136. OR we can link it to an input box which asks
the user what they want to look for when they press the find button ...

Cheers
JulieD

"jtinne" wrote in message
...
I was hoping fo something more simple. Multiple people will be using this
work book. I was hoping to find something were someone could just go to
one
page and enter say number "2136" and it would pull up information on that
item, for instance the location, description ect.

I'm basing what I'm trying do on a vlookup program I did looking locations
by the location number.

Thank You

"JulieD" wrote:

Hi

you can use code along the following lines

Sub FindAcrossSheets()
Dim wks As Worksheet
Dim sname As String

i = 20
For Each wks In Worksheets
If UCase(wks.Name) = "SHEET" & i And i < 28 Then
With wks.Cells
Set c = .Find("AAAAA")
If Not c Is Nothing Then
Sheets("Sheet1").Range("A1").Value = c.Worksheet.Name
Exit Sub
End If
End With
i = i + 1
End If
Next wks

End Sub


---
this code looks for the text AAAAA in any worksheet (with sheet names of
Sheet20 to Sheet 27) and if found puts the sheet name into Sheet1 cell A1

to use this code, right mouse click on a sheet tab, choose view code, in
the
vbe window choose insert / module - copy & paste the code in there.
Change
AAAAA to what you're looking for

switch back to the workbook ALT & F11 and choose tools / macro / macros -
find "FindAcrossSheets" and press RUN.

Hope this helps
Cheers
JulieD


"jtinne" wrote in message
...
Okay I understand vlookup will not work and based on all the responses
I've
have recieved, I need to use VBE. I have never used that before and
don't
even know where to begin. If anymore can offer some tips, please do.

Thank You.

"jtinne" wrote:

I need a couple formulas to search a range of sheets on my workbook.

The first one needs to be able to seach through a range of sheets
(exp.
sheet 20 to sheet 27) and find my value then I want it to display the
name of
the sheet it is on.

Second is to seach a range of sheets then display a value of a
specific
cell
on that sheet it is found on.

Thank you for the help!