Jul 23 2019 12:52 PM
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!
Jul 23 2019 03:52 PM
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
Jul 24 2019 12:31 PM
@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?
Jul 24 2019 01:35 PM
You may try instead of
=IF(G7<>G6,"",...
use
=IF(INDEX(G:G,ROW()+1)<>INDEX(G:G,ROW()),"",
etc.