Select last number to the far right of a row.

[nparsons75]

Is there a formula that will allow me to select the far most value to the right of a row? Also, the value should only be selected if it appears under the day of the month - 1. (yesterday)

For example, in the top table below the value to the far right = 10 This will be returned in to my result cell as the value does sit under yesterdays day of the month.

In the second example the cell = blank on yesterdays day of month and therefore the result in my result cell will = 0.

I hope this makes sense.....

Book10
	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	AF
6	Result	10
7
8	Day of month	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
9	Value	70	61	91	21			73	72	67	116	61			88	103	100	85	69		5	92	107	100	76	62	12	10
Sheet1

Book10
	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	AF
14	Result	0
15
16	Day of month	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
17	Value	70	61	91	21			73	72	67	116	61			88	103	100	85	69		5	92	107	100	76	62	12
Sheet1

 

[Fluff]

In your first example could you have values after the 10 in AB9 & if so should it still return 10 or should it be blank?

 

[nparsons75]

In your first example could you have values after the 10 in AB9 & if so should it still return 10 or should it be blank?

Yes, as each day passes a new value is added on the corresponding day. The value to be added will always be the day after. So, to fill in the value under 27 then that will be complete today. For tomorrows value, this will be updated Wednesday. We do however have weekends and nothing will be added for the saturday or sunday until the monday.

 

[Fluff]

Ok how about

=HLOOKUP(DAY(TODAY()-1),B8:AF9,2,0)

 

[nparsons75]

Ok how about

=HLOOKUP(DAY(TODAY()-1),B8:AF9,2,0)

It didnt seem to work, no result returned. One thing I should have added is that there are multiple values underneath the day number.... The values will all be different, for the purposes of this I just copy / pasted...

Also, the day number, what format does that cell have to be?

Book10
	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	AF
6	Result	10
7
8	Day of month	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
9	Value1	70	61	91	21			73	72	67	116	61			88	103	100	85	69		5	92	107	100	76	62	12	10
10	Value2	70	61	91	21			73	72	67	116	61			88	103	100	85	69		5	92	107	100	76	62	12	10
11	Value3	70	61	91	21			73	72	67	116	61			88	103	100	85	69		5	92	107	100	76	62	12	10
12	Value4	70	61	91	21			73	72	67	116	61			88	103	100	85	69		5	92	107	100	76	62	12	10
Sheet1

 

[Fluff]

Is row 8 numbers or text?

 

[nparsons75]

Is row 8 numbers or text?

its a formula...

Daily production tracker kpi display 2021 v3.1 MASTER.xlsm
	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	AF	AG	AH	AI	AJ
30	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu
31	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	1
JUNE

Cell Formulas
Range	Formula
F30	F30	=E4
G30:AJ31	G30	=F30+1
F31	F31	=E4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G30:H30	Cell	contains an error	text	NO
AI47:AI50,AI30:AI45	Expression	=COUNTIF(holidays,F$30)>0	text	NO
K32:L32,R32:S32,R37:S38,R40:S45,R39,R33,K37:Q45,O36,Q36:R36,F30:AC31,R50:S50,R49,R47:S48,F47:Q50,Y37:AC37,Y36,AC36,Y39:AC45,Y38,AA38:AC38,T47:AC50,F46:AC46,K33:K36,Y32:AC35,R34:S35,F32:J45,M32:Q35,T32:X45,L34:L35,AD30:AJ50	Expression	=WEEKDAY(F$30,2)>5	text	NO
K32:L32,K37:L45,K47:L50,K33:K36,R32:S32,R33,R37:S38,R36,R40:S45,R39,R50:S50,R49,R47:S48,R34:S35,Y47:Z50,Y37:Z37,Y36,Y39:Z45,Y38,Y32:Z35,AA33,AA34:AB34,F30:AJ31,AE47:AJ50,AE32:AJ45	Expression	=F$30=$E$5	text	YES
K32:L32,K37:L45,K47:L50,K33:K36,R32:S32,R33,R37:S38,R36,R40:S45,R39,R50:S50,R49,R47:S48,R34:S35,Y47:Z50,Y37:Z37,Y36,Y39:Z45,Y38,Y32:Z35,AA33,AA34:AB34,F30:AJ31,AE47:AJ50,AE32:AJ45	Expression	=COUNTIF(holidays,F$30)>0	text	NO

 

[Fluff]

Can you post that again showing what you tried for the formula.

 

[nparsons75]

Hi, I have tried to recreate below using my original formulas from my sheet.

Book1
	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	AF	AG
2	Product 1	#N/A
3	Product 2
4	Product 3
5
6	Day	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu
7	Day No	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	1
8	Product 1	70	61	91	21			73	72	67	116	61			88	103	100	85	69		5	92	107	100	76	62	12	10
9	Product 2	24	25	24	17	8		22	18	18	0	0			0	14	18	21	10	10	8	21	21	10	23	17
10	Product 3	22	27	18	17	9	15	28	34	32	33	36	8		34	25	36	29	6		9	34	29	35	8	10	8
Sheet1

Cell Formulas
Range	Formula
C2	C2	=HLOOKUP(DAY(TODAY()-1),C7:AG8,2,0)
D6:AG7	D6	=C6+1
C7	C7	=C6

 

[Fluff]

Ok, row 7 has dates & not numbers so try

=HLOOKUP(TODAY()-1,C7:AG8,2,0)