Combo Box to populate variable output range
I want to use a combox so someone can select an option which then causes a
separate range of cells to become populated. Here's an example: Combo box has 2 choices "New York" and "California." If someone selects New York, then in the output range B1:B3 they see "Albany, Manhattan, Syracuse." If they select California then the output range B1:B3 becomes "Sacramento, San Diego, San Francisco." I was able to do this by using hlookup in range B1:B3 based on a table I made off to the side, but I figure there must be a more intuitive way. Not to complicate this even more but what I'm ideally trying to do is for the output range in B1:B3 to actually expand if for example I chose "Texas" I'd want B1:B5 to be populated with "Austin, Dallas, San Antonio, Houston, Fort worth." |
Combo Box to populate variable output range
You could for example do this. Assuming you have only 5 possible values.
Then put in B1 "New York", B2 "Albany", B3 "Manhattan, B4 "Syracuse", B5 ="", B6 ="" Then put in C1 "California", C2 "Sacramento", C3 "San Diego", C4 "San Franciso", C5 ="", C6 ="". Then put in D1 "Texas", D2 "Austin", D3 "Dallas", D4 "San Antonio" D5 "Houston", D6 "Fort Worth". Make sure you put the equal signs in as in: ="". Assuming you have linked the output of your combo box to A1,and you want your resultant values to appear in H1:H5, select those cells and in H1 enter the array formula =OFFSET(B1,1,MATCH(A1,B1:D1,0)-1,5,1) and after entering the formula press Ctrl+Shift+Enter *not* just Enter. Then you can hide columns B, C and D. If you change your possible values to 10, for example, just select cells H1:H10, press F2 and change the 5 in the formula to 10 and press Ctrl+Shift+Enter. Tyro "DougL" wrote in message ... I want to use a combox so someone can select an option which then causes a separate range of cells to become populated. Here's an example: Combo box has 2 choices "New York" and "California." If someone selects New York, then in the output range B1:B3 they see "Albany, Manhattan, Syracuse." If they select California then the output range B1:B3 becomes "Sacramento, San Diego, San Francisco." I was able to do this by using hlookup in range B1:B3 based on a table I made off to the side, but I figure there must be a more intuitive way. Not to complicate this even more but what I'm ideally trying to do is for the output range in B1:B3 to actually expand if for example I chose "Texas" I'd want B1:B5 to be populated with "Austin, Dallas, San Antonio, Houston, Fort worth." |
Combo Box to populate variable output range
I should add that all empty cells in your values must contain ="" if they
are left blank, zeroes will show, not blanks. Tyro "Tyro" wrote in message ... You could for example do this. Assuming you have only 5 possible values. Then put in B1 "New York", B2 "Albany", B3 "Manhattan, B4 "Syracuse", B5 ="", B6 ="" Then put in C1 "California", C2 "Sacramento", C3 "San Diego", C4 "San Franciso", C5 ="", C6 ="". Then put in D1 "Texas", D2 "Austin", D3 "Dallas", D4 "San Antonio" D5 "Houston", D6 "Fort Worth". Make sure you put the equal signs in as in: ="". Assuming you have linked the output of your combo box to A1,and you want your resultant values to appear in H1:H5, select those cells and in H1 enter the array formula =OFFSET(B1,1,MATCH(A1,B1:D1,0)-1,5,1) and after entering the formula press Ctrl+Shift+Enter *not* just Enter. Then you can hide columns B, C and D. If you change your possible values to 10, for example, just select cells H1:H10, press F2 and change the 5 in the formula to 10 and press Ctrl+Shift+Enter. Tyro |
Combo Box to populate variable output range
Thank You! This works great and it opens so many more possibilities in what
I'm creating!!! Why do I need the "5" in my offset formula as the number of rows that I want in my "found response." It also works with a 1 when I was trying to figure this myself based on your suggested method. "Tyro" wrote: I should add that all empty cells in your values must contain ="" if they are left blank, zeroes will show, not blanks. Tyro "Tyro" wrote in message ... You could for example do this. Assuming you have only 5 possible values. Then put in B1 "New York", B2 "Albany", B3 "Manhattan, B4 "Syracuse", B5 ="", B6 ="" Then put in C1 "California", C2 "Sacramento", C3 "San Diego", C4 "San Franciso", C5 ="", C6 ="". Then put in D1 "Texas", D2 "Austin", D3 "Dallas", D4 "San Antonio" D5 "Houston", D6 "Fort Worth". Make sure you put the equal signs in as in: ="". Assuming you have linked the output of your combo box to A1,and you want your resultant values to appear in H1:H5, select those cells and in H1 enter the array formula =OFFSET(B1,1,MATCH(A1,B1:D1,0)-1,5,1) and after entering the formula press Ctrl+Shift+Enter *not* just Enter. Then you can hide columns B, C and D. If you change your possible values to 10, for example, just select cells H1:H10, press F2 and change the 5 in the formula to 10 and press Ctrl+Shift+Enter. Tyro |
Combo Box to populate variable output range
OFFSET returns a height and width, so "5,1" in the formula means 5 rows high
and 1 column wide. If you are returning a maximum of 5 cells spanning 5 rows in 1 column, as in your example, you must have the 5 to tell OFFSET how many rows (height) and the 1 to specify the width - 1 column to return. If your 5 entries were in one row spanning 5 columns the "5,1" in the formula would be "1,5" - 1 row high, 5 columns wide. You have to specify the height as 5 in this case. If you change the height to 1, then the first entry - e.g. Albany in the case of New York will be returned 5 times - assuming the array fromula spans 5 rows. Tyro "DougL" wrote in message ... Thank You! This works great and it opens so many more possibilities in what I'm creating!!! Why do I need the "5" in my offset formula as the number of rows that I want in my "found response." It also works with a 1 when I was trying to figure this myself based on your suggested method. "Tyro" wrote: I should add that all empty cells in your values must contain ="" if they are left blank, zeroes will show, not blanks. Tyro "Tyro" wrote in message ... You could for example do this. Assuming you have only 5 possible values. Then put in B1 "New York", B2 "Albany", B3 "Manhattan, B4 "Syracuse", B5 ="", B6 ="" Then put in C1 "California", C2 "Sacramento", C3 "San Diego", C4 "San Franciso", C5 ="", C6 ="". Then put in D1 "Texas", D2 "Austin", D3 "Dallas", D4 "San Antonio" D5 "Houston", D6 "Fort Worth". Make sure you put the equal signs in as in: ="". Assuming you have linked the output of your combo box to A1,and you want your resultant values to appear in H1:H5, select those cells and in H1 enter the array formula =OFFSET(B1,1,MATCH(A1,B1:D1,0)-1,5,1) and after entering the formula press Ctrl+Shift+Enter *not* just Enter. Then you can hide columns B, C and D. If you change your possible values to 10, for example, just select cells H1:H10, press F2 and change the 5 in the formula to 10 and press Ctrl+Shift+Enter. Tyro |
Combo Box to populate variable output range
Here's another formula you can use Assuming you have your values entered as
in the OFFSET formula, select H1:H5 and in H1 put =HLOOKUP(A1,B1:D6,{2;3;4;5;6},0) and press Ctrl+Shift+Enter. This will accomplish the same thing as the OFFSET formula. Tyro "DougL" wrote in message ... I want to use a combox so someone can select an option which then causes a separate range of cells to become populated. Here's an example: Combo box has 2 choices "New York" and "California." If someone selects New York, then in the output range B1:B3 they see "Albany, Manhattan, Syracuse." If they select California then the output range B1:B3 becomes "Sacramento, San Diego, San Francisco." I was able to do this by using hlookup in range B1:B3 based on a table I made off to the side, but I figure there must be a more intuitive way. Not to complicate this even more but what I'm ideally trying to do is for the output range in B1:B3 to actually expand if for example I chose "Texas" I'd want B1:B5 to be populated with "Austin, Dallas, San Antonio, Houston, Fort worth." |
All times are GMT +1. The time now is 06:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com