Hi Experts,



See attached file. I need to tabulate the time vertically with dates as highlighted in attached. Data will add to left hand side horizontal table and I need to automatically tabulate these details to right hand side highlighted table. Which formula I've to use for this? Note that, the dates and times will add at left. Can you please advise.

Thanks in advance for your help.

Regards,
Sunil Thomas

Attached Files

Flip Value Date wise.xlsx (13.3 KB, 8 views)

I defined tables as Tables - so all formulas will adjust automatically whenever new data is added into source table.

In both original and added Tables are some helper columns I colored differently. You can hide those to avoid confusion, as user don't need to see them at all.

To apply automation, I defined some Named Ranges.

Currently TimeTable has 99 pre-prepared rows. It will be enough for INT(99/6) = 17 dates with current number of times in source table. When this limit will be overrun, you simply expand TimeTable for some number of rows.

You can set the autofilter for Time column in TimeTable to 'not empty'. This filters out both empty rows at bottom of table, and rows where time field in source for this date was empty.

Attached Files

Flip.xlsx (22.1 KB, 9 views)

Thank You for this quick response and our time. It is working fine. Thanks again.