Ask Your Question
1

reference sheet name from cell value in match formula

asked 2018-02-03 02:01:44 +0200

Mikele gravatar image

I have one sheet with:

  • column A filled with dates in ascending order
  • row 1 filled with currency tickers (USD, JPY, EUR, AUD, CHF)

several other sheets each named exactly as the currency tickers each sheet as column A filled with dates in ascending order, column B with the currency daily average

I am trying to fill cells in my first sheet with values from the other sheet based on the row's date in column A and the column's ticker name in row 1 as the sheet name to look at.

assuming I am in ROW 547 and COLUMN G , I am using this formula in my first sheet: =INDIRECT(G1&"."&ADDRESS(MATCH(A547,G1&"."&$A$1:$A$1000,-1),2))

but I am stuck with the MATCH formula:

this works: =MATCH(A547,USD.$A$1:$A$1000,-1)

this doesn't: =MATCH(A547,G1&"."&$A$1:$A$1000,-1) as it appears like the range $A$1:$A$1000 is in the same sheet where the formula is and not in the one named in the cell G! as I am hoping.

any help is appreciated...

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2018-02-03 06:41:30 +0200

Jim K gravatar image

It should be like this.

=INDIRECT(G$1&".B"&MATCH($A547;INDIRECT(G$1&".$A$1:$A$1000");1))
  • You correctly used INDIRECT for the outside part but forgot to use it on the inner part.
  • G1 & "." & $A$1:$A$1000 is not correct as it attempts to concatenate an actual range. The range should be a string, that is, G$1 & ".$A$1:$A$1000".
  • If the dates are in ascending order, then type for MATCH should be 1. If the function works with -1, then perhaps your dates are in descending order.
  • More $ signs were added for easier filling.
edit flag offensive delete link more

Comments

you are correct, I should have used INDIRECT also in the inner part. I could make it work, thank you so much for the help!

Mikele gravatar imageMikele ( 2018-02-03 09:00:48 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-02-03 02:01:44 +0200

Seen: 331 times

Last updated: Feb 03 '18