I am using Excel 2007 to create a form. What I would like to happen is that based on the value entered in two cells, that a search is done on a second worksheet to find the related value and have that automatically populated in another cell on the first work sheet. Better explanation:

I am trying to get the form (sheet1) to automatically populate the appropriate value based on what paper form was used (cell E19) and how many hits were made (cell H19). Depending on what paper form was used and how many hits were made, a specific score or points are assigned. This score should populate in cell J19 are on sheet2.

Currently, the person is supposed to enter the form name. Would it be more advantageous if I used a list to eliminate human error and misspelling? This question is of course secondary to what formula I need to enter in cell J19 to get the results that I am looking for. Please let me know if you need any additional info to help me.

Any help is SO appreciated and welcomed!!!

Thanks,

April

From your description, with no example of your data, I thinkthis is what your looking for:If your data looks like:

Sheet number 4of workbook:A B C 1) Critera 1 Critera 2 Return Value 2) 3) ALPHA ONE 20 4) BETA TWO 21 5) GAMMA THREE 22 6) DELTA FOUR 23 7) EPSILON FIVE 24 8) ZETA SIX 25 9) ETA SEVEN 26 10) THETA EIGHT 27 11) IOTA NINE 28 12) KAPPA TEN 29On

Sheet number 1of workbook,

in cell A13 enter first search critera: GammaOn

Sheet number 1of workbook,

in cell B13 enter second search critera: ThreeOn

Sheet number 1of workbook,

in cell C13 enter this formula:=SUMPRODUCT((Sheet4!$A$3:$A$12=A13)*(Sheet4!$B$3:$B$12=B13)*(Sheet4!C3:C12))

You should get the return value of 22

MIKE

Mike: Please send me your email address so that I can send you the form and a more detailed example of my problem.

Thanks,

April

Please send me your email address so that I can send you the form and a more detailed example of my problem.The forums are a place for everyone to learn, so you sending me the form kinda defeats the purpose of the forums, does it not?

Let's see if we can solve your problem on line first, OK?

What I would like to happen is that based on the value entered in two cells,

that a search is done on a second worksheet

to find the related value and have that automatically populated in another cell on the first work sheet.That's what I gave you,

just modify the cell numbers on Sheet 1 to:On Sheet number 1 of workbook, in cell E19 enter first search criteria: Gamma

On Sheet number 1 of workbook, in cell H19 enter second search criteria: Three

On Sheet number 1 of workbook, in cell J19 enter the formula:

Here's a breakdown of the formula:Sheet2!$X$X:$X$X=E19 is the range of cells on sheet 2 you want cell E19 to check, no blank cells allowed.

Sheet2!$X$X:$X$X=H19 is the range of cells on sheet 2 you want cell H19 to check, no blank cells allowed.

Sheet2!XX:XX is the range of cells on sheet 2 you want to pick from when the above two match, no blank cells allowed.

=SUMPRODUCT((Sheet2!$X$X:$X$X=E19)*(Sheet2!$X$X:$X$X=H19)*(Sheet2!XX:XX))

If you post a small sample of your second worksheet, then modifying the formula

should not be very difficult.Unless there is more to this problem that you haven't revealed.

Read this How-To before you try to post your data:

http://www.computing.net/howtos/sho...MIKE

On Sheet1, in cell G11 I created a list that consists of the values E5 and E6. Depending on which one of these is chosen will determine which list to do a look up on in Sheet3 which contains physical fitness scores. E5 scores vary greatly from those for E6. Depending on which "list" is used, E5 or E6, the value that is calculated in cell H16 (which comes from adding cells E15+G15+I15) will equal a value on Sheet3. For example, if an E5 scores 215 then J16 should populate with a value of 75. If it is an E6, then J16 should be 31. This is based on the list on Sheet3 which has Cell A1:B1 merged and the text E5. Cell A2 is Score, and cell B2 is Points. Cells A3/B3 - A123/B123 has the allowed score/point computation. The same thing occours in cells D1:E1 (merged with E6 as the text) and D2/E2 being a replica of A2/B2 with the exception that the points in column E vary from the points in column B. My next issue is still based on which value is selected for cell G11. In cell E19, a list consisting of various forms is available. Depending on which form, and how many points were made, then cell J19 should be populated based on the information found on Sheet2. For example, if it is E5, and form A was used and 32 points were made, then J19 should populate with 100. If it were an E6 and form D was used, and 36 points were made then J19 should be 82.

I suggested the email so that I could send you the exact form. I hope that my explanation here is better than my first (and second) attempt for help.

Again, your help is definitely appreciated!

April

On Sheet1, in cell G11 I created a list that consists of the values E5 and E6.

Depending on which one of these is chosen will determine which list to do a look up on in Sheet3

Depending on which "list" is used, E5 or E6, the value that is calculated in cell H16 will equal a value on Sheet3.So you will need some type of =IF() statement to first determine which list to use, correct?

ADDED:

This is based on the list on Sheet3 which has Cell A1:B1 merged and the text E5.Things get problematic with merged cells.

MIKE

Ask Your Question

Weekly Poll