Amend Offset to pick up row number

[howard]

I have a match function in Col F eg F5 to find the row number


I would like to amend the offset function to automatically include the row number

=OFFSET(Sales_Rep!E1357,0,COLUMNS(F1357:AQ1357))

I tried to amend as follows but cannot get it to work

=OFFSET(Sales_Rep!&F5,0,COLUMNS(F&F5:AQ&F5)

I have the following formula in F5 which returns the row number on sheet Sales_Rep

=MATCH(C5,Sales_Rep!B1:B1387,0)

It would be appreciated if someone could kindly amend my code

 

[Smitty]

Take a look at INDIRECT.

It lets you join ranges & text that functions can read. Note that it's very expensive, so you'll want to limit how many you have.

 

[Marcelo Branco]

Since COLUMNS(F1:AQ1)=COLUMNS(Fx:AQx) for any x, try

=OFFSET(INDEX(Sales_Rep!E:E,F5),0,COLUMNS(F1:AQ1))

or (avoiding OFFSET a volatile function)

=INDEX(Sales_Rep!F:AQ,F5,COLUMNS(F1:AQ1))

M.

 

[Smitty]

Since COLUMNS(F1:AQ1)=COLUMNS(Fx:AQx) for any x, try

=OFFSET(INDEX(Sales_Rep!E:E,F5),0,COLUMNS(F1:AQ1))

or (avoiding OFFSET a volatile function)

=INDEX(Sales_Rep!F:AQ,F5,COLUMNS(F1:AQ1))

M.

Nice Marcelo! Far better than INDIRECT!

 

[Marcelo Branco]

Nice Marcelo! Far better than INDIRECT!

Thank you, Smitty


M.

 

[howard]

thanks for the help