Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Vlookup across multiple tabs in separate workbook?

Good morning,
Hopefully this is a simple solution.
I have a workbook with each tab (71 total) containing a business plan for a specific account. The account name is in cell A4 and the planned sales amount is in cell X9. I also have a worksheet in a different workbook with same account names in cells A2 - A72 and want to pull the corresponding sales from the first workbook into cells B2 - B72. Any ideas?

Many thanks in advance.
N


Post your answer or comment

comments powered by Disqus
Hi,

Consider me a raw beginner (actually I'm a little more advanced than raw beginner, but not much).

I'm looking to create a pivot table report from data that exists in multiple tabs in my workbook. The data is similarly formatted.

It is a workbook of customer issues. Each customer has its own tab. The column structure is identical, with headers on the columns, but the rows don't have headers.

For illustration purposes, each row is a customer issue. The first columns are:
tracking numberissue descriptionissue categoryissue sizeopen or closedCustomer name (same as the tab name).
When I try and put a pivot table within one customer tab, It works exactly as I expected and I can get the results I'm looking for (such as count by issue category, count by issue category by issue size).

What I want, and can't figure out how to do, is to add a new summary sheet with a pivot table that does the same thing I described above, but includes the data in all the tabs. I would have the highest order sort be customer name.

Suggestions are most welcome and would be gratefully received.

Thanks,
-Mike

ps: I did a search but the closest I saw involved SQL and multiple files. I don't know SQL and was lost trying to figure this out.

Hi everyone,

At my job my team uses a template for work that has our company logo. We have a bunch of these template in progress and need to change the logo across multiple tabs in each workbook.

I've tried to do so by just selecting the tabs and going to "header/footer", selecting "Picture", then "Replace" and I can replace the picture but the problem is when I go to the other tabs it screws up the page setup and print area formatting for all of the other tabs selected except for the first tab.

Is there any way to change the picture in a header across multiple tabs withouth having this margin issue or do I need to use a macro to do this?

Thanks!

I am very new to using Macros and VB in excel. What I need to do is use VLOOKUP across multiple sheets in a different book. So the formula below would work, except using the VLOOKUP in a 3-D referance returns the #Value error.

	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So I found the macro on this site and am trying to use it. Except the macro is designed to for use in the same workbook and with all sheets. So if I am going to be using the new function VLOOKUPAllSheets in Workbook1, how do I have the function lookup the data in Workbook 2 across sheets 3 thru 11?


	VB:
	
 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 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am very new to using Macros and VB in excel. What I need to do is use VLOOKUP across multiple sheets in a different book. So the formula below would work, except using the VLOOKUP in a 3-D referance returns the #Value error.
Code:
So I found the macro below online and am trying to use it.  Except the macro is designed to for use in the same workbook and
with all sheets.  So if I am going to be using the new function VLOOKUPAllSheets in Workbook1, how do I have the function
lookup the data in Workbook 2 across sheets 3 thru 11?

Code:
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


Is there a formula to incorporate a vlookup scanning multiple tabs.

i.e.
Total Tab will scan "GMP" "GMT" "FMP" & "TRN"

returning #n/a for the missing information and the first line item found on
the rest.

Thanks in advance.

Is there a formula to incorporate a vlookup scanning multiple tabs.

i.e.
Total Tab will scan "GMP" "GMT" "FMP" & "TRN"

returning #n/a for the missing information and the first line item found on
the rest.

Thanks in advance.

I am very new to using Macros and VB in excel. What I need to do is use VLOOKUP across multiple sheets in a different book. So the formula below would work, except using the VLOOKUP in a 3-D referance returns the #Value error.
So I found the macro below online and am trying to use it.  Except the macro is designed to for use in the same workbook and
with all sheets.  So if I am going to be using the new function VLOOKUPAllSheets in Workbook1, how do I have the function
lookup the data in Workbook 2 across sheets 3 thru 11?

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


How do you sort multiple tabs in a workbook simultaneously?

Hi Guys - I need help on making a Find Macro with Userform. I tried recording a macro but it won't give me the result I needed. Just need to find a data across 399 tabs in 1 speadsheet.

Thanks!

I have a column of data with a variety of identifier codes. In each row next
to the ID codes is data. There are several tabs within my workbook with
similarly constructed data bases. Can I use Sum Product to summarize the
data across the multiple tabs that match each of the id codes? If not, is
there another way?

Let me first try to explain what I wish to do...and maybe you can provide me some help.

I have an *Large* excel file which has multiple users. Ever day/week the name of the file changes with a new revision.
This excel file has a laundry list of items in Column A. This same list is also on several tabs in the same workbook (albeit not in the same order).

I need to use a Vlookup function to create a summary for each item. In the past, I have copied and pasted as text the tabs I needed in a new workbook and then used VLOOKUP to pull the data I need...this works...but I want to streamline my process some if I can.

Short of actually adding ANOTHER tab in the workbook (file is already 4+MB), is there anyway I can use the VLOOKUP function to search another workbook?

Hello all,

I have a huge spreadsheet of products that I would like to be able to do a Vlookup across 3 tabs (workbooks). All three workbooks have the maximum number of rows for data.

Thank You.

Andy

I want to repeat the same text in the titles across multiple sheets
of
a workbook. I grouped the sheets but the pagesetup shows the options
under the
Sheet Tab Disabled. I want to print titles by repeating the same text
on the top of all
the workbook. Copying the required titles to multiple sheets won't be
a good option because each workbook contains many sheet and there are
several workbooks. As this cannot be done with grouped sheets it would
require VBA macro to perform the operation.
I tried the following code by Gord Dibben.
'select all sheets and then run the macro.
Dim pArea As String, titleRows As String, titleCols As String
Sub Same_Titles()
titleRows = ActiveSheet.PageSetup.PrintTitleRows
titleCols = ActiveSheet.PageSetup.PrintTitleColumns
For Each oSheet In ActiveWindow.SelectedSheets
If TypeName(oSheet) = "Worksheet" Then
oSheet.PageSetup.PrintTitleColumns = titleCols
oSheet.PageSetup.PrintTitleRows = titleRows
End If
Next
End Sub
This worked but not as i required. This sets the same print range and
rows to repeat at top on
each sheet in grouped sheets. ie. Rows 1:3 in each of each
Sheet1,Sheet2.............. are repeated in their respective pages.
But what i need is to repeat the Rows 1:3 of Sheet 1 to be repeated
in
the top of the Sheet1 as well as Sheet2,Sheet3 and other sheets in
the
workbook.
Any help would be appreciated.
Thanks in advance.

I want to repeat the same text in the titles across multiple sheets of
a workbook.
I grouped the sheets but the pagesetup shows the options under the
Sheet Tab Disabled.
I want to print titles by repeating the same text on the top of all
the workbook.
Is there any method i can do this.
Copying the required titles to multiple sheets won't be a good option
because each workbook contains many sheet and there are several
workbooks.
Thanks in advance.

Sorry for the vague title, but I wasn't sure how best to explain it. And I'm not good at explaining things, so apologies if things get confused.

I have 2 spreadsheets: the first has over 300 worksheets containing relatively the same information, and the second is a single sheet containing a list of names separated out into First name, Surname, date of birth. I need to look up each of the names on the smaller spreadsheet and indicate with a 'yes' if the name appears within the larger spreadsheet in the 'Access' section (explained below). That is the basic Vlookup across multiple sheets from the title.

The 'additional criteria' bit is the problem.

The large spreadsheet is a csv file pulled off a database. The information within is split up into separate sheets for each of our offices, and on each sheet the information is split again between those members of staff with access and those without access. So a sheet might look like this:

          First Name       Surname       Date of Birth
Access
               John           Smith          04/09/1984
               Steve          Jones           15/04/1967

No Access
               Dave          Hammond        16/12/1954
               Jenny         Accord        08/11/1982
The problem is that I only need to know those people from the first spreadsheet that have access (I don't need to know what office they're in or on which sheet they appear, merely whether they have access or not), and each section (Access, No Access) is separated only by a header, which, though always in column A as above, won't always be in the same place as each office has a different number of people within it (meaning that the sections will be different sizes depending on the number of staff). Also, there is the added problem that each sheet isn't necessarily split the same way, i.e. with Access at the top and No Access underneath (as above). Some of the sheets are split the otherway around (No Access first, Access underneath).

Is there anyway a vlookup could be concocted on my smaller spreadsheet that will first look up the name in every sheet in the larger spreadsheet and, once it's found it, look up column A and discard the name if it finds No Access first and keep it if it finds Access first.

I hope that makes sense.

Was searching for an answer to this and saw a link in a post about non-array formulas, which got me 90% to the solution I need ... but, I'm still not all the way there; hopefully someone can help.

I'm attempting to vlookup across multiple tabs, and am using the non-array recommendation (the array seems a little above my ability level!). The non-array IS working - I do get the data I expect, however the issue I'm having is that I get #N/As if no data is found, and I need to get a zero instead.

My current formula looks like this:
=IF(ISNA(VLOOKUP($B9,OBIGROSA!$A:$G,6,0)),IF(ISNA(VLOOKUP($B9,OBIGROTERR!$A:$G,6,0)),VLOOKUP($B9,OBI GROBILLTO!$A:$G,6,0),VLOOKUP($B9,OBIGROTERR!$A:$G,6,0)),VLOOKUP($B9,OBIGROSA!$A:$G,6,0))

Any advice on how to get a zero?

I have attached a blank version of the spreadsheet I have inherited, that I would like some help with.

It is a booking system for students to use to book out electrical euipment such as laptops and OHPs. They have an allowance for the year, based on course requirement, year on course etc and they are each allocated a password and login that they can use that will give them access to their own worksheet by keeping everyone elses hidden. They can also see a Calendar sheet which gives them a rough idea of how much equipment will be available by showing who has what out of what date. This isn't perfect given the criteria used, but it seems to work OK.

The system is fairly automatic. Names, initials and passwords are entered onto the sheet called Students, and a copy of the Blank sheet is taken and a new sheet is created using the students initials as a title (you can try this. To logon to the system use the login name Tutor and the password secret. To view the VBA use the password stuff). The students can then only access certain aspects of the sheet, with much of it locked off. When a Tutor wants to approve someone's request they login in using the details above and unlock the entire system by holding down ctrl and pressing U then entering the password (which for the blank system is stuff).

I am having a few problems with the system in that many people are reporting that they can't access it quickly and become stuck at the login screen for a long time. Also, sometimes (especially if people click disable macros) every sheet will become visible and the protection on the sheets disappears. Why this should be a major issue I don't know given that everyone has access to the calendar, but apparently itis.

What I was wondering is if it was possible to keep the same basic system but create a separate workbook that would act as a sort of front sheet for the main system. It would be identical to the blank sheet and when people logged onto it it would open the main workbook, retrieve their information from the workbook and display it in this front end, meaning that there would be no need to hide everyone elses sheet as there would only be a single sheet on this front end workbook. Then, once they had made changes, it would save the amendments on the original sheet and clear the front end.

Did that make sense and is it remotely possible. I'm not fantastic with VBA at the best of times, but can usually muddle my way through. This, however, is beyond me.

(Edit: OK, so it won't let me upload at the minute, so you can't see what I talking about. But is it possible to use a single sheet workbook to display and amend information from a specific sheet in a separarte workbook?)

Hi,

I am trying to do a sumproduct over multiple tabs. I have been looking at a number of forums but still cant seem to find a good answer to this problem.
I am using excel 2007 below is the formula that has come closest to working...but doesn't.

Basically I have a variable number of tabs plus a TOTAL tab, I am trying to do sumproduct based on 2 criterion the first is a text match where the value being matched is located in B2 of the TOTAL tab this is checked against cell A1 in all other tabs, then the second match is checked against dates so C7 contains Dec 11 (in date format MM/DD/YYYY) this is checked against range C7:AB7 on all other tabs when both matches are found I want to sum values on all other tabs in the range of C8:AB8.

SheetList is a Named Range, where I have a dynamic range set up on another tab that lists all tabs in the workbook.

Any one have an idea where I'm going wrong? Or is there a better way to do this?

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&SheetList&"'!$A$1"),,,))=$B$2),--(N(OFFSET(INDIRECT("'"&SheetList&"'!$C$7:$AB$7"),,,))=C$7),--(N(OFFSET(INDIRECT("'"&SheetList&"'!$C$8:$AB$8"),,,))))

In Excel, I have 144 tabs within the same worksheet. I need to use the Edit
> Find option to find a specific product id within each worksheet. For an
example, product ID: ABC. How can I use the "Find" option across all tabs at
the same time? Thanks

starting point - i have an xls book with multiple worksheets, each with same formatting.
looking for formula to search for specific name across multiple worksheets (some form of vlookup?)
once that name is found, i want to sum the values returned from specific column.

for example: summarizing baseball team stats for each player based on a worksheet for each game.
trying not to have to keep going back and forth updating each individuals stat from each game.

Hi everyone!

I have an Excel workbook that has multiple worksheets (tabs). How can I take the print area and footer information from one tab, and apply it to ALL tabs in the workbook?

Thanks!

-Keith

Hi all,

I believe this is a really simple question, but I can't find it through my search. I would like to unhide specified multiple sheets in a workbook at one go. What I'm doing now is something like this:


	VB:
	
 
Sheets("Source 2").Visible = True 
Sheets("Source 3").Visible = True 
Sheets("Source 4").Visible = True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I managed to get the array function when I want to hide the sheets:

	VB:
	
Sheets(Array("Source 1", "Source 2", "Source 3", "Source 4")).Select 
ActiveWindow.SelectedSheets.Visible = False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but I can't do this when I want to unhide them. I do not want to unhide all sheets in the workbook, only those I specify in the code. How can I use the something like the array code to unhide the sheets?

