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
  • Consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Commented Jan 23, 2023 at 9:31
  • To confirm, the formula sits in the range X6: X10. The link to the sheet is as follows, if you need any other info please don't hesitate to ask: docs.google.com/spreadsheets/d/… Commented Jan 23, 2023 at 13:20

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? Commented 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. Commented 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. Commented 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.