Dependent List "Index" Problem

  • Thread starter Thread starter Benjamin
  • Start date Start date
B

Benjamin

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?
--
 
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.
 
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
 
Back
Top