Here's my problem. I need to extract the information from table 1 and 2 to the solution table. Table 2 is pretty easy using Hlookup but table 1 is kinna tough since the section is across 3 columns. And I can't put them all in a single row. Would Sumproduct work? Can I do a combination of matching the section value from Solution Table to the section value in Table 2? But how do I extract the cross section information from the row of Disneys? Would the formula be something like:
Here's part of an approach. I don't understand how the combination of a name and a letter picks out the two numbers you want to multiply. But it looks as though the letter determines the row of the data point in Table 1. Do the letters have to be text? If numbers, you could use something like int((value+(n-1))/n) to reduce them to row numbers, where n is the number of rows in the table. Try it on the series 1,2,3... with n=3.
The main problem is with the letter lookup - you have to lookup in the entire array A4:C9. although there are ways to do this, I thought the easiest approach would be to concatenate the letters in each row into a single value, then do & index / match...(see d4:d9 in the example).
-to find the position of the letter in the new strings
- to match the charachter name to the right column in the data table
- put them together to return the correct value from the data table
- return the value from the second table.
See the example:
EDIT: removed 'cos too big...
Paddy This message was edited by PaddyD on 2002-09-24 14:58
Wow, cool! Let me try this out and let you guys know what happened. Also after I look more into this problem, I found out I have sooo many more conditions to tag on that a general formula will consist of more If's than the cow come home. Since there was a time crunch, I just did a quick fix using hardcode cell references. I'll work more on this problem and keep you updated. Thanks for all your helps. What a wonderful board!
This works GREAT! I never thought of approaching the solution in this manner. I have learned not only technical skills on this board but more importantly, the way to approach problems. Thanks for all the help, especially PaddyD.