• 460K Members
• 10.1K Online
• 558K Conversations

New Contributor

# Copy data from 2 sheets to others based on criteria

Hi Team,

I have an inquiry similar to the following

https://techcommunity.microsoft.com/t5/Excel/Copy-Data-to-Other-Sheets-Columns-Based-on-Criteria/td-...

I have a workbook with 5 worksheets. 1 for each of 2 salespersons, and 1 for each of 3 locations.

I'd like each of the salespeople to fill in their sheet - with a field for the location, and then have the data automatically populate in to the location sheets.

Using the Array formula solution I have it going from one salesperson to the three locations no worries, but can't get it to go from the 2 sales people to three.

The Array formula is my preferred solution as it updates automatically without a button click for the user.

It seems like the loop count for the second IF is starting from where the first left off maybe?

3 Replies

# Re: Copy data from 2 sheets to others based on criteria

I changed George St. cell D7 from a CSE formula to a regular formula that handles both Jack and Liam. The AGGREGATE function requires Excel 2010 or later, and has similar functionality to SMALL but doesn't need the CSE.

Note that the formula returns 0 when the target cell is blank. You may suppress those zeros in columns N:Q by using a Custom number format #;-#;;@. I don't have a good tweak for your conditional formatting in column  R to get the pink color, however.

=IFERROR(
INDEX(Jack!D\$7:D\$29,
AGGREGATE(15,6,
(ROW(Jack!\$C\$7:\$C\$29) - ROW(Jack!\$C\$7)+1)/(Jack!\$C\$7:\$C\$29=\$T\$4),
ROWS(D\$7:D7)
)
),
IFERROR(
INDEX(Liam!D\$7:D\$29,
AGGREGATE(15,6,
(ROW(Liam!\$C\$7:\$C\$29) - ROW(Liam!\$C\$7)+1)/(Liam!\$C\$7:\$C\$29=\$T\$4),
ROWS(D\$7:D7) - COUNTIF(Jack!\$C\$7:\$C\$29,\$T\$4)
)
),""
)
)

Highlighted

# Re: Copy data from 2 sheets to others based on criteria

@Brad Yundt Oh man that is so much tidier than what I've just finished in the last hour.

I used the one to one for 2 tables in each of the location sheets, then stacked each column in the location sheet's table proper.

# Re: Copy data from 2 sheets to others based on criteria

I honestly cannot decide whether to be deeply impressed by your formulae and their layout or horrified at the choice of tools selected for the job!

In PowerQuery all that is required is to append the input tables and filter to give the output tables.  I appreciate that you want a more dynamic behaviour and so are attracted to tables.  If so, I suspect that this one workbook by itself would justify a copy of Office 365 to gain access to the new dynamic array functionality (at the moment only 'insider' versions but expected for wider release any time now).

The formula I used to combine your salesperson data was

= CHOOSE( s#,

INDEX( Jack, recordNum#, columnNum# ),

INDEX( Liam, recordNum#, columnNum# ) )

This table has named sub-ranges 'combinedBranch' and 'combinedData' giving the final formula

= FILTER( combinedData, combinedBranch="George St" )

Each formula occupied a single cell but they spill to fill their respective table.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies