Relative cell reference is changing the formula to #REF!

[Monica16]

Hi, I need some insight on restricting an error in MS excel. I have a dynamic drop down list that changes  SQL server table I pull in excel. Based on the different drop down selections the table pull is 10 rows or sometimes 100 rows or even more. Corresponding the rows I have relative cell reference formulas (IF function) to this table cells and once the table row changes some cell reference within the IF function changes to #REF! How can I resolve this error? Thanks in advance for the help!

[#2]

@Monica16 

Hi

Although I do not see any sample data but in situations like that you need to create a defined name using an OFFSET function.

Here is one of my tutorials in which I create a Defined Name, you can watch it. It stores an expandable range.

https://www.youtube.com/watch?v=8gqodEiDoJ8

Hope that helps

Nabil Mourad

[#3]

@nabilmourad Thanks for the information. Unfortunately I am not sure if the OFFSET function will be helpful. To elaborate more on my concern attach is a snapshot of the report I built. I had to greyout the data due to security reasons. 

if you see the screenshot, column A to column Q is pulled from SQL server table based on the Portfolio, Category dropdown selections in column C & column D. Starting column R until Column W I have IF function referencing based on the data populated in Column A to Column Q.

For example-

In column R my IF statement is as follows-

=IF(G7<>G6,"",IF(OR(Q7<>Q6,M7<>M6),IF(R6<>"",R6,L6),IF(R6<>"",R6,"")))

I have similar relative reference for all the column cells in R based on the corresponding row number. However when I changed the  portfolio and category drop downs, some of the rows convert the formula as follows-

IF(#REF!<>G6,"",IF(OR(#REF!<>Q6,#REF!<>M6),IF(R6<>"",R6,L6),IF(R6<>"",R6,"")))

Basically the cell's relative reference changes to #REF! which doesn't process the result as expected. I tried changing the relative reference to mixed reference  eg- G$7 for the row number to be absolute(static) but it still errors out with #REF!

Do you know how offset will function in this case or may be have any other suggestions?

[#4]

@Monica16 

You may try instead of

=IF(G7<>G6,"",...

use

=IF(INDEX(G:G,ROW()+1)<>INDEX(G:G,ROW()),"",

etc.