Making information auto-populate across sheets

Question

Hello,&nbsp;I am looking to get information to auto-populate across multiple sheets based on budget codes being used for purchases. For example:&nbsp;Sheet 1 is a master-list of all purchases made and might look like this:&nbsp;Item&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Cost&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Account&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NotesPencil&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Supplies-5432&nbsp; &nbsp; &nbsp; &nbsp;For BobCar&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $20k&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Capital-8345&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Food&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Misc-9265&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Team building&nbsp;Pen&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Supplies-5432&nbsp; &nbsp; &nbsp; &nbsp; For BillyRadio&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$500&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Capital-8345&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Confetti&nbsp; &nbsp; &nbsp; &nbsp; $2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Misc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Team building&nbsp;The "Account" heading would be a drop down list that only allows the budget codes in use. What I would like to do is then have Sheet 2 be the "Supplies" sheet, and if the user selects the supply code on the Sheet 1, then all of the information gets automatically copied over to Sheet 2. Sheet 3 would be the "Capital" sheet. If the Capital code is selected on Sheet 1, then all of the information on that row gets copied over to Sheet 3. So on and so forth with other codes (this way they can be tallied, sorted, and data can be manipulated separate from the master list, and so that transcription errors are limited across the workbook.&nbsp;The problem I'm having is that vlookup will only grab one of the entries in a given account, not all of them. I don't think Index/Match is flexible enough to find everything.&nbsp;&nbsp;What is the best way to go about making that happen in excel? Or am I missing something with the functions I already tried?

oliverscheurich · Answer

KS658795&nbsp;=FILTER(A2:D7,C2:C7="Supplies-5432")&nbsp;If you have access to the FILTER function you can apply this formula. This works across sheets as well. For visualisation i've made an example within one sheet. If you don't have the FILTER function a combination of INDEX and SMALL can be applied.

Forum Discussion

Making information auto-populate across sheets

1 Reply

Resources