Forum Discussion
Automatically change tables based on data from one cell.
Hi everyone!
Need help!
In the image you can see spotted areas
here is the formulae:
=IF(INDEX(Rota14[[MON]:[SAT]],MATCH(CO16,date[W/C Date],0),MATCH(CR17,Rota14[[#Headers],[MON]:[SAT]],0))="off","7.5","")
How can I change formulae to get table " Rota14" changed to any other table "Rota?" based on number in cell CP18?
The formula you posted can be changed by replacing these references:
Rota14[[MON]:[SAT]]
with
INDIRECT("Rota" & $CP$18 & "[[MON]:[SAT]]")
and
Rota14[[#Headers],[MON]:[SAT]]
with
INDIRECT("Rota" & $CP$18 & "[[#Headers],[MON]:[SAT]]")
(The spaces around the ampersands are not required; I included them to improve readability.)The built-in function INDIRECT takes a string value that you construct (in this case using literals, the ampersand operator, and an absolute reference to CP18) and evaluates it as an address or reference. Microsoft describes it here, but you might find this article more meaningful, or you can search for some online videos that explain it.
BTW, the function can also work with references to cells/ranges/tables on a different worksheet or in a different (already-open) workbook.- To dynamically change the table reference in your formula based on the value in cell CP18, you can modify the formula as follows:
=IF(INDEX(INDIRECT(CONCATENATE(CP18,"[[MON]:[SAT]]")),MATCH(CO16,date[W/C Date],0),MATCH(CR17,INDIRECT(CONCATENATE(CP18,"[[#Headers],[MON]:[SAT]]")),0))="off","7.5","")
In the modified formula, the INDIRECT function is used to dynamically construct the table reference based on the value in cell CP18. The CONCATENATE function is used to concatenate the table name ("Rota") with the desired range ("[[MON]:[SAT]]" and "[[#Headers],[MON]:[SAT]]").
By using INDIRECT with CONCATENATE, the formula will evaluate the resulting text as a valid table reference. This allows you to change the table reference by simply changing the value in cell CP18.- unfortunately it didn't work
