Forum Discussion
Autofill select rows from one sheet to another
I have a spreadsheet with 800 recipients served by 40 volunteer mailers, (sample attached)
I wish to auto fill multiple columns from the master spreadsheet to subsequent sheets.
But only selected rows as indicated by the individual mailers.
The master sheet will have a formula as follows:
=A2&" "&B2&"
"&D2&" "&C2&"
"&E2&"
which copies information from several cells to construct an individual mailing address.
The recipient sheet must contain the address, not the formula.
Seems simple enough but I am a novice and am lost to do this.
Someone please help!!
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:
- Yes, this result looks like it is what I want.
Is there something that delineated the mailer "ALLEN" to collect these lines to the secondary sheet?Yes, it is the FILTER function. and I use =TEXTAFTER(CELL("filename",A1),"]") to get the secondary sheet name.
Rachel,
First, let me apologize for not responding sooner. I soooo appreciate your help.
Things have been very hectic for me as I am in the process of arranging my retirement.Your answer did exactly what I was looking for when I posted the question, however, I posted the question prematurely.
What I really need is slightly different and I was hoping I could tax your expertise again.Are you willing?
In the event you are, I will continue.
First of all I will say I am impressed with the formula generator you show in your response. Is that something I have access to or is it an additional application to Excel? I'm sure that is extremely helpful.
I have done some modifications to the base master list.
As you will see, when I input an acronym in Column "F" it populates columns "G" and "H" from the "addresses" sheet, and in Column "I" the master sheet compiles a complete address from columns "A" thru "H".
What I wish to do is, rather than transfer the entire row, I would rather transfer only Column "I", the completed address, and possibly "J", the assigned volunteer mailer, to the subsequent "Volunteer Mailers' " sheets. (1-Dannenberg; 2-Shields; 3-Perez)
I've attached a portion of my worksheet for you.
Thank you so much in advance.
- 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:
