Help cell refernece not to delete when delete columns on another sheet

pkam2010

New Member
Joined
Mar 29, 2019
Messages
19
HI

can someone help. i have a formalu below

=IFERROR(INDEX('sheet2'!$A$5:$S$32,MATCH('sheet1'!B19,'sheet2'!$A$5:$A$17,0),5),"")

however when i need to add/dleete some columns in sheet2 and but i want it to stay and look at the above, but when i add/delete the columns the cells it is referencing to changes to #REF !

how do i stop this from happening
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,453
With the formula in Sheet1 -- Try:

=IFERROR(INDEX(INDIRECT("sheet2!$A$5:$S$32"),MATCH(B19,INDIRECT("sheet2!$A$5:$A$17"),0),5),"")
 

pkam2010

New Member
Joined
Mar 29, 2019
Messages
19
Hi

it is not returning anything, the cell is blank.

when i delete/add rows its great it still loking at the cells in sheet2 but it is not bring up that value it is suppose to find the cell is just blank.
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,453
HI

can someone help. i have a formalu below

=IFERROR(INDEX('sheet2'!$A$5:$S$32,MATCH('sheet1'!B19,'sheet2'!$A$5:$A$17,0),5),"")

however when i need to add/dleete some columns in sheet2 and but i want it to stay and look at the above, but when i add/delete the columns the cells it is referencing to changes to #REF !

how do i stop this from happening
OK, let's try this again.. I assume (at some point) the above Formula worked OK. Right?
So what exactly are you doing in your sheet2 - be specific - tell me exactly column and row what
are you doing to cause the formula "to break", or produce the #REF !? Post back...

Jim
 
Last edited:

Forum statistics

Threads
1,081,991
Messages
5,362,595
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top