Thanks!

I am working with a large report that needs to be broken out and sent as separate files to recipients for confidentiality purposes. I'd rather not use views/protection since there are many different ways particular people need to see the data, plus it is a very large file and flattening it works to everyone's advantage.

My goal is a macro that will copy each tab into separate workbooks, paste special values, and save as each as Cell A1 (or the tab name-same thing). I have tried recording macros and editing (I'm very new to VBA) many times but it's a mess.

Is this possible?

I am trying to compare data across multiple sheets in a workbook using a criteria that I have selected.

3 sheets in my workbook:

1) eBay Motors data for each SKU we sell and a corresponding Ad Closing % that we have calculated to be the minimum necessary to turn a profit for any number of listings.

2) eBay Stores data for each SKU and a corresponding Ad Closing % that we have calculated to be the minimum necessary to turn a profit for any number of listings.

3) Weekly performance data for all Ads (eBay Motors & eBay stores). Each Ad corresponds to a specific SKU and more importantly a Schedule.

What we would like to be able to do is compare the data from Sheet #3 to the other 2 sheets. Based on the Schedule name (in sheet #3) for each Ad we know whether this was an eBay Motors listing or eBay stores listing. Based on the schedule name including the words "eBay Motors" or "eBay Motors Stores" we know whether the data needs to be compared to Sheet #1 or #2.

But we also need to factor in the SKU# that corresponds for each schedule. Each SKU will have multiple schedules and what we are trying to determine is whether the schedule is effective based on the Ad Closing %.

An example would be for SKU X with Schedule name "eBay Motors Stores 30-Day Maintain 2qty" Since the schedule name has eBay Motors Stores we know that this needs to compare with data on Sheet #2. Once on Sheet #2 it needs to find the same SKU# in the SKU column and then determine whether the corresponding Closing % is greater or less then the Closing % on Sheet #3. If greater then this means the Ad was not profitable for that schedule and it should display "LOSS" and if less then "profitable".

I have attached the worksheet for you to take a look at. Any help would be greatly appreciated.


No luck finding an answer? You could always try Google.