0

I have an excel worksheet similar to the following -

  • Col A - Clients name,
  • Col B - Col D - Cient's info such as address, Phone no etc and
  • Col E - Date of next meeting.

I want to create a table such that

  • Col A will be Date of Next meeting,
  • Col B will be Clients name,
  • Col C - Col E will be Clients info.

The condition is that everytime I open the table it will start from 'today' or the closest date (next future date in the list) as in Col A with the Date of next meeting and the table holds data for the next 30 days. Any idea how to move forward?

1 Answer 1

2

Sort the source table by the Date of next meeting.

Then for the first column of your table, you may refer to the following array formula and drag down to fill the rest(Remember to press Ctrl+Shift+Enter to make it an array formula. You may need to adjust the data range D2:D9 according to your data source.):

=TEXT(INDEX(E:E,SMALL(IF(($E$2:$E$9>=TODAY())*($E$2:$E$9<TODAY()+30),ROW($E$2:$E$9),4^8),ROW(E1)))&"","[>0]m/d/yyyy;")

enter image description here For the other three columns, use the array formula below(Press Ctrl+Shift+Enter to make it an array formula. Adjust the data range if necessary.):

=INDEX(A:A,SMALL(IF(($E$2:$E$9>=TODAY())*($E$2:$E$9<TODAY()+30),ROW($E$2:$E$9),4^8),ROW(E1)))&""   

Drag down and right to fill the table. enter image description here

If you would like to do it in a separate sheet, for example, the original data is in Sheet1 and you want to create the new table in Sheet2, just modify the data range in the formulas, and press Ctrl+Shift+Enter to make it an array formula:

=TEXT(INDEX(Sheet1!E:E,SMALL(IF((Sheet1!$E$2:$E$9>=TODAY())*(Sheet1!$E$2:$E$9<TODAY()+30),ROW($E$2:$E$9),4^8),ROW(Sheet2!E1)))&"","[>0]m/d/yyyy;")

enter image description here

=INDEX(Sheet1!A:A,SMALL(IF((Sheet1!$E$2:$E$9>=TODAY())*(Sheet1!$E$2:$E$9enter image description here

7
  • Thanks a ton Yuki. Can we do it in a separate worksheet too?
    – Neel
    May 30, 2018 at 11:24
  • Did you try the formula? It shows error though!
    – Neel
    May 30, 2018 at 15:18
  • Yes, we can do it in a separate worksheet, but you need to change the data range in the formula.
    – Yuki Sun
    May 31, 2018 at 1:30
  • Thanks Yuki. I am new in Excel. Can you please tell me how can I add entire column instead of D2:D9 in the formula? Since my list of Clients name will increase over time, how can I select the entire column so that the formula applies to all the cells of D containing data.?
    – Neel
    May 31, 2018 at 1:40
  • You can change 9 to a large enough number, like 100000.
    – Yuki Sun
    May 31, 2018 at 1:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .