SOLVED

Direccionar una fila

%3CLINGO-SUB%20id%3D%22lingo-sub-1371435%22%20slang%3D%22es-ES%22%3EAddress%20a%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1371435%22%20slang%3D%22es-ES%22%3E%3CDIV%20class%3D%22style-scope%20ytd-comment-renderer%22%3E%3CDIV%20class%3D%22style-scope%20ytd-expander%22%3ELet's%20say%20they%20provide%20me%20with%20a%20daily%20table%20with%20data%20from%206%20stores%20(6%20rows)%2C%20the%20next%20day%20the%20same%20extended%20table%20in%206%20other%20rows%20(already%2012)%20and%20so%20on.%20If%20I%20want%20to%20separate%20the%20data%20into%206%20tables%20and%20automate%20it%2C%20I%20know%20that%20in%20store%20A%20sheet%2C%20I%20will%20have%20the%20rows%20of%20the%20master%20table%20(1%2C7%2C13%2C19%2C...)%2C%20in%20store%20B%20table%20I%20will%20have%20the%20rows%20(2%2C8%2C14%2C20%2C...).%20To%20automate%20it%20I%20had%20thought%20about%20repeating%20that%20described%20process%20but%20I%20do%20not%20know%20if%20it%20is%20possible%20to%20refer%20a%20row%20address%20as%20F(1%2B6).%20Is%20it%20possible%20or%20is%20there%20some%20other%20way%20to%20do%20it%3F%20Pivottables%3F%3C%2FDIV%3E%3CDIV%20class%3D%22style-scope%20ytd-comment-action-buttons-renderer%22%3E%3CDIV%20class%3D%22style-scope%20ytd-comment-action-buttons-renderer%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22style-scope%20ytd-comment-action-buttons-renderer%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22style-scope%20ytd-comment-action-buttons-renderer%22%3E%3CA%20target%3D%22_blank%22%3EAnswer%3C%2FA%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22style-scope%20ytd-comment-action-buttons-renderer%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22style-scope%20ytd-comment-renderer%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1371435%22%20slang%3D%22es-ES%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1376244%22%20slang%3D%22en-US%22%3ERe%3A%20Direccionar%20una%20fila%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1376244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659813%22%20target%3D%22_blank%22%3E%40Josecahu%3C%2FA%3E%26nbsp%3B%2C%20yes%20there%20is%20a%20way%20to%20calculate%20a%20range%20address%20and%20then%20refer%20to%20value%20at%20that%20address%20using%20INDIRECT%20function%20%2C%20see%20attached%20file%20which%20achieves%20what%20you%20want%20%3A-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYellow%20cells%20%3A%20input%20tables%26nbsp%3B%20(alternate%20mixed)%3C%2FP%3E%3CP%3EGreen%20cells%20%3A%20address%20calculation%20tables%3C%2FP%3E%3CP%3EBlue%20cells%20%3A%20output%20tables%26nbsp%3B%20(separated)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1376298%22%20slang%3D%22en-US%22%3ERe%3A%20Direccionar%20una%20fila%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1376298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659813%22%20target%3D%22_blank%22%3E%40Josecahu%3C%2FA%3E%26nbsp%3BBTW%2C%20the%20trasformation%20can%20be%20made%20more%20compact%20by%20calculating%20addresses%20in%20R1C1%20style%20(it%20means%20cell%20ar%20Row1Col1%20%2C%20i.e.%20A1%20%2C%20similarly%20R3C5%20means%20C5)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esee%20sheet2%20of%20attachment%20%2C%20in%20this%20the%20green%20temp%20calc%20cells%20are%20reduced%20to%20much%20extent%2C%20and%20now%20the%20INDIRECT%20function%20uses%20R1C1%20style%20instead%20of%20A1%20style%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1377101%22%20slang%3D%22es-ES%22%3ERe%3A%20Route%20a%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1377101%22%20slang%3D%22es-ES%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F636579%22%20target%3D%22_blank%22%3E%40amit_bhola%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3ETable%2025%20Master.%20Separate%20the%20results%20from%20each%20of%20the%206%20stores%20into%206%20tables.%20If%20I%20separate%20the%20data%20into%20the%20same%20table25%20does%20work%2C%20if%20I%20separate%20into%206%20tables%2C%20in%20Table1%20does%20not%20recognize%20and%20gives%200%20'%40INDIRECTO(Sheet25!%20N3)%20.%20Indirect%20only%20works%20with%20this%20table%3F%3C%2FP%3E%3CP%3EBlade25!%20N3-A2%20-------%26gt%3B%22Sheet%202%20Cell%22%20A2-AN%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1377462%22%20slang%3D%22en-US%22%3ERe%3A%20Direccionar%20una%20fila%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1377462%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659813%22%20target%3D%22_blank%22%3E%40Josecahu%3C%2FA%3E%26nbsp%3B%2C%20Indirect%20works%20across%20sheets%20but%20in%20that%20case%20it%20didn't%20work%20with%20R1C1%20style%20may%20be%20since%20in%20excel%20settings%20default%20style%20is%20A1%20notation.%3C%2FP%3E%3CP%3EIn%20attached%20workbook%2C%20i%20used%20A1%20notation%20and%20it%20split%20the%20Master%20table%20data%20in%20Sheet25%20to%20different%20sheets%20Store%20A%2C%20Store%20B...%20as%20you%20suggest.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20like%20attached%20is%20what%20you%20need%2C%20right%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETake%20care%20%3A%20Indirect%20changes%20the%20address%20of%20data%20pick-up%20sheet%20and%20cells%20to%20literal%20text%20string%2C%20so%20if%20in%20future%20you%20change%20the%20name%20of%20Sheet25%20to%20say%2C%20Table25%20or%20MasterTable%20etc.%2C%20then%20you%20need%20to%20update%20the%20formulas%20as%20per%20new%20sheet%20name.%20To%20make%20it%20easy%2C%20use%20Concaatenate%20function%20as%20i%20did.%20Only%20update%20the%20yellow%20cell%20of%20Store%20A%2C%20Store%20B%20...%20sheets%20to%20new%20sheet%20name.%20And%20remember%20to%20put%20!%20sign%20after%20pick-up%20sheet%20name...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBTW%2C%20there%20could%20be%20otherways%20of%20achieving%20all%20this%20separation%20like%20use%20of%20LOOKUP%20formulas%2C%20data%20sorting%20etc.%20INDIRECT%20is%20one%20of%20the%20easy%20and%20intuitive%20solution.%20Just%20need%20to%20take%20care%20about%20names%20of%20address%20you%20put%20in%20Indirect%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1377480%22%20slang%3D%22en-US%22%3ERe%3A%20Direccionar%20una%20fila%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1377480%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659813%22%20target%3D%22_blank%22%3E%40Josecahu%3C%2FA%3E%26nbsp%3BHi.%20I%20am%20not%20quite%20sure%20how%20your%20data%20is%20collected.%20Assuming%20your%20data%20is%20in%20one%20spreadsheet%20and%20there%20are%20no%20blank%20rows%20or%20columns%20within%20the%20data%20range%2C%20then%20a%20very%20good%20way%20of%20analysing%20this%20data%20is%20to%20use%20Sort%2C%20Filter%20and%20Sub%20Total.%3CBR%20%2F%3EI%20am%20attaching%20an%20example%20of%20what%20I%20mean%20and%20the%20instructions%20to%20acheive%20the%20results%20needed.%20If%20this%20doesn't%20help%20your%20query%2C%20then%20my%20apologies.%3CBR%20%2F%3ESee%20Spreadsheet%20and%20instructions%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1377548%22%20slang%3D%22en-US%22%3ERe%3A%20Direccionar%20una%20fila%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1377548%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659813%22%20target%3D%22_blank%22%3E%40Josecahu%3C%2FA%3E%26nbsp%3BAs%20a%20variant%2C%20%3CSTRONG%3Eand%3C%2FSTRONG%3E%20in%20case%20your%20Excel%20version%20supports%20the%20FILTER%20function%20(%3CU%3Esee%20note%20below%3C%2FU%3E).%20In%20the%20attached%20workbook%20I%20tried%20to%20visualise%20the%20situation%20you%20are%20describing%2C%20though%2C%20for%20three%20stores%20only.%20Easy%20to%20expand.%20Stores%20may%20be%20added%20at%20any%20time.%20Not%20every%20stores%20must%20be%20included%20every%20day.%20Stores%20do%20not%20have%20to%20be%20in%20the%20same%20order%20every%20day.%20Obviously%2C%20store%20names%20must%20be%20consistent.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3CSTRONG%3ENote%3A%3C%2FSTRONG%3E%26nbsp%3BThis%20function%26nbsp%3Bis%20currently%20available%20to%20Microsoft%20365%20subscribers%20in%20the%20Monthly%20channel.%20It%20will%20be%20available%20to%20Microsoft%20365%20subscribers%20in%20the%20Semi-Annual%20channel%20starting%20in%20July%202020.%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
Imaginemos que me facilitan una tabla a diario con los datos de 6 tiendas (6 filas), al día siguiente la misma tabla ampliada en otras 6 filas (ya son 12) y así sucesivamente. Si quiero separar en 6 tablas los datos y automatizarlo, sé que en la hoja de la Tienda A, tendré las filas de la tabla maestra (1,7,13,19,...), en la tabla de la Tienda B tendré las filas (2,8,14,20,...). Para automatizarlo había pensado en repetir ese proceso descrito pero no sé si es posible referenciar una dirección de fila como F(1+6). ¿Es posible o hay alguna otra forma de hacerlo? ¿Tablas dinámicas?
 
 
6 Replies
Highlighted

@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)

Highlighted

@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

Highlighted
Best Response confirmed by Josecahu (New Contributor)
Solution

@amit_bhola 

  

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

Highlighted

@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.

Highlighted

@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.

Highlighted

@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.