If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dependent List "Index" Problem
I'm using the template from:
http://www.contextures.com/xlDataVal15.html I'm having a problem getting the pulldown menus to move to say start at Cell G8 and the dependent at H8 as I have my Data Entry sheet formatted differently. As I don't want the drop downs on A2 and B2. I'm entering the "names" on G8 and H8 but no luck.... Can someone help me understand how to tweak the formulas to allow for this? -- |
#2
|
|||
|
|||
Dependent List "Index" Problem
I'm guessing its this formula that's causing problems:
=Lists!$A$2:INDEX(Lists!$A:$A,COUNTA(Lists!$A:$A)) For you scenario, change it to: =Lists!$G$8:INDEX(Lists!$G:$G,COUNTA(Lists!$G:$G)+ 6) assuming you're still using headers. If not, change the +6 to a +7. To help break-it-down: The goal of formula is to create an array encompassing the data you want. YOu know the starting point, its the end point that is unknown. Thus, we start with G8, and setup a formula to find other end. INDEX lets you give an array and then a row number (optional column number as well). Thus, we want to look at column G, and the row number we want is to be determined. For this, COUNTA counts how many cells are already used. This is where you may need to modify formula I gave, depending of if G1:G7 have data in them. If there are blanks, you will need to add an integer to help offset the count. Looking through the rest of the template, you'll see that INDEX and COUNTA appear sevearl times. These will need to be adjusted accordingly. The last one is MATCH, which returns the row/column number of a found string withing an array. For more information on these functions, you can try looking in the XL help file. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Benjamin" wrote: I'm using the template from: http://www.contextures.com/xlDataVal15.html I'm having a problem getting the pulldown menus to move to say start at Cell G8 and the dependent at H8 as I have my Data Entry sheet formatted differently. As I don't want the drop downs on A2 and B2. I'm entering the "names" on G8 and H8 but no luck.... Can someone help me understand how to tweak the formulas to allow for this? -- |
#3
|
|||
|
|||
Dependent List "Index" Problem
Thank you so much. I eventually put all the formulas in Notepad and started
at them a while. And figured out what your saying. Got it working, thanks for taking the time. -Ben "Luke M" wrote: I'm guessing its this formula that's causing problems: =Lists!$A$2:INDEX(Lists!$A:$A,COUNTA(Lists!$A:$A)) For you scenario, change it to: =Lists!$G$8:INDEX(Lists!$G:$G,COUNTA(Lists!$G:$G)+ 6) assuming you're still using headers. If not, change the +6 to a +7. To help break-it-down: The goal of formula is to create an array encompassing the data you want. YOu know the starting point, its the end point that is unknown. Thus, we start with G8, and setup a formula to find other end. INDEX lets you give an array and then a row number (optional column number as well). Thus, we want to look at column G, and the row number we want is to be determined. For this, COUNTA counts how many cells are already used. This is where you may need to modify formula I gave, depending of if G1:G7 have data in them. If there are blanks, you will need to add an integer to help offset the count. Looking through the rest of the template, you'll see that INDEX and COUNTA appear sevearl times. These will need to be adjusted accordingly. The last one is MATCH, which returns the row/column number of a found string withing an array. For more information on these functions, you can try looking in the XL help file. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Benjamin" wrote: I'm using the template from: http://www.contextures.com/xlDataVal15.html I'm having a problem getting the pulldown menus to move to say start at Cell G8 and the dependent at H8 as I have my Data Entry sheet formatted differently. As I don't want the drop downs on A2 and B2. I'm entering the "names" on G8 and H8 but no luck.... Can someone help me understand how to tweak the formulas to allow for this? -- |
Thread Tools | |
Display Modes | |
|
|