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!
A Drop Down List is extremely useful in Excel. However, If we have hundreds of values it becomes difficult to find the specific value we are looking for. In this tutorial you'll learn how to shrink your list to fewer options by typing few characters and your drop down list will show only options ...
@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.
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?