Home

Relative cell reference is changing the formula to #REF!

%3CLINGO-SUB%20id%3D%22lingo-sub-770428%22%20slang%3D%22en-US%22%3ERelative%20cell%20reference%20is%20changing%20the%20formula%20to%20%23REF!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-770428%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20need%20some%20insight%20on%20restricting%20an%20error%20in%20MS%20excel.%20I%20have%20a%20dynamic%20drop%20down%20list%20that%20changes%26nbsp%3B%20SQL%20server%20table%20I%20pull%20in%20excel.%20Based%20on%20the%20different%20drop%20down%20selections%20the%20table%20pull%20is%2010%20rows%20or%20sometimes%20100%20rows%20or%20even%20more.%20Corresponding%20the%20rows%20I%20have%20relative%20cell%20reference%20formulas%20(IF%20function)%20to%20this%20table%20cells%20and%20once%20the%20table%20row%20changes%20some%20cell%20reference%20within%20the%20IF%20function%20changes%20to%20%23REF!%20How%20can%20I%20resolve%20this%20error%3F%20Thanks%20in%20advance%20for%20the%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-770428%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-770732%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20cell%20reference%20is%20changing%20the%20formula%20to%20%23REF!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-770732%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380986%22%20target%3D%22_blank%22%3E%40Monica16%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EAlthough%20I%20do%20not%20see%20any%20sample%20data%20but%20in%20situations%20like%20that%20you%20need%20to%20create%20a%20defined%20name%20using%20an%20OFFSET%20function.%3C%2FP%3E%3CP%3EHere%20is%20one%20of%20my%20tutorials%20in%20which%20I%20create%20a%20Defined%20Name%2C%20you%20can%20watch%20it.%20It%20stores%20an%20expandable%20range.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D8gqodEiDoJ8%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D8gqodEiDoJ8%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772294%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20cell%20reference%20is%20changing%20the%20formula%20to%20%23REF!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772294%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20information.%20Unfortunately%20I%20am%20not%20sure%20if%20the%20OFFSET%20function%20will%20be%20helpful.%20To%20elaborate%20more%20on%20my%20concern%20attach%20is%20a%20snapshot%20of%20the%20report%20I%20built.%20I%20had%20to%20greyout%20the%20data%20due%20to%20security%20reasons.%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124362iF39FE382FF454ABD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Broken%20LIR%20error.PNG%22%20title%3D%22Broken%20LIR%20error.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eif%20you%20see%20the%20screenshot%2C%20column%20A%20to%20column%20Q%20is%20pulled%20from%20SQL%20server%20table%20based%20on%20the%20Portfolio%2C%20Category%20dropdown%20selections%20in%20column%20C%20%26amp%3B%20column%20D.%20Starting%20column%20R%20until%20Column%20W%20I%20have%20IF%20function%20referencing%20based%20on%20the%20data%20populated%20in%20Column%20A%20to%20Column%20Q.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example-%3C%2FP%3E%3CP%3EIn%20column%20R%20my%20IF%20statement%20is%20as%20follows-%3C%2FP%3E%3CP%3E%3DIF(G7%26lt%3B%26gt%3BG6%2C%22%22%2CIF(OR(Q7%26lt%3B%26gt%3BQ6%2CM7%26lt%3B%26gt%3BM6)%2CIF(R6%26lt%3B%26gt%3B%22%22%2CR6%2CL6)%2CIF(R6%26lt%3B%26gt%3B%22%22%2CR6%2C%22%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20similar%20relative%20reference%20for%20all%20the%20column%20cells%20in%20R%20based%20on%20the%20corresponding%20row%20number.%20However%20when%20I%20changed%20the%26nbsp%3B%20portfolio%20and%20category%20drop%20downs%2C%20some%20of%20the%20rows%20convert%20the%20formula%20as%20follows-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF(%23REF!%26lt%3B%26gt%3BG6%2C%22%22%2CIF(OR(%23REF!%26lt%3B%26gt%3BQ6%2C%23REF!%26lt%3B%26gt%3BM6)%2CIF(R6%26lt%3B%26gt%3B%22%22%2CR6%2CL6)%2CIF(R6%26lt%3B%26gt%3B%22%22%2CR6%2C%22%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20the%20cell's%20relative%20reference%20changes%20to%20%23REF!%20which%20doesn't%20process%20the%20result%20as%20expected.%20I%20tried%20changing%20the%20relative%20reference%20to%20mixed%20reference%26nbsp%3B%20eg-%20G%247%20for%20the%20row%20number%20to%20be%20absolute(static)%20but%20it%20still%20errors%20out%20with%20%23REF!%3C%2FP%3E%3CP%3EDo%20you%20know%20how%20offset%20will%20function%20in%20this%20case%20or%20may%20be%20have%20any%20other%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772418%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20cell%20reference%20is%20changing%20the%20formula%20to%20%23REF!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772418%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F380986%22%20target%3D%22_blank%22%3E%40Monica16%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20try%20instead%20of%3C%2FP%3E%0A%3CPRE%3E%3DIF(G7%26lt%3B%26gt%3BG6%2C%22%22%2C...%3C%2FPRE%3E%0A%3CP%3Euse%3C%2FP%3E%0A%3CPRE%3E%3DIF(INDEX(G%3AG%2CROW()%2B1)%26lt%3B%26gt%3BINDEX(G%3AG%2CROW())%2C%22%22%2C%3C%2FPRE%3E%0A%3CP%3Eetc.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Monica16
New Contributor

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!

3 Replies

@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

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. Broken LIR error.PNG

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?

Highlighted

@Monica16 

You may try instead of

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

use

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

etc.

 

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies