05-07-2020 10:33 AM
05-07-2020 10:33 AM
05-09-2020 02:31 AM
@Josecahu , yes there is a way to calculate a range address and then refer to value at that address using INDIRECT function , see attached file which achieves what you want :-
Yellow cells : input tables (alternate mixed)
Green cells : address calculation tables
Blue cells : output tables (separated)
05-09-2020 03:30 AM
@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
05-09-2020 02:53 PMSolution
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
05-09-2020 10:08 PM
@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.
05-09-2020 10:55 PM - edited 05-09-2020 11:06 PM
@Josecahu Hi. I am not quite sure how your data is collected. Assuming your data is in one spreadsheet and there are no blank rows or columns within the data range, then a very good way of analysing this data is to use Sort, Filter and Sub Total.
I am attaching an example of what I mean and the instructions to acheive the results needed. If this doesn't help your query, then my apologies.
See Spreadsheet and instructions attached.
05-10-2020 12:07 AM - edited 05-10-2020 05:00 AM
@Josecahu As a variant, and in case your Excel version supports the FILTER function (see note below). In the attached workbook I tried to visualise the situation you are describing, though, for three stores only. Easy to expand. Stores may be added at any time. Not every stores must be included every day. Stores do not have to be in the same order every day. Obviously, store names must be consistent.
Note: This function is currently available to Microsoft 365 subscribers in the Monthly channel. It will be available to Microsoft 365 subscribers in the Semi-Annual channel starting in July 2020.