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

 

[BB38]

I did try that ...I've tried to even recreate the table. this is driving me crazy. I wish I could upload a sheet (I can't do the mini sheet upload b/c it's on a work pc where I can't install that).

help?

 

[Fluff]

What is the value of G9 & what is the range of your table?

 

[BB38]

Hi - G9 in this case is "33" which should then take me to the 33rd row.

Range of table is: =$B$12:$K$56 including a header.

 

[Fluff]

In that case with #All changed to #Headers, I don't know why you get a #REF error.

 

[Fluff]

If you use the Evaluate Formula button on the Formula tab & step through the calculation at what point does it go to #REF?

 

[BB38]

If you use the Evaluate Formula button on the Formula tab & step through the calculation at what point does it go to #REF?

this is the first time I have ever used the Evaluate Formula function I am sorry to say. I just used it and pressed evaluate to step through but nothing came up with a reference...we are now back to a spill. Apologies - i'm trying to fix this for work at the same time. Here is the current formula (note I changed table name to Sellside and the column to "Individual" - but everything else is the same.

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

I appreciate your help.

 

[Fluff]

You have left out the headers, try

HYPERLINK("#"&CELL("address",INDEX(Sellside[Individual],LEFT(G9,FIND(",",G9&",")-1)-ROW(Sellside[[#Headers],[Individual]]))),"Go to Row")

 

[BB38]

You have left out the headers, try

HYPERLINK("#"&CELL("address",INDEX(Sellside[Individual],LEFT(G9,FIND(",",G9&",")-1)-ROW(Sellside[[#Headers],[Individual]]))),"Go to Row")

that fixed it right away. Thank you SO much. Really mad my day!

 

[Fluff]

Glad we could help & thanks for the feedback.