[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Demo Macro Script

Posted on 2013-11-26
5
Medium Priority
?
262 Views
Last Modified: 2013-11-26
EE Pros,

I'm putting together a Demo tab so as to pre-load information concerning a particular ability to demonstrate using 3 columns of data.  Upon selecting the Demo to run, the data should automatically populate the other two tabs.  Change the demo, and the data should also change.

That's it!  Thank you in advance.

B.
Demo-Script.xlsm
0
Comment
Question by:Bright01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39678422
Copy this to F9 of customer box and from there copy it to each of the other boxes

=INDEX(Demos!$E$8:$G$13,MATCH(E9,Demos!$C$8:$C$13,0),MATCH(Demos!$D$3,Demos!$E$6:$G$6,0))
0
 

Author Comment

by:Bright01
ID: 39678499
Ssaqibh,

Thanks for the quick reply.  I'm looking for a macro ("Upon selecting the Demo to run...."), that when fired, will populate the Customer and Pricing Tabs.  This is because, without firing the Macro for the Demo, the user can enter their own information (so I can't use a formula in the cell).

Make sense?

B.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 39678639
This goes to the sheet3 (Demo) module

Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("Demos").Range("D3") <> "" Then
    formla = "=if(rc[-1]="""","""",INDEX(Demos!R8C5:R13C7,MATCH(RC[-1],Demos!R8C3:R13C3,0),MATCH(Demos!R3C4,Demos!R6C5:R6C7,0)))"
    With Sheets("Customer").Range("F9:F13")
        .FormulaR1C1 = formla
        .Value = .Value
    End With
    With Sheets("Price").Range("F9:F13")
        .FormulaR1C1 = formla
        .Value = .Value
    End With
End If
End Sub

Open in new window

0
 

Author Closing Comment

by:Bright01
ID: 39678728
Thank you! Works great..... I can build with it.

Have a great Thanksgiving!
0
 

Author Comment

by:Bright01
ID: 39678838
Ssaqibh,

Actually, While this works, I have some issues as I incorporate this solution.  I'll be authoring a fix question.

The major issues are I cannot work easily with the RC approach as reference points.  What's missing here is the macro doesn't use the Reference Cells which is why I put them in the Demos Tab.

Hope you will pick it up.

Thanks,

B.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question