| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
| indirect formula | Excel Worksheet Functions | |||
| Indirect Formula | Excel Discussion (Misc queries) | |||
| Indirect Formula | Excel Worksheet Functions | |||
| indirect formula | Excel Worksheet Functions | |||
Linear Mode
