I would like to use vlookup to get data that is on several different pages.

I realize I could do this by =SUM('ATStemplate1:PH TEMPLATE 2'!F31), where I
change the F31 to be the cell I want for each different column, however I am
already out to column BA. When columns are added or deleted (by date) than I
run into problems.

What I would LIKE to do is something similar to this
=sum(VLOOKUP(b$4,'3005:3007'!$1:$65536,6,FALSE))
Where "6" will change depending on the date (which I accomplish with a
Hlookup)

I thought about
=sum(VLOOKUP(b$4,'3005:3007'!$1:$65536,6,FALSE)),V LOOKUP(b$4,'3006'!$1:$65536,6,FALSE)),VLOOKUP(b$4, '3007'!$1:$65536,6,FALSE)))
However I have like 25 tabs, which would make this formula very large, not
to mention the addition and deletion of tabs.

Is this even possible other than the long hand written way

OzGrid to the rescue:
Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _
Col_num as Integer, Optional Range_look as Boolean)

''''''''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid.com

'Use VLOOKUP to Look across ALL Worksheets and stops _
at the first match found.
'''''''''''''''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
Dim vFound

On Error Resume Next

For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup _
(Look_Value, Tble_Array, _
Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then Exit For
Next wSheet

Set Tble_Array = Nothing
VLOOKAllSheets = vFound
End Function

To use this code do this:

1. Push Alt+F11 and go to InsertModule
2. Copy and paste in the code.
3. Push Alt+Q and Save.

Now in any cell put in the Function like this:

=VLOOKAllSheets("Dog",C1:E20,2,FALSE)

Where "Dog" is the value to find

" " C1:E20 is the range to look in the first column and find "Dog"

" " 2 is the relative column position in C1:E20 to return return our result
from.

" " FALSE (or ommited) means find and exact match of "Dog"

In other words the UDF has the exact same syntax as Excels VLOOKUP. The only
difference is that it will look in ALL Worksheets and stop at the first
match.

Regards,
Ryan--


--
RyGuy


"Chris" wrote:

I would like to use vlookup to get data that is on several different pages.

I realize I could do this by =SUM('ATStemplate1:PH TEMPLATE 2'!F31), where I
change the F31 to be the cell I want for each different column, however I am
already out to column BA. When columns are added or deleted (by date) than I
run into problems.

What I would LIKE to do is something similar to this
=sum(VLOOKUP(b$4,'3005:3007'!$1:$65536,6,FALSE))
Where "6" will change depending on the date (which I accomplish with a
Hlookup)

I thought about
=sum(VLOOKUP(b$4,'3005:3007'!$1:$65536,6,FALSE)),V LOOKUP(b$4,'3006'!$1:$65536,6,FALSE)),VLOOKUP(b$4, '3007'!$1:$65536,6,FALSE)))
However I have like 25 tabs, which would make this formula very large, not
to mention the addition and deletion of tabs.

Is this even possible other than the long hand written way

Kinda what I was looking for, but not quite.

I will need it to find ALL of the fields and not stop at the first one.

Basically I have tabs with Job numbers (say 50 jobs, starting at 3000)
The job is broken into different tasks (say task#1 - task#12)
Each task is forecasted by week.

What I need to know is how many hours I spend on task#3 the week of 4/21
across ALL jobs.

Reading the VBA that you have written it doesn't look like that sum's up the
hours, looks like if ends first time it finds the correct week.
I would be using the same formula to get each additional week per task, so
the column has to be variable.

I have been using this code to get the information from a single tab.

Vlookup($A10,3000!$1:$65536,hlookup(F$1,3000!$1$65 526,2,false)false)

I just need to add that code 50 times to get all the jobs (of course
switching the 3000 for 3001, 3002, 3003, etc.