First define the ROOM1, ROOM2, ROOM3
Assumed
Range("E1:E5") has details of Room1, define this range as room1

Range("F1:F6") has details of room2, define this range as room2

Range("G1:G6") has details of room3, define this range as room3

You have validation in Cell B1

now in cell B3 put this formula and drag it.

=IF(ISERROR(INDEX(INDIRECT($B$1),ROW(A1),0)),"",IN DEX(INDIRECT($B
$1),ROW(A1),0))


For Checklist template

same as above, for Sl.no.
use this in cell A3 & drag it down

=IF(B3<"",COUNTA(B$3:B3),"")


On Oct 26, 11:43*am, exploringmacro
wrote:
Hi,

Good day, can anybody help me, actually I dont know how to describe the
problem, I will just give the things that I've done.

SCENARIO 1:

I'm making a PUNCH Lists / CHECK Lists, but the items are per locations. see
below example

A1: LOCATION * * * B1: <drop down lists using Data Validation
* * * * * * * * * * * * * * * * * * * *ROOM1
* * * * * * * * * * * * * * * * * * * *ROOM2
* * * * * * * * * * * * * * * * * * * *ROOM3

What I want is, when I choose the location on B1 <eg: ROOM1, all the items
in ROOM 1 will show on the B3, B4,B5,B6 ....B10,

I've define the name accordingly

ROOM1 * * * * * * * * *ROOM2 * * * * * * * * * * ROOM3
Bed * * * * * * * * * * * Bed * * * * * * * * * * * * * Bed
Table * * * * * * * * * *Table * * * * * * * * * * * *Table
Headboard * * * * * * Headboard * * * * * * * *Headboard
Cup * * * * * * * * * * * Sofa * * * * * * * * * * * * Refrigerator
Spoon * * * * * * * * * *Electricfan * * * * * * * *Computer
* * * * * * * * * * * * * * *LCD TV * * * * * * * * * * Table Lamp
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *Sofabed

CHECKLISTS TEMPLATE

A1: LOCATION * * * B1: <drop down lists using Data Validation
* * * * * * * * * * * * * * * * * * * *ROOM1
* * * * * * * * * * * * * * * * * * * *ROOM2
* * * * * * * * * * * * * * * * * * * *ROOM3

If I choose ROOM1, then all the Items in Room1 will show as follows

A2: SN * * * * * * * * * B2 : ITEMS
A3: 1 * * * * * * * * * * *B3: *Bed *
A4: 2 * * * * * * * * * * *B4: *Table
A5: 3 * * * * * * * * * * *B5: *Headboard
A6: 4 * * * * * * * * * * *B6: Cup

and the same as ROOM2 and ROOM3, I know that I can use =indirect(b1) in the
Data Validation as a drop down lists, but we dont want to use the drop down
lists.

Please help.

thank you.