Question: I want to populate the adjacent cells in a sheet depending on the drop down box value.

In the workbook, I have a DATA sheet listing contact information, name, phone & email.

In the main sheet, you can select from the dropdown list a company name and then I would like to populate the adjacent cells with the information from the data sheet.

What would be the best method?

Cheers

Answer: 
You can use an anti-join:

```
select u.usernamte, d.date
from usertable u
cross join (select distinct date as dt from usagetable) d
left join usagetable ut on ut.username = u.username and ut.date = d.dt
where ut.username is null
order by d.date, u.username

```
The date range should be derived first between minimum and maximum dates or if a separate date table can be created. Then do cartesian products between date and user table and do left join with Usage table and look for null values in where clause. This how i did it:

```
create table UserTable(Username varchar(10));
create table UsageTable(Username varchar(10), UsageDate Date);

insert into UserTable values ('User1');
insert into UserTable values ('User2');
insert into UserTable values ('User3');

insert into UsageTable values ('User1','1-FEB-2022');
insert into UsageTable values ('User2','2-FEB-2022');
insert into UsageTable values ('User1','3-FEB-2022');
insert into UsageTable values ('User2','3-FEB-2022');

commit;

with rnge as (select min(UsageDate) min_date, max(UsageDate) max_date from UsageTable),
dt as (select generate_series(min_date,max_date,'1 day') as dt from rnge),
Usr as (select Username, dt from dt, UserTable)
select Usr.* from Usr left join UsageTable usg on usr.username = usg.username
and usr.dt = usg.UsageDate
where usg.username is null; 

```

Note: above sql works in postgres to generate the date range. however, you would use below to generate the date range in oracle. replace the dt table by below with some changes:
in oracle this is how date range is generated:

```
select
to_date('04-01-2016','dd-mm-yyyy') + lvl
from
(select level - 1 lvl
from
  dual
connect by
level <= (to_date('10-01-2015','dd-mm-yyyy') - to_date('04-01-2016','dd-mm-yyyy'))+ 1);

```