07-23-2019 12:52 PM
07-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!
07-23-2019 03:52 PM
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.
Hope that helps
07-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.
In column R my IF statement is as follows-
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-
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?
07-24-2019 01:35 PM
You may try instead of