Forum Discussion
Direccionar una fila
Tabla 25 Maestra. Separar en 6 tablas los resultados de cada una de las 6 tiendas. Si separo los datos en la misma tabla25 sí que funciona, si separo en 6 tablas, en la Tabla1 no reconoce y da 0 =@INDIRECTO(Hoja25!N3) . Indirecto sólo funciona con la presente tabla?
Hoja25!N3=A2 ------->"Hoja 2 Celda" A2=AN
Thanks
Josecahu BTW, the trasformation can be made more compact by calculating addresses in R1C1 style (it means cell ar Row1Col1 , i.e. A1 , similarly R3C5 means C5)
see sheet2 of attachment , in this the green temp calc cells are reduced to much extent, and now the INDIRECT function uses R1C1 style instead of A1 style
Tabla 25 Maestra. Separar en 6 tablas los resultados de cada una de las 6 tiendas. Si separo los datos en la misma tabla25 sí que funciona, si separo en 6 tablas, en la Tabla1 no reconoce y da 0 =@INDIRECTO(Hoja25!N3) . Indirecto sólo funciona con la presente tabla?
Hoja25!N3=A2 ------->"Hoja 2 Celda" A2=AN
Thanks
Josecahu , Indirect works across sheets but in that case it didn't work with R1C1 style may be since in excel settings default style is A1 notation.
In attached workbook, i used A1 notation and it split the Master table data in Sheet25 to different sheets Store A, Store B... as you suggest.
I think like attached is what you need, right?
Take care : Indirect changes the address of data pick-up sheet and cells to literal text string, so if in future you change the name of Sheet25 to say, Table25 or MasterTable etc., then you need to update the formulas as per new sheet name. To make it easy, use Concaatenate function as i did. Only update the yellow cell of Store A, Store B ... sheets to new sheet name. And remember to put ! sign after pick-up sheet name...
BTW, there could be otherways of achieving all this separation like use of LOOKUP formulas, data sorting etc. INDIRECT is one of the easy and intuitive solution. Just need to take care about names of address you put in Indirect function.