Data info pulled from 2 sheets

[Maya Bungol]

Hi all,

I need help again. I wanna make sure I'm putting the correct formulas. And if anyone can check it that would be perfect! Something is off with my formula because we already have the invoices but the column "Receipts" still shows "Missing".

If there's another formula to use to avoid kicking me out, that would be perfect.

This is the master sheet, where everything shows all the data from other sheets.

	STATEMENT REPORT
Paid	Diverted From Account Number	Post Date	Tran Date	Diverted From Cardholder Name	Amount	Merchant Name	CC ID	Receipts	Status
	552747****2924	6/30/2022	6/29/2022	CORI BLOXSOM	81.00	SQ *MR. BURRITOS	CCCB01	Paid	C
	552747****2924	6/30/2022	6/29/2022	CORI BLOXSOM	63.00	AMZN Mktp US*M07RB14Q3	CCCB01	Missing	O
	552747****8279	6/30/2022	6/28/2022	CHANCE WEIGHT	21.41	ALBERTSONS #0009	CCCW01	OK	R
p	552747****1574	6/30/2022	6/28/2022	SHILOH KERR	27.28	ALBERTSONS #0043	CCSK01	Paid	C
	552747****2352	6/30/2022	6/28/2022	ARIS BATISTA	9.63	SAFEWAY #1160	CCAB02	Missing	O
	552747****0831	6/30/2022	6/28/2022	TODD OKKEN	21.98	LEWISTOWN TRUE VALUE	CCTO01	Missing	O
	552747****0831	6/30/2022	6/28/2022	TODD OKKEN	42.97	LEWISTOWN TRUE VALUE	CCTO01	Missing	O
p	552747****7075	6/30/2022	6/28/2022	MATT BROSSMAN	18.99	OLD CHICAGO BOZEMAN	CCMB01	Paid	C
p	552747****9460	6/30/2022	6/29/2022	HATTIE REDMON	54.85	THE TROUGH	CCHR01	Paid	C

This is the statement sheet - the data pulled from here is for the Master sheet (columns C to G ). This is where we base all the transactions.

Tran Date	Merchant Name	Amount	Diverted From Account Number	Diverted From Cardholder Name
6/29/2022	SQ *MR. BURRITOS	$81.00	552747****2924	CORI BLOXSOM
6/29/2022	AMZN Mktp US*M07RB14Q3	$63.00	552747****2924	CORI BLOXSOM
6/28/2022	ALBERTSONS #0009	$21.41	552747****8279	CHANCE WEIGHT
6/28/2022	ALBERTSONS #0043	$27.28	552747****1574	SHILOH KERR
6/28/2022	SAFEWAY #1160	$9.63	552747****2352	ARIS BATISTA
6/28/2022	LEWISTOWN TRUE VALUE	$21.98	552747****0831	TODD OKKEN
6/28/2022	LEWISTOWN TRUE VALUE	$42.97	552747****0831	TODD OKKEN
6/28/2022	OLD CHICAGO BOZEMAN	$18.99	552747****7075	MATT BROSSMAN
6/29/2022	THE TROUGH	$54.85	552747****9460	HATTIE REDMON

And this invoice sheet is where we confirm if we received or paid the invoices from the transaction sheet.

Vendor	Vendor Name	Inv Date	Invoice	Amount	Acctg Date	Status
CCAB02	CC ARIS BATISTA x2352	6/27/2022	YOK220627	8.01	7/1/2022	Fully paid
CCAB02	CC ARIS BATISTA x2352	6/1/2022	SAF220601	10.97	6/1/2022	Fully paid
CCAB02	CC ARIS BATISTA x2352	6/14/2022	MOD220614	11.41	7/1/2022	Fully paid
CCAB02	CC ARIS BATISTA x2352	6/13/2022	YOK220613	14.54	7/1/2022	Fully paid
CCCB01	CC CORI BLOXSOM x2924	6/21/2022	RIS220621	71.88	6/21/2022	Fully paid
CCCB01	CC CORI BLOXSOM x2924	6/30/2022	AMA220630	79.99	7/1/2022	Open
CCCB01	CC CORI BLOXSOM x2924	6/29/2022	MRB220622	81.00	6/22/2022	Fully paid
CCCB01	CC CORI BLOXSOM x2924	6/21/2022	CEN220621	83.95	6/21/2022	Fully paid
CCCB01	CC CORI BLOXSOM x2924	6/2/2022	ROS220602	93.31	6/2/2022	Fully paid
CCCB01	CC CORI BLOXSOM x2924	6/2/2022	HAM220602	105.14	6/2/2022	Fully paid
CCCB01	CC CORI BLOXSOM x2924	6/9/2022	MAP220609	145.00	6/9/2022	Fully paid
CCCB01	CC CORI BLOXSOM x2924	6/14/2022	STA220614	163.98	6/14/2022	Fully paid

If any could help. I really appreciate it!

Thank you!

 

[rlv01]

I need help again. I wanna make sure I'm putting the correct formulas.

You use the work "formuals" (plural), but I only see the one formula in Col F.

Something is off with my formula because we already have the invoices but the column "Receipts" still shows "Missing".

The "Receipts" column does not have a formula, so the fact that cell contents is a text value of "Missing" is meaningless to me. Since it is not a formula, it will only change if you type something new in the cell, or if you run a VBA routine (that you have not shared) that writes to that cell.

 

