Show Values in multiple cells from one single cell reference

[emisa]

Hi,

Thanks for all your help in advance.


Is there any way I can populate multiple cells in a given row with specific values, according to the value from one single cell? I'm trying to simplify my schedule as it is recurring in 4-week cycle.

	A	B	C	D	E	F	G	H
1		MON	TUE	WED	THU	FRI	SAT	SUN
2	W1	MORNING	NIGHT	NOCTE	SHORT	NOCTE	NOCTE	OFF
3	W2	NOCTE	DOUBLE	MORNING	NIGHT	NIGHT	OFF	NIGHT
4	W3	DOUBLE	OFF	OFF	DOUBLE	NIGHT	NIGHT	OFF
5	W4	OFF	MORING	MORNING	MORNING	MORNIG	OFF	NIGHT
6	W?

<tbody>
</tbody>

From the table, week starts on mondays, then shifts are fix according to week. I was just thinking if in A6 (W?) I put w2, will B6-H6 copy or mirror the values from B3-H3 or W1 and mirror values from B2-H2 and so on?

Heaps thanks!!!

 

[FormR]

Something like this maybe, the formula in B6 can be copied across.

Excel 2012
	A	B	C	D	E	F	G	H
1		MON	TUE	WED	THU	FRI	SAT	SUN
2	W1	MORNING	NIGHT	NOCTE	SHORT	NOCTE	NOCTE	OFF
3	W2	NOCTE	DOUBLE	MORNING	NIGHT	NIGHT	OFF	NIGHT
4	W3	DOUBLE	OFF	OFF	DOUBLE	NIGHT	NIGHT	OFF
5	W4	OFF	MORING	MORNING	MORNING	MORNIG	OFF	NIGHT
6	W2	NOCTE	DOUBLE	MORNING	NIGHT	NIGHT	OFF	NIGHT
Sheet1

Cell Formulas
Range	Formula
B6	=VLOOKUP($A6,$A$2:$H$5,COLUMNS($B6:B6)+1)

 

[emisa]

Hi FormR, your suggestion did work. However, while Im trying to insert additional columns, (2-3 blank colums or cells in between A to H, in returns the value to zero. Im not sure what went wrong thereafter.

 

[FormR]

Can you post an example of your new layout and the expected results?

 

[emisa]

this is how it looks like now

	A				B		C		D		E		F		G		H
1					MON		TUE		WED		THU		FRI		SAT		SUN
2	W1				MORNING		NIGHT		NOCTE		SHORT		NOCTE		NOCTE		OFF
3	W2				NOCTE		DOUBLE		MORNING		NIGHT		NIGHT		OFF		NIGHT
4	W3				DOUBLE		OFF		OFF		DOUBLE		NIGHT		NIGHT		OFF
5	W4				OFF		MORING		MORNING		MORNING		MORNIG		OFF		NIGHT
6	W2				0		0		0		NIGHT		0		0		NIGHT

<tbody>
</tbody>

 

[FormR]

This version should handle column inserts more robustly.

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1				MON		TUE		WED		THU		FRI		SAT		SUN
2	W1			MORNING		NIGHT		NOCTE		SHORT		NOCTE		NOCTE		OFF
3	W2			NOCTE		DOUBLE		MORNING		NIGHT		NIGHT		OFF		NIGHT
4	W3			DOUBLE		OFF		OFF		DOUBLE		NIGHT		NIGHT		OFF
5	W4			OFF		MORING		MORNING		MORNING		MORNIG		OFF		NIGHT
6	W2			NOCTE		DOUBLE		MORNING		NIGHT		NIGHT		OFF		NIGHT
Sheet1

Cell Formulas
Range	Formula
D6	=VLOOKUP($A6,$A$2:$P$5,COLUMNS($A6:D6))