Reference Current Non Blank Cell - Monthly Calendar

[FERG NATION]

I am trying to reference daily sales sheet to input current total on my monthly sales report
(spreadsheet included)

as we track sales through out the month the total changes daily
I would like the input to change on the monthly sales sheet to reflect the most current non blank cell

SALES SHEET

							SALES TO DATE
	1-Sep​	2-Sep​	3-Sep​	4-Sep​	5-Sep​	6-Sep​	7-Sep​	8-Sep​	9-Sep​	10-Sep​	11-Sep​	12-Sep​	13-Sep​	14-Sep​	15-Sep​	16-Sep​	17-Sep​	18-Sep​	19-Sep​	20-Sep​	21-Sep​	22-Sep​	23-Sep​	24-Sep​	25-Sep​	26-Sep​	27-Sep​	28-Sep​	29-Sep​	30-Sep​
DAVE	$ 1.00	$ 2.00	$ 3.00	$ 4.00	$ 5.00	$ 6.00	$ 7.00	$ 8.00	$ 9.00	$ 10.00	$ 11.00	$ 12.00	$ 13.00	$ 14.00	$ 15.00	$ 16.00	$ 17.00	$ 18.00	$ 19.00	$ 20.00	$ 21.00	$ 22.00	$ 23.00	$ 24.00	$ 25.00	$ 26.00	$ 27.00
JIM	$ 2.00	$ 2.00	$ 2.00	$ 2.00	$ 2.00	$ 3.00	$ 4.00	$ 5.00	$ 6.00	$ 7.00	$ 7.00	$ 7.00	$ 7.00	$ 7.00	$ 7.00	$ 7.00	$ 7.00	$ 7.00	$ 8.00	$ 9.00	$ 10.00	$ 11.00	$ 12.00	$ 13.00	$ 14.00	$ 15.00	$ 16.00
STEVE	$ 3.00	$ 4.00	$ 5.00	$ 6.00	$ 7.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 9.00	$ 10.00	$ 11.00	$ 12.00	$ 13.00	$ 14.00	$ 15.00	$ 16.00	$ 17.00	$ 18.00	$ 19.00	$ 20.00
SUZY	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 4.00	$ 5.00	$ 6.00	$ 7.00	$ 8.00	$ 9.00	$ 10.00

MONTHLY TOTAL SHEET

	MONTHLY TOTAL
DAVE
JIM
STEVE
SUZY

 

[jasonb75]

some of them are copying duplicate cells

Do some names appear in more than 1 row in your real Sales sheet? That is something not allowed for in the formula.

Can you upload your test sheet to one of the file sharing sites (dropbox or similar) and post a link to it in the forum?

 

[FERG NATION]

Do some names appear in more than 1 row in your real Sales sheet? That is something not allowed for in the formula.

Can you upload your test sheet to one of the file sharing sites (dropbox or similar) and post a link to it in the forum?

TESTSALES.xlsx

Shared with Dropbox

www.dropbox.com

 

[jasonb75]

Looks like the problem is being caused by inconsistent data, some of the names have a space in front of the $ symbol on one sheet but not on the other.

The inconsistency appears to go both ways making it practically impossible to allow for in a formula if the list must be compiled manually, if you simply need to pull all records then you could use something like this to pull the names from the other sheet.

TESTSALES.xlsx
	C	D	E
26	ALQ$		$9,489.60
27	AMA $		$16,636.80
28	BLT $		$10,371.08
29	FRG $		$17,813.40
30	JJM$		$1,500.00
31	CLOSER 6 $		$1,100.00
32	CLOSER 7$		$12,500.00
33	CLOSER 8 $		$13,569.00
34	CLOSER 9 $		$14,200.00
35	CLOSER 10$		$2,200.00
36	0		$500.00
37	ODJ $		$1,714.68
38	GZZ$		$3,050.15
2022 MONTH END

Cell Formulas
Range	Formula
C26:C38	C26	=UNIQUE('SEPT 2022'!B31:B43)
E26:E38	E26	=LOOKUP(1E+100,FILTER('SEPT 2022'!$C$31:$Y$43,'SEPT 2022'!$B$31:$B$43=C26,""))
Dynamic array formulas.

 

[FERG NATION]

ty for all you help fixing the title inconsistency it worked