Input formula based on a dropdown

Posted on 2014-07-14
Last Modified: 2014-07-15

I have a validation list in cell F5 in Tab ("Control_Sheet")

I code running that drops a date into another workbook

With ActiveSheet
    r = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("AO2").Formula = "=TEXT(NOW(),""mmm yy"")"

As opposed to using the date, can i reference where the user has selected the date

the workbook it is dropped into is called currentmonth.xlsx

So it will put the date Month and current year based on the dropdown in format "mmm yy"

Question by:Seamus2626
    LVL 22

    Accepted Solution

    You can reference a cell in a different workbook using this formula:


    Just change "'C:\Set\Your\Path\Here\" to your workbook path and "Sheet1'!A1" to the sheet name and reference cell with the date you want. In your target cell go to Format - Format Cell - Custom. Set that to mmm yy.


    Author Closing Comment

    Perfect, thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now