Excel formula question

[mdarsot]

I have a forumla like below

=IF(AND('Publish Tracking'!$E95) Cell# 1
=IF(AND('Publish Tracking'!$E102) cell# 2


The above formula is skipping 7 rows every time. Now if i have to copy this formula down to many rows. it will not skip same number of rows (i.e 7rows) every time. instead it will substitute
=IF(AND('Publish Tracking'!$E95) Cell# 1
=IF(AND('Publish Tracking'!$E102) cell# 2
copy formula down makes it look like below
=IF(AND('Publish Tracking'!$E103) cell# 2


How can i copy the formula and make it skip same number of rows.


thanks for your help.

 

[AnotherHiggins]

First, what purpose does "AND" serve above? Or is that just a clipped portion of a larger formula?

Anyway, you can use INDIRECT to build a string that is then treated as the text of a formula.

For example - IF your first sample formula is in Row 2, then you could use:
[tab]=INDIRECT("'Publish Tracking'!$E" & (ROW() - 1) * 7 + 88)

If you drag that formula down to Row 3, it would return data for 'Publish Tracking'!$E102, as you requested.

Obviously the math must be tailored to the cell in which the formula is placed.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mdarsot]

it is clipped portion of large formula. I just wanted to get idea accross.

at this point i dont want to re write all the formulas again. I wonder if there is a better solution out there that will skip same amount of rows in each copied formula.

thanks

 

[SkipVought]

Hi,

Is there a way to FILTER the rows to only display the rows you want the forumula in?

If so, apply the filter.

Copy the first formula.

Select the cells you want the formula in and PASTE.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

You could just use Find and Replace for the formulas on a single row. As I said before, you can just drag the formulas down from the first row and they will iterate properly.

As you've discovered, Excel doesn't pick up on the pattern you're trying to establish. There's nothing to be done about that - you're just asking Excel to do something it can't.

Another solution comes to mind that doesn't involve changing your formula - but it's very manual and might not save you much time.

Let's say that your formula is in A2. You could select range A2:A8, then drag the formula down as far as you need.

Excel will follow that pattern of formula; 6 empty rows; formula; 6 empty rows... - repeating.

You would wind up with something that looks like this:
[tt]
Row1
Row2 =IF(AND('Publish Tracking'!$E95)
Row3
Row4
Row5
Row6
Row7
Row8
Row9 =IF(AND('Publish Tracking'!$E102)
Row10
Row11
Row12
Row13
Row14
Row15
Row16 =IF(AND('Publish Tracking'!$E109)
....
[/tt]

Then delete all of the empty rows (there are multiple shortcuts to do that quickly if you're interested).

You'd be left with a series of formulas in contiguous cells that increase the row reference by 7.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.