line graphs

[jbrown021286]

TECH HOURS WORKSHEET 4.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1			Current month						Monthly unapplyed labor
2			Average Express Delta Per Tech	Dailey Express deficit/ surplus	Dailey unapplyed labor from express	Total unapplied labor month to date	unapplyed labor trend projection		January	February	March	April	May	June	July	August	September	October	November	December
3			-4.8	-38.7	$ (604.69)	$ (10,532.81)	$ (14,628.91)		$(11,321.88)
4
Express

Cell Formulas
Range	Formula
C2:G3	C2	='Delta Breakbown'!V1:Z1
I3	I3	=Jan!BK2
Dynamic array formulas.

i am wanting to have a line graph that shows the monthly data points as they get entered through the year and the projection in G3 as it updates Dailey shown as the current month. is this possible or can the graph functions only be used in a more static application.

 

[awoohaw]

you have references to other sheets (Delta Breakdown and Jan). I figure Delta Breakdown is header data that have no bearing on the Line Graph.
But, is the value you expect in J3 eventually going to have a value from a sheet called February (in Feb!BK2). If so, is that worksheet created?
If all the months worksheets are already created this could work. (And it could work if they aren't but you'll get a a lot of messages to open an unopened workbook).

=IF(TEXT(TODAY(),"mmmm")=J$2,$G$3,Feb!BK2

)

 

[jbrown021286]

you have references to other sheets (Delta Breakdown and Jan). I figure Delta Breakdown is header data that have no bearing on the Line Graph.
But, is the value you expect in J3 eventually going to have a value from a sheet called February (in Feb!BK2). If so, is that worksheet created?
If all the months worksheets are already created this could work. (And it could work if they aren't but you'll get a a lot of messages to open an unopened workbook).

=IF(TEXT(TODAY(),"mmmm")=J$2,$G$3,Feb!BK2

)

you are correct in your assumptions. at the end of every month the worksheet Data breakdown will be copied into worksheets that are already created and the refenced cell will be the same across all worksheets Jan!BK2, Feb!BK2, Mar!BK2 and so on. the data breakdown sheet will then be cleared and start logging the new month's data to then be referenced in F3. can you clarify where I need to plug your formula in?

 

[awoohaw]

Cells I2 to Z2 need to have a date value (the first of each month for the year). Format the cell "mmmm". You could do this with one array formula in cell I2:

=Date(2024,Sequence(,12),1)

Cells I3 to Z3

=If(Month(Today())=Month(I2),$G3, IFERROR(INDIRECT(Text(I2,"mmm")&"!BK2"),""))

So, Ineeded to create a Jan sheet:

Book1
	BJ	BK	BL
1		Value
2		15
3
Jan

Then here is the roll up sheet:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1			Current month						Monthly unapplyed labor
2			twzt	twzt	twzt	twzt	twzt		January	February	March	April	May	June	July	August	September	October	November	December
3			13	14	13	11	13		15	13
4
Sheet1

Cell Formulas
Range	Formula
I2:T2	I2	=DATE(2024,SEQUENCE(1,12),1)
C3:G3	C3	=RANDARRAY(1,5,11,15,1)
I3:T3	I3	=IF(MONTH(TODAY())=MONTH(I2),$G3, IFERROR(INDIRECT(TEXT(I2,"mmm")&"!BK2"),""))
Dynamic array formulas.

 

[jbrown021286]

Cells I2 to Z2 need to have a date value (the first of each month for the year). Format the cell "mmmm". You could do this with one array formula in cell I2:

=Date(2024,Sequence(,12),1)

Cells I3 to Z3

=If(Month(Today())=Month(I2),$G3, IFERROR(INDIRECT(Text(I2,"mmm")&"!BK2"),""))

So, Ineeded to create a Jan sheet:

Book1
	BJ	BK	BL
1		Value
2		15
3
Jan

Then here is the roll up sheet:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1			Current month						Monthly unapplyed labor
2			twzt	twzt	twzt	twzt	twzt		January	February	March	April	May	June	July	August	September	October	November	December
3			13	14	13	11	13		15	13
4
Sheet1

Cell Formulas
Range	Formula
I2:T2	I2	=DATE(2024,SEQUENCE(1,12),1)
C3:G3	C3	=RANDARRAY(1,5,11,15,1)
I3:T3	I3	=IF(MONTH(TODAY())=MONTH(I2),$G3, IFERROR(INDIRECT(TEXT(I2,"mmm")&"!BK2"),""))
Dynamic array formulas.

Thanks for the help that got it

 

[awoohaw]

Happy to help.

Best Wishes!