[Maya Bungol]

Hi,

Not sure why all the formulas from each column didn't show up. I tried the mini sheet but my computer keeps "not responding" that's why I only did the tables only.
But anyways these are the formulas from the Master sheet of each column.

A = DATA VALIDATION FOR PAID

B =IF(Statement!K262="","",Statement!K262)

C =IF(Statement!D262="","",Statement!D262)

D =IF(Statement!C262="","",Statement!C262)

E =IF(Statement!L528="","",Statement!L528)

F =IF(Statement!F262="","",Statement!F262)

G =IF(Statement!E262="","",Statement!E262)

H =IFERROR(VLOOKUP(F271,KEY!$A$2:$E$151,4,FALSE),"")

I =IF(AND([@Amount]="",[@[Diverted From Account Number]]=""),"",IF(COUNTIFS(TS!A:A,[@[CC ID]],TS!C:C,[@[Tran Date]],TS!E:E,[@Amount],TS!G:G,"Fully paid")>=1,"Paid",IF(COUNTIFS(TS!A:A,[@[CC ID]],TS!C:C,[@[Tran Date]],TS!E:E,[@Amount])=1,"OK","Missing")))

J =IF($J271="Missing","O",IF($J271="Ok","R",IF($J271="Paid","C", "")))

 

[rlv01]

A couple of comments:

1. When using XL2BB, it is important to use the "Mini Sheet" button with the "Cell Formulas" checkbox ticked, not the "Table only" button. This is so that formulas are included along with column and row information. I recommend you go back an re-post your data using "Mini Sheet" to show all the formulas.

2. I know that when you are fully involved in a problem, it is easy to forget that other people are not, so take a moment to consider how others might interpret your post. For example, your last post references a several formulas by column letter (A,B,C), but none of the tables you have posted include any column letters. I have no idea if "Vendor" is column A or column Z.

So instead of this:

Vendor	Vendor Name	Inv Date	Invoice	Amount	Acctg Date	Status
CCAB02	CC ARIS BATISTA x2352	6/27/2022	YOK220627	8.01	7/1/2022	Fully paid
CCAB02	CC ARIS BATISTA x2352	6/1/2022	SAF220601	10.97	6/1/2022	Fully paid
CCAB02	CC ARIS BATISTA x2352	6/14/2022	MOD220614	11.41	7/1/2022	Fully paid
CCAB02	CC ARIS BATISTA x2352	6/13/2022	YOK220613	14.54	7/1/2022	Fully paid
CCCB01	CC CORI BLOXSOM x2924	6/21/2022	RIS220621	71.88	6/21/2022	Fully paid
CCCB01	CC CORI BLOXSOM x2924	6/30/2022	AMA220630	79.99	7/1/2022	Open
CCCB01	CC CORI BLOXSOM x2924	6/29/2022	MRB220622	81	6/22/2022	Fully paid
CCCB01	CC CORI BLOXSOM x2924	6/21/2022	CEN220621	83.95	6/21/2022	Fully paid
CCCB01	CC CORI BLOXSOM x2924	6/2/2022	ROS220602	93.31	6/2/2022	Fully paid
CCCB01	CC CORI BLOXSOM x2924	6/2/2022	HAM220602	105.14	6/2/2022	Fully paid
CCCB01	CC CORI BLOXSOM x2924	6/9/2022	MAP220609	145	6/9/2022	Fully paid
CCCB01	CC CORI BLOXSOM x2924	6/14/2022	STA220614	163.98	6/14/2022	Fully paid

Post This:

Book1
	A	B	C	D	E	F	G
1	Vendor	Vendor Name	Inv Date	Invoice	Amount	Acctg Date	Status
21	CCAB02	CC ARIS BATISTA x2352	6/27/2022	YOK220627	8.01	7/1/2022	Fully paid
22	CCAB02	CC ARIS BATISTA x2352	6/1/2022	SAF220601	10.97	6/1/2022	Fully paid
23	CCAB02	CC ARIS BATISTA x2352	6/14/2022	MOD220614	11.41	7/1/2022	Fully paid
24	CCAB02	CC ARIS BATISTA x2352	6/13/2022	YOK220613	14.54	7/1/2022	Fully paid
143	CCCB01	CC CORI BLOXSOM x2924	6/21/2022	RIS220621	71.88	6/21/2022	Fully paid
144	CCCB01	CC CORI BLOXSOM x2924	6/30/2022	AMA220630	79.99	7/1/2022	Open
145	CCCB01	CC CORI BLOXSOM x2924	6/29/2022	MRB220622	81	6/22/2022	Fully paid
146	CCCB01	CC CORI BLOXSOM x2924	6/21/2022	CEN220621	83.95	6/21/2022	Fully paid
147	CCCB01	CC CORI BLOXSOM x2924	6/2/2022	ROS220602	93.31	6/2/2022	Fully paid
148	CCCB01	CC CORI BLOXSOM x2924	6/2/2022	HAM220602	105.14	6/2/2022	Fully paid
149	CCCB01	CC CORI BLOXSOM x2924	6/9/2022	MAP220609	145	6/9/2022	Fully paid
150	CCCB01	CC CORI BLOXSOM x2924	6/14/2022	STA220614	163.98	6/14/2022	Fully paid
Sheet2