Copying a series of "indirect" formulas

W

wmjenner

I want to copy the following formula down 600 lines:

=if(INDIRECT("Sheet1!$H15")+7<INDIRECT("Sheet1!$M15"),INDIRECT("Sheet1!i15"),0)

With a "normal" formula, row 15 referenced in the formula would cop
down relatively, i.e. the next row's formula would refer to $h16, the
$h17 etc. With the indirect formula, the 15 is absolute and does no
increase relatively. Is there a way to make it do so?

Thanks
 
F

Frank Kabel

Hi
you may use ROW for this. Try
=if(INDIRECT("Sheet1!$H" & ROW(15:15))+7<INDIRECT("Sheet1!$M" &
ROW(15:159),INDIRECT("Sheet1!i"& ROW(15:15)),0)
 
T

Trevor Shuttleworth

Frank

I think the 159 should be 15)

=IF(INDIRECT("Sheet1!$H" & ROW(15:15))+7<INDIRECT("Sheet1!$M"
&ROW(15:15)),INDIRECT("Sheet1!i"& ROW(15:15)),0)

Regards

Trevor
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Referencing Multiple Worksheets using VLOOKUP and INDIRECT 3
Use of Indirect 3
Copying Indirect formulas 4
Formula to Identify groups of numbers to be added. 4
Indirect function – relative column copy/paste 1
help on indirect() 1
using indirect throughout a sheet 3
Copying Formulas 3

Top