Forum Discussion
Solved
insert DATA from one sheet to another..
i have a worksheet full of data, i have a list of names in column 1 .. i then have rows after each of them of Dates of when assessments are due.. (in columns 3 month, 6 month and so on) .... in another sheet, i would like to have 12 Columns of each Month in that Year.... i want to then use a formula, to look at my first worksheet to tell me who is due assessment in 'January 2023' and so on .. and i would like it to then bring up all the names in that column of all the people due in that month, they will have numerous people in each month though.... but i cant figure out how to do this
=IFERROR(INDEX(Sheet1!$B$3:$B$500,SMALL(IF(MMULT(N(Sheet1!$G$3:$L$500=Sheet2!B$3),ROW($1:$6)^0)=1,ROW($1:$498)),ROW(1:1))),"")You are welcome. With this formula you can enter employees in range B3:B500 and their corresponding dates in range G3:L500.
6 Replies
=IFERROR(INDEX($A$2:$A$20,SMALL(IF(MMULT(N($B$2:$E$20=G$1),ROW($1:$4)^0)=1,ROW($1:$19)),ROW(1:1))),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell G2 and filled across range G2:J12 in the example. This can be done across two worksheets accordingly. In cells G1:J1 are dates 01.01.2023 and so on formatted as "mmmm".
This is definitely the kind of thing i am looking for, i am trying to paste this formula and trying to adjust to the table i have, however its not bringing up the names.... How do i add my 'test' sheet (for GDPR purposes). so that you can see my layout? i have added my 'test' workbook, for you to see ?
=IFERROR(INDEX(Sheet1!$B$3:$B$12,SMALL(IF(MMULT(N(Sheet1!$E$3:$J$12=Sheet2!B$3),ROW($1:$6)^0)=1,ROW($1:$10)),ROW(1:1))),"")In your test file i've adapted the formula and it returns the intended results.
