This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Mac Excel issue - I have a formula which works perfectly on a number of sheets (and has worked perfectly in the test workbook). I must have deleted a column or cell because now i only get #REF in this one column, all other columns work as they should.
The formula is =IF(ROWS(JH$6:JH7)>COUNTA(IJ:IJ),"",INDEX(IJ:IJ,SMALL(IF(IJ$6:IJ$3970<>"",ROW(IJ$6:IJ$3970)),ROWS(JH$6:JH7))))
the results for the surrounding cells are shown here:
All these cells use the same formula, referencing their specific source column: IG, IH, II the error cell II, IJ, IK etc.
If I change the column number for the first cell above from IG to the problem column II the formula works:
Can anybody suggest a solution?
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(252.8, 79%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERA%3C/text%3E%3C/svg%3E)
Your question isn't particularly clear to me, especially as no column headers are visible in the pictures, but the most likely cause I can see for that error from that formulas is due to #REF errors in column IJ.