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

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?

@Monica16 

You may try instead of

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

use

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

etc.

 

 

 

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies