a bit complicated...

[ryanduck]

Here's the situation.

I have 4 different sheets, which have 4 difference pivot tables. Calculated on each of those tables is a grade, tied to a unique identifier in the table. The data tab contains all of the unique identifiers in a column.

On my 5th sheet, I have a BIG list of all of the unique identifiers. I want to use Vlookup to return the grade from each sheet. is there a way to vlookup across 4 different sheets? can I use if statements nested?

Any help would be totally awesome.

 

[ryanduck]

By the way, the grade is text, eg "A,B,C,D, or F"

Here's the situation.

I have 4 different sheets, which have 4 difference pivot tables. Calculated on each of those tables is a grade, tied to a unique identifier in the table. The data tab contains all of the unique identifiers in a column.

On my 5th sheet, I have a BIG list of all of the unique identifiers. I want to use Vlookup to return the grade from each sheet. is there a way to vlookup across 4 different sheets? can I use if statements nested?

Any help would be totally awesome.

 

[Aladin Akyurek]

Have a look at examples with the GETPIVOTDATA function.

 

[ryanduck]

I guess another note... the "Grade" column is not in the pivot table, rather calculated outside of it.

By the way, the grade is text, eg "A,B,C,D, or F"

 

[ryanduck]

And lastly.... sigh...

There are actually 8 sheets. I want this "summary" sheet that's pulling the grades in to pull the grades from sheet sets of 4.

Col C on "summary" will pull grades from sheets 1,3,5,7
while
Col D on "summary" will pull grades from sheets 2,4,6,8

Possible?

I guess another note... the "Grade" column is not in the pivot table, rather calculated outside of it.

 

[ryanduck]

Ok, got this online. Does anyone see a way to make this function ONLY look at certain sheets, instead of all sheets. Lets say catagories are "auto" and "home" if "auto" is in the title of all of the worksheets, can I add something into this function to only have it look at those sheets?

Thanks.



Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)
''''''''''''''''''''''''''''''''''''''''''''''''
'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

 

[ryanduck]

Can anyone help on this one please? I just can't figure out how to make it search some of the workbooks, or selected/certain workbooks...?

 

[Aladin Akyurek]

If a VLOOKUP is expected to return a number, you could also try something like:

LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4,5},
   0,
   VLOOKUP(...),
   VLOOKUP(...),
   VLOOKUP(...),
   VLOOKUP(...)))

 

[Puertorekinsam]

dim ws as worksheet

for each ws in thisworkbook.worksheets

if ws.name like "*auto*" then

(your code)

end if



next

 

[ryanduck]

AWESOME. Thank you. I hate vb code

dim ws as worksheet

for each ws in thisworkbook.worksheets

if ws.name like "*auto*" then

(your code)

end if



next