Pull Data from one Workbook to another not using Vlookup help!

[Grimlocc]

Hello,

I am trying to figure out a formula that will allow one spreadsheet to pull in data from another.

I have one spreadsheet that contains data relating to staff in columns A to BO and an Area Pay Code relating to each member of staff is in column S. I will call this spreadsheet Full Staff List.

I have a separate spreadsheet which I currently copy and paste data into from the Full Staff List and on this spreadsheet I only have staff who are on a particular Area Pay Code (code 7). I will call this spreadsheet Area Pay Code List.

What I would like to be able to do is make it so that the Area Pay Code List spreadsheet automatically looks at the Full Staff List spreadsheet, identifies all instances of code 7 in column S, then, for each member of staff on code 7, it brings in their name, Pay ID, their line manager, hours worked, pay grade and location. All of these items of data are on the Full Staff List spreadsheet along the row for each member of staff.

I know how to do a Vlookup but I don’t think that will work because I want to pull in data from more than one cell along a row.

Is there a way to do this? I’m trying to think of a formula that says:

Look in another spreadsheet at column S. Find all instances of pay code 7. For each code 7 you find, look along the same row and bring in data from cells x,y and z.

The difficulty I think is that the spreadsheet that I want this formula in has no references to look up against in the other spreadsheet other than knowing that I want pay code 7. Also, I wish to bring in data from multiple cells along the row but not necessarily cells that are next to each other.

Hope you can help and thanks very much!

 

[igold]

If you wanted to use VBA, you could try something like this. You would have to change the line in red to delete the columns that you do not want after the copy.

