Vertical & Horizontal Offset / match row and column

**David Brown** · 12-29-2010, 07:12 AM

Hi

New Explanation associated with file v1.1 attached.

I need to have a formula which links to an existing block of numbers which auto-populates a similar block below the source block, but which correctly positions the start of the number block in the correct vertical (column) and row (horizontal) position depending upon the start date of the business which can change.

I have used years 2013 and years 2018 as examples of when the business start date needs to trigger the auto-population of the schedules. I have encircled the numbers and pointed to exactly where I need the formula link to commence. In years prior to the start date, the cells need to return zero.

Thanking you in advance.
Best / david

**DonkeyOte** · 12-29-2010, 07:26 AM

David, I confess I'm not entirely sure I follow the requirements.

Are you saying the 2nd matrix should be an adjusted version of the 1st matrix based on first number in row 5 or are the values (%) in the 2nd matrix to be calculated in some way ?

**e4excel** · 12-29-2010, 09:39 AM

Hi David,

I was just searching for something on CountIFS when I came across this post and found this Amazing File with Graphs..

Honestly, I didnt understand a thing in the file and also the requirement but really found it very good..

Expecially the percentage colouring..JUSt so different Qute imaginative...Eh!

REgards
e4excel

**David Brown** · 12-29-2010, 02:56 PM

Hi - I have added a new file with better explanation to the existing thread. I have colour circled the number blocks and pointed to exactly where the numbers needs to be auto-populated to.
Thanks in advance/David

**DonkeyOte** · 12-30-2010, 04:14 AM

David, first thing I would do would be to return the start year in another cell for ease of reference:

Please Login or Register  to view this content.

with the above in place I'd then use the below formula to generate the 2nd matrix

Please Login or Register  to view this content.

attached is working example of the above which should "translate" per your locale configuration (delimiters etc...)

**David Brown** · 12-30-2010, 12:43 PM

This is fantastic! Thanks very much. So this solution is meaningful to others, I have explained it more fully below...

This schedule is applicable to any business which is deploying a service, such as telecoms infrastructure in annual increments and there is a customer uptake curve for each annual increment. For example, an ISP might be deploying a global VPN which to work requires the deployment of POP's globally. If the deployment of some of the POP's is delayed, it is possible to shift the typical service uptake curve to the date when that POP comes on line.

Thanks/David