For example, I have a date column in current sheet (overview_sheet) and a count column, the sheets are named the same with the addition of a date behind it to differentiate each sheet eg. sheet_0714, sheet_0715

For the count column cells, I use functions like =sheet_0715!H20 manually to get the value from the other sheet, but is there a way where I can change the date part of the function dynamically based on the date of the row?

so, =sheet_(date)!H20 where (date) is based on date of the row in current sheet

CodePudding user response：

Please use one of the following, depending on how your cells are formatted.

1. formatted as text: _0715
   =INDIRECT("sheet"&T1&"!H20")

2. formatted as date: 0715 =INDIRECT("sheet_"&TEXT(T2,"mmdd")&"!H20")

Functions used:

• INDIRECT
• TEXT