Sub FilterCopy()


    Dim lRow As Long
    Dim wsFSL As Worksheet: Set wsFSL = Worksheets("Full Staff List")
    Dim wsAPC As Worksheet: Set wsAPC = Worksheets("Area Pay Code")
    
    lRow = wsFSL.Cells(Rows.Count, 1).End(xlUp).Row
    wsFSL.Range("$A$1:$BO$" & lRow).AutoFilter Field:=19, Criteria1:=7
    wsFSL.Range("$A$1:$BO$" & lRow).SpecialCells(xlCellTypeVisible).Copy wsAPC.Range("A1")
    [COLOR=#ff0000]wsAPC.Range("D:K", "W:W").EntireColumn.Delete[/COLOR]   'Change these columns to fit your needs
    wsFSL.AutoFilterMode = False
    
End Sub

 

[igold]

Whoops, the column I highlighted in red should really read as follows...

wsAPC.Range("D:K,W:W").EntireColumn.Delete   'Change these columns to fit your needs

Sorry about the confusion!

 

[Grimlocc]

Thanks so much for this- I'll give it a go when I'm back in work. Bank holiday this weekend so it will be Tuesday

 

[igold]

Happy Holidays!

Let me know how it goes.

 

[FDibbins]

Just because you need to pull from different columns, doesn't mean you cant sill use VLOOKUP. Instead of specifying the column number to use, you could replace that with MATCH to ID which column you need it to use, something lik...

=vlookup(Z2,$A$1:$J200,MATCH(Z1,$A$1:$J$1,0),0)
Z2=what you are trying to find
A1:J200 = your data table
Z1 = the heading name you want
A1:J1 = table headings

 

[Grimlocc]

Hi,

I have tried both of your suggestions but am really struggling – I’m pretty much a novice when it comes to Excel unfortunately. I'll let you know where I'm struggling.

For the VBA, I am struggling with:

I am getting a runtime error 9: Subscript out of range message.

Where you put the names of my spreadsheets on the second and third rows of data, how does it know where to look? If both of my spreadsheets are in the same folder, will it just find them? If I am running the macro from the Area Pay Code spreadsheet, why does it need the name of that spreadsheet in the macro?
At the end of the sixth row, where you have put A1 in brackets, what is the A1 representing?
In the part where I have to delete and add in my columns, I need to delete a lot of columns; would I do this by typing A:A, B:B, C:C and so on? Also, if I can delete a load of columns that are next to each other can I do this: F:K, AB:AY etc etc?
How will this know which columns to populate?

For the vlookup, I am struggling with:

Where do I enter the formula if I want to populate several columns? Normally, when I do a vlookup, I am just pulling data into the cell I type the formula into but here, I want to pull in more than one cell’s worth of data into multiple cells.
Where you put Z2, here I assume I just type in the pay code? (in this case, I will type in 7)
A1:J200 = your data table. Do I just drag out and select the entire table of data that I am pulling from? A1 to BO860?
Z1 = the heading name I want. I’m not sure what you mean – is this the heading of the column I am pulling data from or into? Do I actually type the heading name or just the cell number?
A1:J1 = table headings. Is this the name of each heading in the table I am pulling data into? Do they have to match the headings on the table I am pulling from? Again, do I need the cell numbers here or the actual heading names?
Does it matter that the data I want to pull in is to the left of the pay codes in the table I am pulling from?

Very sorry to have to ask for clarification, like I say, I’m pretty much a novice. For clarification, I’ll let you know the layout of my spreadsheets:

The spreadsheet I am pulling into has my Pay Code in cell C3 – it just stays there as a reference.

Cells A4 to F4 are the headings of my columns that I want populating:
Staff Number, Name, Line Manager, Hours Worked, Pay Grade, Location.

The spreadsheet I am pulling from is:

There are 67 columns, the Area Pay Code is in column S. The data I want to pull in is in columns:
A,B,K,M,N,G

Thanks very much for your help and sorry to be a pain!

 

[igold]

No problem, let's sse if we can get this sorted out...

I am getting a runtime error 9: Subscript out of range message.

Are both spreadsheets in the same workbook, and do the names of the sheets match exactly to what is written in the code. If you debug does it show what line the error is on.

Where you put the names of my spreadsheets on the second and third rows of data, how does it know where to look? If both of my spreadsheets are in the same folder, will it just find them? If I am running the macro from the Area Pay Code spreadsheet, why does it need the name of that spreadsheet in the macro?

This is tied to the first question. If the spreadsheets are in different workbooks, then you will have to provide the full path and name of the both workbooks. The code should reside in and run from the "Full Staff List" sheet.

At the end of the sixth row, where you have put A1 in brackets, what is the A1 representing?

The A1 in brackets in the cell that the filtered info gets copied to. It will fill in the entire sheet from there.

In the part where I have to delete and add in my columns, I need to delete a lot of columns; would I do this by typing A:A, B:B, C:C and so on? Also, if I can delete a load of columns that are next to each other can I do this: F:K, AB:AY etc etc?

Yes, I tried to demonstrate that in the code. You can delete isolated columns such as Column B with B:B, and you can delete contiguous columns by using F:K.

How will this know which columns to populate?

All columns will be populated identically to the Full Staff List sheet (except filtered for "7"), that is why you have to supply which columns you want to delete at the end of the code.

Also after reading your questions for Ford's VLookUp solution, please be specific as to what row is your header row and what row your data starts on...

 

[Grimlocc]

Hi Igold - thanks so much for helping.

The sheet that I am pulling into has it's headers on row 4 - so I want to pull into columns A, B, C, D, E and F. The numbers of rows can change depending on how many people I have on Pay Code 7.

Both sheets are actually in separate work books held in different places so I'll factor in the full paths for them.

I'll give it another go now, assuming that the data in your original VBA was going to put it into A1?

Thanks very much - greatly appreciated.

 

[igold]

A couple of quick questions:

Where is the header row on your source sheet?
On the "Area Pay Code" sheet, are you saying the the headers are on Row 4 and are they identical to the corresponding headers on the Full Staff List?
What is the full name of the workbook that has the "Area Pay Code"?
Are you now saying that the only columns you want to keep on the "Area Pay Code" Sheet are the adjacent columns A:F?