Formula calculation

Question

I am trying to generate a chart based on a selection form control list box.  When the user makes a selection G43 in the formula below is populated with the row number that contains the data to be charted.  The formula below is used to determine the first column of that row that contains data for the chart. 

This is the formula I am trying to use

="=MATCH(INDEX(Data!"&G43&":"&G43&",MATCH(TRUE,INDEX(ISNUMBER(Data!"&G43&":"&G43&"),0),0)),Data!"&G43&":"&G43&",0)"

It returns

=MATCH(INDEX(Data!30:30,MATCH(TRUE,INDEX(ISNUMBER(Data!30:30),0),0)),Data!30:30,0)

If I edit the cell and hit calculate (F9) it displays the above and if I hit F9 again it returns a number that represents the column that the data fro the graph starts in.

My question is how do I make excel evaluate the formula twice so I get the number and not the intermediate formula?

Answer 1

Sorry, I wrote G4 instead of G43 in two of the three formulas that I proposed, and I forgot to add the reference to the Data sheet in all of them.

What I meant is: enter one of the formulas, e.g.

=MATCH(TRUE,ISNUMBER(INDIRECT("Data!"&G43&":"&G43)),0)

exactly as written here in a cell and confirm it by pressing Ctrl+Shift+Enter

Answer 2

Thank you this worked perfectly.

Answer 3

Thank you for you quick response but I am not sure I understand it.

I made this formula as an array as I think you were suggesting

{="=MATCH(INDEX(Data!"&G43&":"&G43&",MATCH(TRUE,INDEX(ISNUMBER(Data!"&G43&":"&G43&"),0),0)),Data!"&G43&":"&G43&",0)"}

But it still returns this instead of a number

=MATCH(INDEX(Data!30:30,MATCH(TRUE,INDEX(ISNUMBER(Data!30:30),0),0)),Data!30:30,0)

Perhaps I misunderstood what you were suggesting.  Can you be more specific please and show how me how the original formula should look.

Answer 4

As an array formula, confirmed with Ctrl+Shift+Enter:

=MATCH(TRUE,ISNUMBER(INDIRECT("Data!"&G43&":"&G43)),0)

or

=MATCH(TRUE,ISNUMBER(OFFSET(Data!$1:$1,G43-1,0)),0)

or

=MATCH(TRUE,ISNUMBER(INDEX(Data!$1:$1048576,G43,0)),0)