# Show Values in multiple cells from one single cell reference

#### emisa

##### New Member
Hi,

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!!!

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Something like this maybe, the formula in B6 can be copied across.

Excel 2012
ABCDEFGH
1MONTUEWEDTHUFRISATSUN
2W1MORNINGNIGHTNOCTESHORTNOCTENOCTEOFF
3W2NOCTEDOUBLEMORNINGNIGHTNIGHTOFFNIGHT
4W3DOUBLEOFFOFFDOUBLENIGHTNIGHTOFF
5W4OFFMORINGMORNINGMORNINGMORNIGOFFNIGHT
6W2NOCTEDOUBLEMORNINGNIGHTNIGHTOFFNIGHT
Sheet1
Cell Formulas
RangeFormula
B6=VLOOKUP(\$A6,\$A\$2:\$H\$5,COLUMNS(\$B6:B6)+1)

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.

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

this is how it looks like now
ABCDEFGH
1 MON TUE WED THU FRI SAT SUN
2W1MORNINGNIGHTNOCTESHORTNOCTENOCTEOFF
3W2NOCTEDOUBLEMORNINGNIGHTNIGHTOFFNIGHT
4W3DOUBLEOFFOFFDOUBLENIGHTNIGHTOFF
5W4OFFMORINGMORNINGMORNINGMORNIGOFFNIGHT
6W2000NIGHT00NIGHT

<tbody>
</tbody>

This version should handle column inserts more robustly.

Excel 2012
ABCDEFGHIJKLMNOP
1MONTUEWEDTHUFRISATSUN
2W1MORNINGNIGHTNOCTESHORTNOCTENOCTEOFF
3W2NOCTEDOUBLEMORNINGNIGHTNIGHTOFFNIGHT
4W3DOUBLEOFFOFFDOUBLENIGHTNIGHTOFF
5W4OFFMORINGMORNINGMORNINGMORNIGOFFNIGHT
6W2NOCTEDOUBLEMORNINGNIGHTNIGHTOFFNIGHT
Sheet1
Cell Formulas
RangeFormula
D6=VLOOKUP(\$A6,\$A\$2:\$P\$5,COLUMNS(\$A6:D6))

Replies
1
Views
399
Replies
5
Views
226
Replies
7
Views
196
Replies
4
Views
282
Replies
6
Views
294

1,207,421
Messages
6,078,436
Members
446,337
Latest member
nrijkers

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back