Yet another issue beyond my understanding...Drop Down lists and Auto-Populate

[DrGnBld]

Hello again all, i have yet another issue that is confounding me and no amount of googling has come close to helping me make sense of it. Essentially what i am trying to do is have cells AM42 through AM48 auto populate with the corresponding target values from sheet 1 based upon a selected JMF from the drop-down list in cell H7. I tried writing the data 2 different ways on sheet 1 and i still cant figure out how to make it tie in to each corresponding JMF. Eventually i would like to add more to this, but im starting with three just as a test subject. As usual, im providing the attachment for but feel sheet 1 needs some explaining. Columns D E and F are the data im trying to auto-populate with based upon each JMF number (D being for 16009, E for 16006 and F for 16005). The data spread out across multiple columns in the exact same, just listed different because i thought that would help. Only made my head hurt more. Please help!

[Glenn Kennedy]

Merged cells - the work of Satan!! Why do you feel the need to merge columns and rows instead of resizing them? They are going to make your life so difficult.

Anyhow. What are your expected answers in EACH cell for H037716006?

[DrGnBld]

The sheet titled QC gradation isnt actually my creation, it was a friend of mines. But to answer the expected answers in each cell if i select H037716006 are:AM42=Blank, AM43=100, AM44=99, AM45=94, AM45=85, AM46=76, AM47= Blank, AM48=50, AM49=36, AM50=28, AM51=20, AM52=12, AM52=8, AM53=4

Or, Column E on sheet 1, rows 2-15

[Glenn Kennedy]

Unfriend him/her. (S)he deserves it!!!!!

[Glenn Kennedy]

See the problem that has been created for you. You refer to cell AH 43... It doesn't exist It has been merged with AH42 to make th cell look deeper. this nonsense makes it much more complicated to make formulae that have been copied down. Indeed cell AH42 is actually 10 cells (5 across and two down) that have been merged for absolutely no good reason at all.

I am going to de-merge the rows (you can - and should - demerge the columns yourself).

[DrGnBld]

I didnt even catch that, i honestly never paid that much attention to that column to notice they dont go numerically anymore.

[Glenn Kennedy]

Unfortunately, if I demerge the rows, the columns demerge too and then the whole thing looks like a dog's breakfast,

|Try this in AH42, copied down.

=OFFSET(Sheet1!$C$2,(INT(ROWS($1:1))-1)/2,MATCH($H$7,Sheet1!$A$2:$A$15,0),,)

[DrGnBld]

So taking that into account, even though you said your going to unmerge them, the correct answer would have been(if i paid any attention whatsoever):AM42=Blank, AM44=100, AM46=99, AM48=94, AM50=85, AM52=76, AM54= Blank, AM56=50, AM58=36, AM60=28, AM62=20, AM64=12, AM66=8, AM68=4

[DrGnBld]

That code worked beautifully. Thank you so much. My only question regarding that code, if i were to add the rest of the data i actually have, where would i modify the code at to auto-populate the new data?

[Glenn Kennedy]

Hi. I've been away all day & am only able to reply now. You would need to adjust this bit: Sheet1!$A$2:$A$15 to suit. the rest should be OK. However, foor some reason or other you have a data validation (dropdown) list in those cells. I would get rid of that. the potential for a mes-up is huge.

Otherwise it should all run OK.