INDIRECT Formula
 

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.

INDIRECT Formula
 

Try the below formulas and copy down as required. The named ranges are
Room1,Room2 and Room3

In A3
=IF(B3="","",ROW(A1))

In B3
=IF(COUNTA(INDIRECT($B$1))=ROW(A1),INDEX(INDIRECT ($B$1),ROW(A1)),"")

If this post helps click Yes
---------------
Jacob Skaria


"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.

INDIRECT Formula
 

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.

INDIRECT Formula
 

Hello Mr. Jacob,

Thanks so much, it works perfectly.

May I ask one more thing, is there any way to create a formula as auto
border based on the number of rows?

I mean, if room1 is a3:c15, then when i click the print preview the border
is up to that only, then if room2 is a3:c30 then when i click the print
preview the border is up to that range only.

many many thanks.


"Jacob Skaria" wrote:

Try the below formulas and copy down as required. The named ranges are
Room1,Room2 and Room3

In A3
=IF(B3="","",ROW(A1))

In B3
=IF(COUNTA(INDIRECT($B$1))=ROW(A1),INDEX(INDIRECT ($B$1),ROW(A1)),"")

If this post helps click Yes
---------------
Jacob Skaria


"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.