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:
- 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.
- So, if I leave this in place on the secondary sheet, if I add another entry for the mailer ALLEN on the master list, will it also auto populate to the secondary list, and will updates also transfer through?
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.
