I am have a spreadsheet which i am trying to use like a gantt chart (long story why the company doesn't want to use project)
Anyway, i am trying to make it so that when i add a week number to one cell it will automatically highlight and have information from another in the corresponding place on the chart.
[TABLE="width: 1323"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [TABLE="width: 1323"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Current week[/TD]
[TD="colspan: 17"]2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Shop floor[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project No[/TD]
[TD]Start Wk[/TD]
[TD]Finish Wk[/TD]
[TD]Finish Yr[/TD]
[TD]Hrs[/TD]
[TD]Hrs/wk[/TD]
[TD]36[/TD]
[TD]37[/TD]
[TD]38[/TD]
[TD]39[/TD]
[TD]40[/TD]
[TD]41[/TD]
[TD]42[/TD]
[TD]43[/TD]
[TD]44[/TD]
[TD]45[/TD]
[TD]46[/TD]
[TD]47[/TD]
[TD]48[/TD]
[TD]49[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]OSTs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PG-2xxxxx[/TD]
[TD]36[/TD]
[TD]51[/TD]
[TD]2017[/TD]
[TD]2893[/TD]
[TD]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PG-3xxxxxx[/TD]
[TD]46[/TD]
[TD]8[/TD]
[TD]2018[/TD]
[TD]576[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]PG-xxxxxx[/TD]
[TD]4[/TD]
[TD]32[/TD]
[TD]2018[/TD]
[TD]5766[/TD]
[TD]199[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PG-xxxxxx[/TD]
[TD]18[/TD]
[TD]34[/TD]
[TD]2018[/TD]
[TD]3000[/TD]
[TD]176[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PG-xxxxxx[/TD]
[TD]10[/TD]
[TD]22[/TD]
[TD]2018[/TD]
[TD]2500[/TD]
[TD]192[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PG-xxxxxx[/TD]
[TD]32[/TD]
[TD]46[/TD]
[TD]2018[/TD]
[TD]3800[/TD]
[TD]253[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
From the table above as you can see wk start 36, if i change that to 40 i want the items in wk 36 in the chart to move to wk 40... but like it would if you change the date in a gantt chart in project to would move the entire project line.
Can this be done with conditionally formatting??
i have ran out of idea and my brain hurts 1 help
Anyway, i am trying to make it so that when i add a week number to one cell it will automatically highlight and have information from another in the corresponding place on the chart.
[TABLE="width: 1323"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [TABLE="width: 1323"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Current week[/TD]
[TD="colspan: 17"]2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Shop floor[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project No[/TD]
[TD]Start Wk[/TD]
[TD]Finish Wk[/TD]
[TD]Finish Yr[/TD]
[TD]Hrs[/TD]
[TD]Hrs/wk[/TD]
[TD]36[/TD]
[TD]37[/TD]
[TD]38[/TD]
[TD]39[/TD]
[TD]40[/TD]
[TD]41[/TD]
[TD]42[/TD]
[TD]43[/TD]
[TD]44[/TD]
[TD]45[/TD]
[TD]46[/TD]
[TD]47[/TD]
[TD]48[/TD]
[TD]49[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]OSTs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PG-2xxxxx[/TD]
[TD]36[/TD]
[TD]51[/TD]
[TD]2017[/TD]
[TD]2893[/TD]
[TD]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD="align: right"]181[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PG-3xxxxxx[/TD]
[TD]46[/TD]
[TD]8[/TD]
[TD]2018[/TD]
[TD]576[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]PG-xxxxxx[/TD]
[TD]4[/TD]
[TD]32[/TD]
[TD]2018[/TD]
[TD]5766[/TD]
[TD]199[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PG-xxxxxx[/TD]
[TD]18[/TD]
[TD]34[/TD]
[TD]2018[/TD]
[TD]3000[/TD]
[TD]176[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PG-xxxxxx[/TD]
[TD]10[/TD]
[TD]22[/TD]
[TD]2018[/TD]
[TD]2500[/TD]
[TD]192[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PG-xxxxxx[/TD]
[TD]32[/TD]
[TD]46[/TD]
[TD]2018[/TD]
[TD]3800[/TD]
[TD]253[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
From the table above as you can see wk start 36, if i change that to 40 i want the items in wk 36 in the chart to move to wk 40... but like it would if you change the date in a gantt chart in project to would move the entire project line.
Can this be done with conditionally formatting??
i have ran out of idea and my brain hurts 1 help