Formula copying problem

[accountingblm]

I have a spreadsheet that has a tab for ever vendor then one main tab for a summary of payments to the corresponding vendors. The spreadsheet is laid out with the company name on the left then the payment go out to the right on a linear basis. The payments are scheduled every week but there is an empty space between weeks because one of the vendors is paid on an entirely different day of the week. The formula I use to pick up the payment amount from the vendors corresponding sheet looks like this (='Advanced Pain Mgmt. Cntr.'!G17) minus the parentheses. On the actual vendor sheet where the payment is picked up from the payments run from top to bottom one right after the next, so the next one i would need for the summary sheet would be G18,G19 and so on. But i think the space in between payment weeks on the summary sheet combined with the vendor sheet running vertically is throwing off the formula when i try and copy it down the line. I end up with I17, then k17, then m17. My problem is I need to do this for around forty or so vendors and make sure that it picks up all the payment for each for the next year, so manually entering the formula is out of the question. Any help would be great as i am at a lose.

 

[Luke M]

Since you're trying to make a formula that is copied horizontally to change a vertical reference, we'll need to manipulate the way the formula changes. We could do this with the INDIRECT function so we can use a function to get the correct row, and combine it with the COLUMN function so the calculated row will change as we copy horizontally. If your first formula was to reference G17, the starting formula would be:


=INDIRECT("'Advanced Pain Mgmt. Cntr.'!G"&16+INT(COLUMN(B1)/2))


If it it now G17 and some row x, change the 16 in the formula to x-1.

The formula will increment which row it looks at 1 row for every 2 columns (which, with all your spaces, is what I think you want.)