Adjusting Row or Column Reference in Formula

[edavies]

Earlier today I posted a query regarding my complex spreadsheet and met
with great success on the response, which encourages me to post another
question.

To recap: the spreadsheet consists of 40 worksheets with the majority
having about 7K cells. There is one worksheet that is a master summary
for the others. It is made up of about 50 arrays (23r x 35c) pulling
data from 23 of the other worksheets.

It would make formatting this master summary spreadsheet easier if I
could put a command in the formula to go to the next line in the source
data.

Here's an example: In the first array of the summary sheet, data is
pulled from cell D3 in Sheet 1 and so on through cell D3 in Sheet 23.
If I copy and paste to create the next array, the formulas change to
pull data from cell D26 in Sheet 1 through cell D26 in Sheet 23. This
requires me to manually change each cell's formula to reference cell D4
for each of the 23 summary cells. What I want ideally is a way to
compose the formula so that the cells in each array move to the next
line in the source data automatically.

Sometimes I create spreadsheets where I have a similar need to adjust
the column reference. So if there is a similar solution for columns I
am interested in that as well.

Thanks.

 

[Ken Johnson]

Hi,
I could be wrong, but it looks to me like you're trying to filldown a
formula so that as it goes down the column it cycles through the row
numbers 3 to 23. ie after it reaches 23 it goes back to 3 then on to 23
again. This type of pattern is achieved using the MOD function, and the
way the formula is written depends on the row number of the topmost
cell using the formula. The following example is when the topmost cell
using the formula is in row 5...

=MOD(ROW()-5,21)+3

If you paste this formula into any cell in row 5 then fill it down as
far as you like you should see the pattern 3,4,5,...23,3,4,5...23 etc.
To use it in a different starting row just change the 5 to the number
of your starting row.


I think you should be able to incorporate this formula into your
formula which is generating your summary sheet arrays.

Hope this helps.

Ken Johnson