Offset data columns using data from another field

[creddy]

I'm trying to offset the data in the period columns base on the number of periods in the Transit Time column.

For example in the first row below, Period 1 would be 0 and a new Period 16 column needs to be created so that the data is shifted by 1 column

and  in the second row, Period 1 & 2 would be 0 and new Period 16 and 17 columns need to be created so that the data is shifted by 2 columns.

Any help with this would be greatly appreciated.

Thanks in advance

[danrh · Accepted solution]

Try this out:

By using Transpose first, you can change the column names instead of trying to change where the data is.  Then when you Cross Tab back to the original format, it shifts the data over the number of columns called out in your Transit Time column.

Hope it helps!

[creddy]

Didnt think of it this way.

This is so much simpler than what I was thinking( to Transpose and use the Multi Row formula)

Thanks a lot.