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]

Is this what you're trying to do?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1								SALES TO DATE
2
3		01-Sep	02-Sep	03-Sep	04-Sep	05-Sep	06-Sep	07-Sep	08-Sep	09-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
4	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
5	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
6	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
7	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
8
9
10
11		MONTHLY TOTAL
12	DAVE	27
13	JIM	16
14	STEVE	20
15	SUZY	10
16
Sheet4

Cell Formulas
Range	Formula
B12:B15	B12	=LOOKUP(1E+100,INDEX($B$4:$AB$7,MATCH(A12,$A$4:$A$7,0),0))

 

[FERG NATION]

THIS IS TWO DIFFERENT SHEETS, SAME WORKBOOK

 

[jasonb75]

It works the same with two sheets as it does with one, you just need to edit the sheet names and cell ranges to match your actual data.

 

[FERG NATION]

It works the same with two sheets as it does with one, you just need to edit the sheet names and cell ranges to match your actual data.

SO IF THERE IS ONLY DATA ON 9/1 THAT WILL GIVE ME THE CORRECT INFO ON MONTHLY SHEET
EVERY DAY NEW INFORMATION IS ENTERED AND I NEED THE NUMBER TO MATCH THE MOST RECENT INFO

 

[jasonb75]

There is no need for replies to be in capitals. If you continue to type your replies with your caps lock on then I will not respond.

If you set up the formula the same way that I have in the example then it will take the name from the small table and look for the matching row in the big table. It will then look at the numbers in that row and give you the last one it finds (looking from left to right).

 

[FERG NATION]

There is no need for replies to be in capitals. If you continue to type your replies with your caps lock on then I will not respond.

If you set up the formula the same way that I have in the example then it will take the name from the small table and look for the matching row in the big table. It will then look at the numbers in that row and give you the last one it finds (looking from left to right).

sorry about caps my job makes us fill out forms in all caps just didnt switch it
this is great and i appreicate all your help

 

[FERG NATION]

I enter the daily sales report for each sales person ... it is an updated total every day
i am trying to create a monthly tally for all the other information with the spreadsheet
but i need it to reference over the course of the whole month, final sales totals can be up to $50,0000 for the month
my daily sales tracker is one sheet and my monthly tracker is in another sheet all within the same work book
day 1 will be $2000 .... day 2 will be $3000 so on and so on .... so i can not do a sum function rather i need the formula
to look across the whole month and relay the most current number


SALES SHEET (separate from other data, sheet 1, book 1)

							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 (separate from other data, sheet 2, book 1)

	MONTHLY TOTAL
DAVE
JIM
STEVE
SUZY

last time i asked i was provided this formula

=LOOKUP(1E+100,INDEX($B$4:$AB$7,MATCH(A12,$A$4:$A$7,0),0))

tried to make adjustments but i keep getting n/a or error message

does anyone else have any ideas ... mind you the largest # will be $50,000

my chart is just an example

 

[jasonb75]

The same formula with the sheet name added to it. This is the best I, or anyone else, will be able to do for you based on the example and information provided.

It should work although you may need to edit the ranges as detailed below the formula if the positions are not exactly as they appear to be in the example tables that you provided.
There are many things that we can not see form your example that could cause problems with the formula. One common problem is merging cells, they might make it look nicer but the problems that they cause with formulas will far outweigh any aesthetic benefit that you might see.

=LOOKUP(1E+100,INDEX('SALES SHEET'!$B$4:$AB$7,MATCH($A2,'SALES SHEET'!$A$4:$A$7,0),0))

'SALES SHEET'!$B$4:$AB$7 This range needs cover all of the results in the sales sheet (including the blanks for days not yet entered. In the example this is from Dave's sales on 1-Sep to Suzy's sales on 30-Sep

$A2 This needs to look at Dave's name on the Monthly Total Sheet.

'SALES SHEET'!$A$4:$A$7 This needs to look at all of the dates at the top of the Sales sheet.

 

[FERG NATION]

The same formula with the sheet name added to it. This is the best I, or anyone else, will be able to do for you based on the example and information provided.

It should work although you may need to edit the ranges as detailed below the formula if the positions are not exactly as they appear to be in the example tables that you provided.
There are many things that we can not see form your example that could cause problems with the formula. One common problem is merging cells, they might make it look nicer but the problems that they cause with formulas will far outweigh any aesthetic benefit that you might see.

=LOOKUP(1E+100,INDEX('SALES SHEET'!$B$4:$AB$7,MATCH($A2,'SALES SHEET'!$A$4:$A$7,0),0))

'SALES SHEET'!$B$4:$AB$7 This range needs cover all of the results in the sales sheet (including the blanks for days not yet entered. In the example this is from Dave's sales on 1-Sep to Suzy's sales on 30-Sep

$A2 This needs to look at Dave's name on the Monthly Total Sheet.

'SALES SHEET'!$A$4:$A$7 This needs to look at all of the dates at the top of the Sales sheet.

ty for up date ..... some of them are populating some of them are not .... some of them are copying duplicate cells

Can I share the sheet with you to look at? I created a smaller version just focusing on this part