SUMIF?

[JTL9161]

Trying to put together a formula that I think would be a SUMIF but I may be barking up the wrong tree.

I am using letters to simplify.

I have about 250 names in column A on spreadsheet A.
I have 3 other spreadsheet tabs B, C, D
I put a value and date on spreadsheet A column B & C next to the corresponding name in column A.
In column D I put which spreadsheet this person belongs to B, C or D.

So it should look something like this

Name	amount	date	spreadsheet
John	100	11/18/2020	B
Bob	100	11/17/2020	C
Steve	100	11/16/2020	D

What I am trying to do is get this info on spreadsheet A to appear (in the case's above) John's info on spreadsheet B in the same format. Bob's on C and Steve's on C.
Basically from the A spreadsheet I want all the B spreadsheet people show just on B the C's on C and the D's on D

Hope I explained this for everyone to understand.
Appreciate any help
Thank you
James

 

[RustEE2020]

I do not think you can do this without using VBA when you say formula it makes it sound like you are unwilling to use VBA is this true?
Are we just copying the data or do we need relative references in case you update the info on sheet A?

 

[ekrause]

The easiest way to do this would be to sort on Column D and then copy and paste them to the various spreadsheets - you can either do this manually or using VBA as mentioned by @RustEE2020

There's a way to do it formula driven but it assumes you have unique identifiers in the Names Column or that you can create a helper column in your source sheet where you concatenate Name&Date&Spreadsheet into a column. Helper Column is better because you can have the same person/company/entity in column B with different dates but they need to have a unique Name & Date for this to work appropriately.

Source Spreadsheet with Helper Column

Destination Spreadsheet with Helper Column (note name of spreadsheet in cell A1)

Formula (starting in Cell F2)

=INDEX(OFFSET(Sheet7!$B$2:$B$13,0,MATCH(F$1,Sheet7!$B$1:$F$1,0)-1),MATCH(0,INDEX((COUNTIF(F$1:F1,OFFSET(Sheet7!$B$2:$B$13,0,MATCH(F$1,Sheet7!$B$1:$F$1,0)-1))+(Sheet7!$E$2:$E$13<>B!$A$1))*1,0,0),0))

Then INDEX Match off of the helper column.

 

[AhoyNC]

Another possible option with formulas.
If you have Excel 365 with the FILTER function take a look at the formula in E2 other wise try the formula in A2 and copy down and across as needed.
The formula need to be put in each spreadsheet.

Spreadsheet named B

Book1
	A	B	C	D	E	F	G
1	Name	amount	date		Name	amount	date
2	John	100	11/18/2020		John	100	11/18/2020
B

Cell Formulas
Range	Formula
A2:C2	A2	=IFERROR(INDEX(A!$A$2:$C$4,AGGREGATE(15,6,(ROW(A!$A$2:$A$4)-ROW(A!$A$2)+1)/(A!$D$2:$D$4="B"),ROWS($A$1:A1)),COLUMNS($A$1:A1)),"")
E2	E2	=FILTER(A!$A$2:$C$4,A!$D$2:$D$4="B","")

Spreadsheet A

Book1
	A	B	C	D
1	Name	amount	date	spreadsheet
2	John	100	11/18/2020	B
3	Bob	100	11/17/2020	C
4	Steve	100	11/16/2020	D
A

 

[JTL9161]

Thank you for your input! I will see if this works for me.

 

[AhoyNC]

JTL9161 - It would help if you would update your profile so we know which version of Excel you are using.