Help Solving a Spill Error

[BB38]

I am using a spreadsheet with 3 different tabs for various data items.

On tab 1, I'm using the following formula:

HYPERLINK("#"&CELL("address",INDEX(Table2[Investor],LEFT(G8, FIND(",",G8&",")-1)-ROW(Table2[[#Headers],[Investor]]))),"Go to Row")

this works great. If I click "go to row" it takes me to the row where the data I was looking for is.

On Tab 2, i'm using the same formula with a different data set. Here it is below:

HYPERLINK("#"&CELL("address",INDEX(Table22[[#All],[Name]],LEFT(G9, FIND(",",G9&",")-1)-ROW(Table22[[#All],[Name]]))),"Go to Row")

This results in a spill error. I don't know why - and I can see the data it is selecting as obstructing (Column H, rows 12-54) which I'm not even sure how it's getting over there.

help?

Brad

 

[Norie]

Brad

What do you get if you enter that formula in a cell in a blank column?

 

[BB38]

hi Norie - are you saying to take that formula, cut and paste it to a empty column somewhere?

a few things:

When I do hover over the Spill error it says "#B$B$25 - click once to follow" which is odd and it shows the spill area outlined as well.

I have been trying to cut and paste this formula to a new blank cell and the same spill happens in the column below wherever I put the formula.

 

[RoryA]

Why are you using [#All] in the second formula, especially in the ROW part - that will return an array of row numbers?

 

[BB38]

that just shows up when I select the range I want it to check - I agree it doesn't show up on the first sheet and not sure why. I deleted it and the same thing is happening still.

 

[Fluff]

The 2nd part of the formula should have #Headers not #All

oops bit slow.

 

[Norie]

Brad

I'm suggesting you try the formula in an empty column to see what it's actually returning, that might give you an idea what the problem is with the formula.

 

[BB38]

closer. I just replaced with headers, no spill error now, just a "ref!" error.

 

[BB38]

Brad

I'm suggesting you try the formula in an empty column to see what it's actually returning, that might give you an idea what the problem is with the formula.

yep. Just did that and saw another member said get rid of #All and Replace with #Headers. Switched it to a diff cell. Now it's a REF problem.

 

[BB38]

Brad

I'm suggesting you try the formula in an empty column to see what it's actually returning, that might give you an idea what the problem is with the formula.