Can anyone please help with this?

  • Thread starter Thread starter haas786
  • Start date Start date
H

haas786

Hi all! I desparately need help with an Excel problem. Here's an
example of what I'm trying to achive below:

In Cell B2 I have a drop down list which contains names of our
brokers.
In Cell C2 I have a drop down list describing types of deals (values
are C, A, X, or T)


Once the user chooses the name and type of deal, they would press a
button which would then take the values in B2 and C2 together and
find
matches in another sheet which contains 3 columns worth of data:
Name,
Type of Deal, and Deal Number (alphanumeric). There will never be 0
deal numbers or
more than 8 answers for any given Criteria (e.g. If I select Tom and
C, the resulting answer will have between 1 and 8 different Deal
Numbers.)


Once Excel or VBA finds these deal numbers, they need to be "placed"
or copied into cells B17 all the way down to B24 (if there are 8
deals
numbers.) So, if there are 2 deal numbers corresponding to Tom and C,
Excel will copy both deal numbers and paste them into B17 and B18.


I'm not sure if VLOOKUP or HLOOKUP or INDEX - MATCH function can do
this but if possible, please let me know.


Thank you in advance for your help!
 
How is the data on the other sheet setup?

Is it like this:

Tom...C...10
Tom...C...20
Tom...C...52
Tom...X...22
Tom...A...10
Tom...A...37

Or, is it random:

Tom...C...10
Bill...X...22
Sue...A...17
Bob...C...20
Tom...C...15

Biff
 
Create a macro button or a code that activates when you change the drop down
list and insert the following code.

Sub Haaas()

Range("B3").Activate

Do Until ActiveCell.Value = ""

ActiveCell.Copy
ActiveCell.Offset(14, 0).PasteSpecial
ActiveCell.Offset(-13, 0).Activate

Loop

End Sub
 
Create a macro button or a code that activates when you change the drop down
list and insert the following code.

Sub Haaas()

Range("B3").Activate

Do Until ActiveCell.Value = ""

ActiveCell.Copy
ActiveCell.Offset(14, 0).PasteSpecial
ActiveCell.Offset(-13, 0).Activate

Loop

End Sub










- Show quoted text -
Click to expand...

Hey AKphidelt ,

I don't know what angle you're taking and thanks for your help, but I
have no idea what you're trying to achieve with that code. Thanks for
trying though!
 
How is the data on the other sheet setup?

Is it like this:

Tom...C...10
Tom...C...20
Tom...C...52
Tom...X...22
Tom...A...10
Tom...A...37

Or, is it random:

Tom...C...10
Bill...X...22
Sue...A...17
Bob...C...20
Tom...C...15

Biff











- Show quoted text -
Click to expand...

Biff,

The data is random, similar to your 2nd example, and the last column
contains alphanumeric entries instead of just pure numbers.

Thanks...
 
Here's a small sample file that demonstrates this:

Haas(1).xls 16kb

http://cjoint.com/?fngLRAk3eZ

The formulas used are array** formulas. A single formula was entered in cell
B17 then drag copied down to B24.

Try making some selections from the drop downs and see what happens (note:
the sample data does not cover every possibility. I used just enough data
for demonstration purposes)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Note: if you have 1000's of rows of data then this might not be the best
approach.

Biff
 
Here's a small sample file that demonstrates this:

Haas(1).xls 16kb

http://cjoint.com/?fngLRAk3eZ

The formulas used are array** formulas. A single formula was entered in cell
B17 then drag copied down to B24.

Try making some selections from the drop downs and see what happens (note:
the sample data does not cover every possibility. I used just enough data
for demonstration purposes)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Note: if you have 1000's of rows of data then this might not be the best
approach.

Biff







- Show quoted text -
Click to expand...

Thanks a million - this did the trick! God bless you!
 
Back
Top