Forum Discussion
Autofill select rows from one sheet to another
Hi,
You can type below formula in Cell A1 in each secondary sheet:
=VSTACK(Table3[[#Headers],[Full Address]:[MAILER]], FILTER(Table3[[Full Address]:[MAILER]],Table3[MAILER]=TEXTAFTER(CELL("filename",A1),"]"),""))Breakdown of the formula:
TEXTAFTER(CELL("filename",A1),"]") -> gets the secondary sheet name, such as "1-Dannenberg".
FILTER(Table3[[Full Address]:[MAILER]],Table3[MAILER]=TEXTAFTER(CELL("filename",A1),"]"),"") -> filter out column I and column J with column J = sheetName.
VSTACK -> stack table headers and data vertically.
In case you want to check the formula step by step by yourself, here is how to open the "formula builder":
Put your cursor at cell A1, select the function you want to check (e.g VSTACK, or FILTER), then click the "fx" icon (highlighted in red rectangle).
Then the formula builder will open in the right:
My need has changed to transferring single column of cells rather than whole rows, Also I need to retain the master list and maintain it as interactive between sheets.
Hi,
You can type below formula in Cell A1 in each secondary sheet:
=VSTACK(Table3[[#Headers],[Full Address]:[MAILER]], FILTER(Table3[[Full Address]:[MAILER]],Table3[MAILER]=TEXTAFTER(CELL("filename",A1),"]"),""))
Breakdown of the formula:
TEXTAFTER(CELL("filename",A1),"]") -> gets the secondary sheet name, such as "1-Dannenberg".
FILTER(Table3[[Full Address]:[MAILER]],Table3[MAILER]=TEXTAFTER(CELL("filename",A1),"]"),"") -> filter out column I and column J with column J = sheetName.
VSTACK -> stack table headers and data vertically.
In case you want to check the formula step by step by yourself, here is how to open the "formula builder":
Put your cursor at cell A1, select the function you want to check (e.g VSTACK, or FILTER), then click the "fx" icon (highlighted in red rectangle).
Then the formula builder will open in the right: