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
Highlighted

@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

@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?

@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