Copy a row...

[niloc85]

Hi,

Im looking for some help as I dont use excel much!

I would like to have a formula/script to do the following...

I have a cell (C3) within sheet one which will contain a text value based upon a dropdown list. That text could be for example A, B or C. Based upon that text value of A, B or C within C3 I would like to copy a row from sheet two C20-30 to sheet one location C20-30.

Is this possible in a easy enough way?

Thanks if your able to help.

 

[niloc85]

Sorry I was planning on attempting to do this myself hence no explanation, but couldn't make sense of the code!

So there are a few parts to this so I will start from the top of the sheet where I have step 1.

1. (Sheet-Capacity) The user from a drop down will select a value from the dropdown in cell "Capacity" D4. That value will be taken from Sheet "Inputs" B4-6 and will contain either aggressive, moderate or conservative. Each of these conditions have a value next to them aggressive 40%, moderate 50% or conservative 60%. That value needs to be copied from "Inputs" either of E4-6 to Capacity E4 depending on what is selected in D4.
2. Same applies for the other cells from D5-7, the cell names in "Capacity" refer to the same names in "Inputs" with the same type of action to occur with coping the percentage from "Inputs" to "Capacity"
3. (Sheet-Capacity) Cell "Capacity" D4 and E4 have a drop down list, this dropdown list takes the data from "Application" B6-26 for "Capacity" D4 and C7-26 for "Capacity". Again what is specified in "Capacity" D4 dictates what is copied from "Application" C7-26 to E4.

I hope I have explained ok.

 

[niloc85]

Also latest excel....

https://www.dropbox.com/s/gijcwfd7m6s6g7j/Leys Calculator With Change Event_neil edit.xlsm?dl=0

 

[L. Howard]

Have a look at the D4 to E7 range.

Used VLOOKUP in the E's.

Does that work?

https://www.dropbox.com/s/c4gjxduzr9y06u4/Leys Calculator With VLOOKUP.xlsm?dl=0


Your explanation seems to want to return a value to E4 based on what is selected in D4, however later you want to use the drop down in E4 to return another value somewhere else.

Is the D4 - E7 cells preforming correctly? If so, what are next steps? Else, what must be done with D4 - E7?

Also, had to UNMERGE some cells on sheet Inputs.

Howard

 

[niloc85]

D4 to E7 all looks to work as expected. That covers what I meant in point 1 and point 2.

Just the points in number 3 now, I think it will be the same type of action with a VLOOKUP right?

Also I think as I have added some more fields I have broken the capacity O9:Y18

 

[L. Howard]

D4 to E7 all looks to work as expected. That covers what I meant in point 1 and point 2.

Just the points in number 3 now, I think it will be the same type of action with a VLOOKUP right?

Also I think as I have added some more fields I have broken the capacity O9:Y18

3. (Sheet-Capacity) Cell "Capacity" D4 and E4 have a drop down list, this dropdown list takes the data from "Application" B6-26 for "Capacity" D4 and C7-26 for "Capacity". Again what is specified in "Capacity" D4 dictates what is copied from "Application" C7-26 to E4.

Ref: Point 3.
You cannot have a drop down in D4 to work for Application" B6-26 because it has a drop down for Aggressive, Moderate, Conservative, nor can you have a drop down in E4 because that is where a formula is. Perhaps that was a typo?

The O9:Y18 has already been fixed on the linked copy I posted. Adjusted the code to move the returns over two columns, from M to W and now O to Y.

Next steps for the Application" B6-26 ??

Howard

 

[niloc85]

Thank god your excel skills are better than my explanation attempts!

3. (Sheet-Capacity) Cell "Capacity" D11:18 and E11:18 have a drop down list, this dropdown list takes the data from "Application" B6-26 for "Capacity" D4 and C7-26 for "Capacity". Again what is specified in "Capacity" D11:18 dictates what is copied from "Application" C7-26 to E11:18.

 

[L. Howard]

Go to my Post #23 and download the link again.

Check the Capacity sheet cells D11:E18.

What adjustment are needed, as I do not think this is the correct fix.

There are two Headers in the drop down which will produce a lookup return of 0 in column E.
If this is the correct list to have in the drop downs, we should remove the headers from the column B on Appliation.

Howard

 

[niloc85]

Go to my Post #23 and download the link again.

Check the Capacity sheet cells D11:E18.

What adjustment are needed, as I do not think this is the correct fix.

There are two Headers in the drop down which will produce a lookup return of 0 in column E.
If this is the correct list to have in the drop downs, we should remove the headers from the column B on Appliation.

Howard

This is correct. You are right however the heading will cause an issue with the formula.

 

[L. Howard]

This is correct. You are right however the heading will cause an issue with the formula.

Download again.

See if the small format change on Sheet Application is suitable.

Howard