Is this possible??

[JimmieNeu]

I am creating a spreadsheet where I have created a drop down cell that contains a set list of options. Once this cell is filled out from the drop down list I have been copy-pasting from another worksheet within the same workbook. I imagine this task is possible with some sort of excel wizardry that I do not posses. Can anyone here help me?

Lets say cell F2 contains the drop down and cells A3:E4 are what are being copied from the other worksheet.

Thank you for your time!

 

[alansidman]

You have not provided enough details to clearly understand, but I am guessing as to what I think you need. Suggest you look at employing the Vlookup Function

Here is a link that will explain https://www.techonthenet.com/excel/formulas/vlookup.php

 

[Michael M]

what are the options in the dropdown ??
what are the different outcomes of each result in the dropdown??
Sounds like you will need a VBA solution....are you OK with that ??

 

[JimmieNeu]

This is what is in the drop down:

Classic

PQ

LG E

LG LS

Edge

ADV

LS

Avanti

AQ E

AQ LS

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

But these items will change. I am creating a spreadsheet for work... and do not have all the required information with me at this point. Eventually, there will be similar short phrases that will result in a large section of cells to copy and paste over. The cell group will be roughly 2 rows and 10 to 25 columns wide. The cells within will contain a variety of blank, alpha, and formulas. The formula will refer to a cell within the 2x5 cell groups. I am at the very early stages of creating this spreadsheet. I am sorry I do not have all the information that you have requested at this time.


Can you please educate me as to what a VBA solution is??
I am willing to learn and educate myself as long as I am pointed in the right direction.

Thanks again for your time!

 

[Michael M]

A VBA solution is a macro, either recorded by you OR written for / by you.
The written macro will always be shorter and more efficient.
If you record the steps in a macro someone may also be able to modify it to "clean" it up for you.
I'm guessing a Select Case Macro would be useful here if different copy / paste areas are required for each different item on the dropdown

 

[JimmieNeu]

I have more data to work from now... and realize the drop down menu triggering an auto-fill will not work. There are too many variables from build sheet to build sheet. So I have started creating a different method of breaking down the information needed so my crew can receive the information they need to get their job done in the most efficient way possible.


This is what I have so far...
https://1drv.ms/u/s!AnKqzpJeeI3VgSodwzD4d-qULE7n

I hope everyone can see the picture I linked to.

What I am trying to accomplish is the information entered in J17 to trigger K17, L17, M17, and N17 to be filled automatically. The information entered in Column J should be matched to the information bank entered into Column B. The formulas that need to be entered in columns K through N are in the information bank entered in Columns C through F.

To further explain...

Column A is blank.​

Column B contains the list of possible items that need to be built.
Columns C through F contain the formulas to know what size parts are needed.

Column I contains the length of the item. This number varies a lot and is entered manually.
Column J contains the description of the item. This also varies a lot and is entered manually.
I was hoping that Columns K, L, M, N could be filled automatically.

The information in Column J will determine what column's K through N should be.

 

[Peter_SSs]

Welcome to the MrExcel board!

I hope everyone can see the picture I linked to.

We can see the image but we cannot copy from it to test with. For the future, my signature block below has a link to better ways to show sample data that can be copied.

You will need to adjust the row ranges in the formula below to cover whatever rows are used in columns B:F but try this in K17 and copy across and down.

=IF($J17="","",INDEX(C$2:C$100,MATCH($J17,$B$2:$B$100,0)))

 

[JimmieNeu]

=IF($J17="","",INDEX(C$2:C$100,MATCH($J17,$B$2:$B$100,0)))

This does part of what I am asking... I received similar results when I tried VLOOKUP. The cell (K17) with the above formula did copy the displayed text in Column C, but not the formula in Column C that I need in Cell K17.



I will look into the better ways to show sample data in your signature as soon as I can.

Thank you for your time and effort,
JN
​

 

[Peter_SSs]

... but not the formula in Column C that I need in Cell K17.

Not quite sure why you need the formula in K:N when they are already in C:F, but this worksheet change code should copy the C:F cells and paste into K:N.
Test with a copy of your workbook. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range, cFound As Range
  Dim s As String
  
  Set Changed = Intersect(Target, Columns("J"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      s = c.Value
      If Len(s) Then
        Set cFound = Columns("B").Find(What:=s, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If cFound Is Nothing Then
          c.Offset(, 1).Resize(, 4).ClearContents
        Else
          cFound.Offset(, 1).Resize(, 4).Copy Destination:=c.Offset(, 1)
        End If
      Else
        c.Offset(, 1).Resize(, 4).ClearContents
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[JimmieNeu]

In columns A through F:
A: Blank
B: List of possible benches
C: Formula to determine the front panel size of the bench
D: Formula to determine the back panel size of the bench
E: Formula to determine the upper panel of the bench
F: Formula to determine the lower panel of the bench

I am using the bench list and the formula following it to keep myself from needing to type and retype the formula over and over for each order. Doing so will create the possibility of human error and none of us want that... we want to make it right the first time!!




Even though you did not completely understand the why, what you have suggested seems to be working!! Thank you so much!!! I am continuing to work on this to expand it to a fully functional worksheet. Once completed, I should be able to take 20 hours of work down to 2-3 hours. Even though I am investing a great deal of time into creating this, it will be worth it in the long run.

THANKS AGAIN!!