15,744,304 members
See more:
I am trying to dragdown a formula which is
`=IF(SUM(SecurityLevels!E\$257:E\$261)>0,"3",IF(AND(SUM(SecurityLevels!E\$232:E\$256)>0,SUM(SecurityLevels!E\$2:E\$231)>0),"2",IF(SUM(SecurityLevels!E\$2:E\$231)>0,"1","0")))`

Currently it won't drag down column number to be F, G, H, etc. My current sheet I am doing this in is ReportOwnerQuery. The sheet I'm trying to dragdown from is SecurityLevels. Manually changing the Column letter works but is super inefficient as I have to do it to JF. The Security levels sheet has a list of names in Row 1 starting in Column C and going to JF. Could I make an Indirect that will help with this so I can just drag it down to change column letter? Will be a lot quicker than doing Find and Replace every line.

What I have tried:

Find and Replace every line, it works but is super inefficient.
Tried a Indirect() but I can't get it working quite properly. I got it working in the SecurityLevels sheet for part of the complex calculation I was doing. Here is one cells example:
`=COUNTIF(INDIRECT("'ReportOwnerQuery'!AD"&COLUMN()-1),"*"&\$A260&"*")`

Working manual solution: A solution from copy pasting would be what I am using until I figure out a formulated way was to switch to copy the formula to a an empty row, drag it to the right as much as I needed the go into Formula view mode (CTRL+`). Copy and paste the formulas with columns different into Notepad then paste where I wanted to originally drag down.
Posted
Updated 24-Apr-19 9:33am
v3

## Solution 1

`=IF(SUM(INDEX(SecurityLevels!E\$257:JF\$261,0,ROW()))>0,"3",IF(AND(SUM(INDEX(SecurityLevels!E\$232:JF\$256,0,ROW()))>0,SUM(INDEX(SecurityLevels!E\$2:JF\$231,0,ROW()))>0),"2",IF(SUM(INDEX(SecurityLevels!E\$2:JF\$231,0,ROW()))>0,"1","0")))`

Is a conditional solution.

v2