IF function randomly skips rows - why?

**Heirak** · 03-05-2012, 05:31 AM

Hi Everyone,

First, a background of what I have so you may identify potential issues.

I have 4 tabs that each run a query to an in-house database. The tables of data are loaded into the respective tabs offset 5-10 columns to the right in order for me to use the first few columns for my own functions.

Since the data from the tables increases in row numbers over time, and my functions calculate on each new row, I inserted an IF function into each row that will perform the calculations if there is new data in the rows and remain blank if there isn't. I'm doing this in order to avoid having to manually drag down the equations for each new row in the table. And without this function, if I drag my formulas beyond the last row of table data, they all become #N/A.

So I use this function in order to prevent that:
For example: =IF($H523="","",(IF($E523="FC",$AG523,"")))

I will always have data in column H if there is new information from the table. It is formatted as 'general' and contains MP1.

So each row has their specific IF function that technically should remain specific to each row, 523, 524, etc.

My issue starts occurring near the end of the table. Currently, the table returns data up to row 525. I would expect my next row function to look like the above with 524 but it looks like this:
=IF($H525="","",(IF($E524="FC",$AG525,"")))

As you can see, it completely skips 524 in 2 of 3 spots. This is the second time it has occurred after I manually fixed the formulas and 'dragged' them down again. It happens on another tab as well.

Does anyone know what my problem might be? Should I be using a different formula to accomplish my goal that may be more efficient and at the same time may fix my problem?

-Thanks!

**Pepe Le Mokko** · 03-05-2012, 05:38 AM

Hi and welcome to the board

couldn't you insert a Table ? Formula will adapt automatically as rows are added

**martindwilson** · 03-05-2012, 07:46 AM

i cant see why ,can you post an example ,incidently a bit of over use of () there
it should be
=IF($H523="","",IF($E523="FC",$AG523,""))

**Heirak** · 03-05-2012, 08:09 AM

Thank you both!

I don't know if a table will work (but I have basic skills with excel so please tell me if I'm wrong) because some of my columns have lookups into other 'mapping' sheets.

I'll fix the redundancy issue - thank you! What other examples were you looking for aside from the one I described? I'll gladly post if you wouldn't mind getting back to me.