Pull only rows from another spreadsheet that match certain criteria

[jaihawk8]

Every quarter, I have to do bonus statements for our sales rep. Part of that includes giving them the detail that make up their sales for the quarter. I have a master spreadsheet that contains all of the information for all of the reps. Normally I filter that for each rep, copy and paste their sales into their spreadsheet. Is there a way, in their spreadsheet, to write something that will pull their information from the master so I don't have to filter, copy, and paste for 45 different reps?

 

[Muhammad_Usman]

Every quarter, I have to do bonus statements for our sales rep. Part of that includes giving them the detail that make up their sales for the quarter. I have a master spreadsheet that contains all of the information for all of the reps. Normally I filter that for each rep, copy and paste their sales into their spreadsheet. Is there a way, in their spreadsheet, to write something that will pull their information from the master so I don't have to filter, copy, and paste for 45 different reps?

Yes I think you can grab information from master-sheet to sub sheets. Would you like to share your workbook or format of your sheet for batter result.

 

[vcoolio]

Hello Jaihawk8,

Further to Muhammad_Usman's request, to share your workbook here, upload it to a free file sharing site such as Drop Box and then post the link to your file back here. If your data is sensitive then please use dummy data.

Cheerio,
vcoolio.

 

[jaihawk8]

Yes I think you can grab information from master-sheet to sub sheets. Would you like to share your workbook or format of your sheet for batter result.

Okay, here are examples of the 2 files.

Commission Log Example is the Master File
Earnings Statement Example is the Sales Rep File

What I would like to do in the Sales Rep File is pull all of the rows from the Commission Log Example that meet certain criteria. In this example, I would like to populate the Earnings Statement File with all of the rows that are for the Sales Rep "Brett Saberhagen".


https://www.dropbox.com/s/54zuazomqeomoda/Commission Log Example.xlsx?dl=0

https://www.dropbox.com/s/cojkzr4vc3cb097/Earnings Statment Example.xlsx?dl=0

 

[Aladin Akyurek]

Insert a new column in front of the current column A in

Earnings Statement File.

Now enter in A1:

Brett Saberhagen

In A2 control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF('[Commission Log Example.xlsx]Sheet1'!$E$2:$E$9=$A$1,ROW('[Commission Log Example.xlsx]Sheet1'!$E$2:$E$9)-ROW(INDEX('[Commission Log Example.xlsx]Sheet1'!$E$2:$E$9,1,1))+1),ROWS($A$2:A2)),"")

In B2 just enter, copy across to AC2, and copy down:

=IF($A2="","",INDEX('[Commission Log Example.xlsx]Sheet1'!$A$2:$AB$9,$A2,MATCH(B$1,'[Commission Log Example.xlsx]Sheet1'!$A$1:$AB$1,0)))_{^{<strike>
</strike>}}​

​

 

[jaihawk8]

Insert a new column in front of the current column A in

Earnings Statement File.

Now enter in A1:

Brett Saberhagen

In A2 control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF('[Commission Log Example.xlsx]Sheet1'!$E$2:$E$9=$A$1,ROW('[Commission Log Example.xlsx]Sheet1'!$E$2:$E$9)-ROW(INDEX('[Commission Log Example.xlsx]Sheet1'!$E$2:$E$9,1,1))+1),ROWS($A$2:A2)),"")

In B2 just enter, copy across to AC2, and copy down:

=IF($A2="","",INDEX('[Commission Log Example.xlsx]Sheet1'!$A$2:$AB$9,$A2,MATCH(B$1,'[Commission Log Example.xlsx]Sheet1'!$A$1:$AB$1,0)))_{^{<strike>
</strike>}}​

​

I followed your instructions. In A2, it gives 1 and only completes 1 line after the formula in B2 has been copied down. I'm thinking that A2 should be 3, since there are 3 instances of Brett Saberhagen. What did I do wrong?

 

[Aladin Akyurek]

I followed your instructions. In A2, it gives 1 and only completes 1 line after the formula in B2 has been copied down. I'm thinking that A2 should be 3, since there are 3 instances of Brett Saberhagen. What did I do wrong?

Control+shift+enter: press down the control and the shift keys at the same time while you hit the nter key. If done correctly, Excel itself puts a pair of { and } around the formula in recognition.

 

[jaihawk8]

Control+shift+enter: press down the control and the shift keys at the same time while you hit the nter key. If done correctly, Excel itself puts a pair of { and } around the formula in recognition.

I do have the { } around the formula, but it's still giving 1 in A2. Is there a need for it to know how many instances of Brett Saberhagen appear first so it knows how many rows it needs to come up with?

 

[Aladin Akyurek]

I do have the { } around the formula, but it's still giving 1 in A2. Is there a need for it to know how many instances of Brett Saberhagen appear first so it knows how many rows it needs to come up with?

See: https://www.dropbox.com/s/8ej99w2w8hjtgpn/Earnings Statment Example.xlsx?dl=0

When you open this file, the source file Commission Log Example must also be open.

 

[jaihawk8]

See: https://www.dropbox.com/s/8ej99w2w8hjtgpn/Earnings Statment Example.xlsx?dl=0

When you open this file, the source file Commission Log Example must also be open.

Thank you for that. Two additional questions...

Can this be written to include two criteria? For instance, we already have it using the name of the Sales Rep. Would it also be possible to have it use the payroll date to only pull in the information for the rep for a specific pay period?

Second question is if it's possible to have it pull in the same formatting as on the Commission Log.

I guess a third question would be if this could be made into an expanding table. For formatting purposes.

Thanks!