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:
- Thank you for your response.
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:
rachel To autofill specific rows for each volunteer, first, create a master sheet with the formula combining the address, like `=A2&" "&B2&" "&D2&" "&C2&" "&E2&" "`. Then, to copy only selected rows to the volunteer sheets, use the `INDEX` or `FILTER` functions to pick the relevant rows. Once you have the addresses on the volunteer's sheet, use `Copy-Paste Special > Values` to paste only the address (not the formula). This ensures the recipient sheet shows the actual address without the underlying formula.
