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:
 
- Rachel,
Thank you so much for your response and the tremendous help you've been.
I have two more questions regarding this process.
I hope you can see me through.
First, I have the font in my master list color coded for various reasons.
I also strike through those who are being removed from the list.
I send a copy of the sheet for each mailer including the strikethrough so they know to remove them from their list as well.
Do you know, is there a way to migrate the font color and strikethrough with this transfer?
My second question is, can this sheet be set up to generate a separate spreadsheet I can attach to an email without going through the process of copying and pasting each mailer list?
(I'm working with around 70 volunteer mailers.)
The font color and strikethrough are especially time consuming.
They would be the more important to me.
Your time and wxpertise are deeply appreciated.Hi,
To keep the formatting in Master sheet, you will have to use VBA.
I am not sure whether you are open to that. I don't mind sending you the VBA code. (I use Record Macro to write VBA anyway), but the challenge is, VBA code is less transparent than Excel formulas, so you might find it difficult to adapt or maintain the VBA code by yourself.
But just in case you are interested, here is how you can use "Record Macro" to generate the VBA code:
1. Follow below to display "Developer Tab" in your ribbon:
2. Click the "Record Macro" button:
3. Go to "1-Dannenberg", clear contents of the whole sheet, then go to "Master" sheet, filter out rows with"1-Dannenberg", select Column I and Column J, copy, go to"1-Dannenberg", select cell A1, paste special with "Values & Source Formatting".
4. Press "Stop Recording: button:
I added a "Filter By Mailer" button in "Addresses" sheet. It is linked to this macro.
As for splitting those sheet into different excel sheets, with formatting, you can use online tools for that. just google "Split Excel files online":
I use this one:
https://products.groupdocs.app/splitter/excel
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.
