0

I've been using the below formula which is supposed to search a list of tab names in range AK17:AK32 and then if the user is on that current tab it will then show the corresponding result of the cell in range AV17:AV32.

=IFERROR(INDIRECT(""&AK17&"!AV17"), IFERROR(NOW()/0)*1&"")

Up until recently, it appeared to do what was needed however now it appears to only show the result of cell AV17 regardless of what tab the user is currently on.

The list of agents in column AK can change and is used to create the tab names each month hence why I was using the INDIRECT function. Is there an easier way I could implement this that would work better?

2

1 Answer 1

0

search a list of tab names in range AK17:AK32 and then if the user is on that current tab

To find a value in the current tab, you do not need to use indirect(). Put the agent name in cell I2 and use this formula instead:

=arrayformula( 
  iferror( 
    hlookup( 
      W6:W10, 
      AV16:AZ, 
      match(I2, AK17:AK32, 0) + 1, 
      false 
    ) 
  ) 
)

This is an array formula that will automatically fill cells Y6:Y10. To fill another number of cells, modify the parameter W6:W10 accordingly.

See cell Matt!Y6 in your sample spreadsheet.

3
  • Only issue with that is that I need the formula to be automatic if possible which is why was using the INDIRECT. If there was a way cell Y6 could populate with the current sheet name that may work? Jan 23, 2023 at 15:02
  • You are looking for values in the current sheet, so you do not need a sheet name but an agent name. There may be a sheet by the name of each agent, but you should still not confuse the two. You can automate the formula by populating cell I2 (not cell Y6) with the sheet name. One way to do that is to use the SheetName() custom function. Jan 23, 2023 at 15:24
  • Apologies, i thought as it didn't fully answer the question I needed to reclarify in the comments but on reflection, I will use this answer and use a drop-down in I2 pulling names from column AK for the user to manually choose the name to populate. Jan 23, 2023 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.