Pick table from drop down and populate on other sheet

[siva venkata]

Dear Friends,

From the excel spread sheet, while selecting from the drop down list (Deployment Model) from attached Workout sheet, the corresponding table should be highlighted from given tables and populate the same table in the Recommendation sheet from the same excel.

Please help with the steps to achieve with Excel. I am attaching sample sheet for your reference and easy understanding about the requirment.

Activities to be carried out:

Go to Workout (Sheet) --- > Select from drop down list (C2) -- > Based on Projection Volumes, list ----> It should highlight the corresponding table from below --> Pick up that particular table and Populate the same in Recommendation sheet (with out formulas, only values should come in tabular format)

Thanks in advance.

[aydeegee]

Hi sira venkata

I am confused by D2. The formula says C2, but then there is data validation. Further confusion when the data validation list is #REF!

Column C contains lots of #REF!s - not easy to understand what is going on.

Based on Projection Volumes, list ----> It should highlight the corresponding table from below

where exactly is the "corresponding table"?

To sum up - I would love to help, but have no idea where to start.

Regards
Alastair

[siva venkata]

Dear Alastair,

Thanks for your reply. Let me put in this way for better understanding.
I am sending you the modified samplefile excel sheet.

There will be two columes (C,D) for As on date volumes and Projection volumes data.

Projection volumes cells (Cell D) input changes based on the As on date volumes (Cell C) selection parameter.
You can ignore (#REF!) from the tables. Those are the formulas used to calculate based on other inputs.

Now, the requirement is, based on the Deployment Model required selection, Cell D4 will up updated with an option. Now, when the selection has made in Cell D4, it has to pick up or highlight the particular tables given below. i.e., I have given with red font and yellow background of each option across the table.

After highligting the required table (based on selection), that particular table should push it to or appear in the Recommendation sheet with in the same excel and in the same tabular format.

Hope it is clear on the requirement. If not please let me know.

Thanks in advance.

Regards,
SV

[aydeegee]

Hi sira venkata

I believe that I may be beginning to understand, but don't get your hopes up.

I am still confused by D3. The formula says =C3 but there is a drop down menu. Will D3 ever be different to C3?

D4 has a drop down with a selection of 5 items but you have only shown 3 areas?

I believe that:
if D4 is "Basic Hardware" then you require that B8:F12 be copied to "Recommendations"
if D4 is "Active/Active with Database on O/S Cluster" then B14:F21 needs to be copied.

If this is so, then what get copied if D4 is "O/s clustering ...."?

Regards
Alastair

[siva venkata]

Hi Alastair,

Yes, D3 will never be different from C3. What every option I select in C3 (As on date volumes), it will update in D3 (Projection volumes). Purposfully I created drop down to show it has identical list.

I have updated the samplefile1 excel sheet with the drop down list, matching to the tables, for your understanding.

I believe that:
if D4 is "Basic Hardware" then you require that B8:F12 be copied to "Recommendations"
if D4 is "Active/Active with Database on O/S Cluster" then B14:F21 needs to be copied.

Your understanding to the above is correct for the flow. While copying the particular tables to the "Recommendations" sheet, only the values has to go by hiding the formulas. If that is possible it will be great.

Best Regards,
SV

[aydeegee]

Hi sira venkata

Here's the file - the macro is working exactly as I want it to. Does it do what you want?

Regards
Alastair

[siva venkata]

Hi Alastair,

Excellent . This is what exactly I am looking to achieve.But, here the thing is I am not getting the exact format in "Recommendation" sheet as of "workout" sheet. i.e table format, centering context and numbers etc., If we can get the exact replica as of the tabular format in "Recommendation" sheet it will be helpful.

Secondly, Please provide me the detailed steps, to carry out to achive this ("Post to Recommendation Ctrl+Shift+Q) for setting up button, forumlas to be used, so that I can understand and fine tune as per the requirment.

Thank you once again.

Best Regards,
SV

[aydeegee]

Hi sira venkata

I have updated the macro to take across the formatting.

To see the macro press Alt+F8

(from this screen you may select options, which is where the "Q" is set for the Ctrl+shift+Q)

Use "Step Into" to see the macro. I have annotated the macro so you can see what is going on.

To make the button, just select any shape and place it on the spreadsheet, right click to "Assign macro".

Have fun fine tuning!

Regards
Alastair

[siva venkata]

Thank you very much Alastair for your help on the request.
I was travelling last week, so it was bit delay in responding.

I will work on this based on your recommendations, and surely will come back.

Thank you once again.

Regards,
SV

[siva venkata]

Hi Alastair,

Came back after long time to the same thread. I have fine tuned the macro according to my need. I have created button assigned macro and done everything.

But while running or clicking on the run button, I am getting an error where I am not getting any output populated on the Sizing Recommendations sheet.
Run-time error '1004': Method 'Range' of object'_Global' failed

I am pasting the macro below for your reference:

Sub PostRec()
'macro written by Alastair Guthrie 17 April 2014

Sheets("Core workout").Select
rec = Cells(4, 4) ' The setting in D4 determines which rows to send for Rec.
If rec = "Basic Hardware" Then
rng1 = "B42:F47" 'Pick up 2 ranges to deal with (a) sending values only to the Rec. and (b) formatting to go to Rec.
rng2 = "B43:E47"
Else
If rec = "Active/Active with Database on O/S Cluster" Then
rng1 = "B62:F71"
rng2 = "B63:E71"
Else
If rec = "Active/Active with Database on Oracle RAC" Then
rng1 = "B87:F96"
rng2 = "B88:E96"
End If
End If
End If

Range("Sizing Recommendation!A4:e4000").Delete Shift:=xlUp 'Get rid of old dataRange(rng1).Copy ## When I am debugging getting higlighted with yellow color on this line##
Sheets("Sizing Recommendation").Select
Cells(2, 1).Select
ActiveSheet.Paste ' Paste everything, including formulas and formats
Range("'Core workout'!" & rng2).Copy
Cells(3, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'posts values only to non-merged cells
Cells(4, 1).Select

End Sub


Appreciate your help in fixing this issue.

Regards,
SV

[aydeegee]

Hi siva venkata

If you will upload a copy of your revised workbook, I will try to see what is going wrong.

Regards
Alastair