0

I have let's say one excel table (formatted as a table) in one sheet that has date column (in Column A) with some unique dates but starting from row ten (A10). Other rows are empty (because of the structure of table and the columns on the right).

What I wanted is to have that Date column on another sheet but in different cell, for instance C6 as starting point. And every time if I add something on the first sheet where unique dates are, I would like to have them auto populated on second sheet in defined column.

I tried on many ways but at the end I must do it manually, to drag it down to auto populate it... is there a way to do it automatically, to automatically expands every time one table is populated to get it on second?

Unique Data - Sheet 1

Sheet 2 - Auto Populate

What I used on to get the data on second sheet is:

=INDEX(Data['[Datum']];MATCH(0;COUNTIF($C$6:C6;Data['[Datum']]);0))  as an array formula.

Or if I direct reference:

=Sheet1! XYZ   still can't be auto populated and must manually drag formulas down..

First thing is that number 00.01.1900 (on C7) which is wrong, can't get rid of it, and secondly table doesn't auto populate when I add something on Sheet 1.

MmVv
  • 553
  • 6
  • 22

1 Answers1

1

I added 1 condition to exclude the blanks (resulting in 00.01.1900) and added IFERROR, so you can drag the formula down not resulting in errors and resulting in updated values when your table gets updated with new values.: =IFERROR(INDEX(Data['[Datum']],MATCH(1,(COUNTIF($C$6:$C6,Data['[Datum']])=0)*(Data['[Datum']]<>""),0)),"")

Is this what you're looking for?

PS In office365 it's much easier: `=UNIQUE(FILTER(Data['[Datum']];Data['[Datum']]<>"")

P.b
  • 8,293
  • 2
  • 10
  • 25
  • something is wrong or missing in your formula (upper one). It gives me blanks just. – MmVv Jan 10 '21 at 16:16
  • I made a mistake when changing the data I used into yours. How about now? – P.b Jan 10 '21 at 16:26
  • Now works! But this is semi-automatic...i still need to drag my formula on another table to be populated or i can drag up to XXX and leave it for being populated. I guess I found a solution in Power Query for this part....but this formula of you will be used on another place =) I am avoiding VBA because of sharing the files. – MmVv Jan 10 '21 at 16:43
  • That was the idea; drag it down further than the data in your table and the data will grow along with the data. Glad out can use it. – P.b Jan 10 '21 at 17:42