Display info range depending on cell selected

Microsoft Microsoft excel 2007 (pc)
June 2, 2010 at 14:17:30
Specs: Windows 7
I will attempt to be as detailed as possible in describing what I am trying to accomplish. I have very detailed experience with writing Excel functions, however I do not have much experience with VB script (with basic programming education.)

I have one tab (named "Reference") in an excel file with multiple tables, all with the same structure just different information. I have a separate tab (named "Input") with a single range of cells (with the same structure as those previous tables) in which I would like to display the data from the reference tables on the first tab.

I would like for excel to display the information from the correct reference table depending on which cell is selected at the moment.

Example: One of such reference tables is labeled "Training." When my users select any cell in the Input tab under the Training column (H) with the purpose of inputting data, I would like the range B2:D5 to display the information from the Training table. Another table is labeled "Development." When the user selects any cell in the Input tab under the Development column (I) I would like the SAME RANGE (B2:D5) to display the information from the Development table.

I will probably need more than just a snippet of code, i will need some background explanation so I can understand the solution and be flexible with where I place the information. Any help would be much appreciated.

See More: Display info range depending on cell selected

June 2, 2010 at 15:18:57
You've done a good job of explaining your requirements.

What's missing is the layout of the Input and Reference sheets.

Input Sheet

Where in Column H is the string "Training"? Where in column I is the string "Development"?

I am assuming you have more than just these 2 strings. If you want the code to run as soon a cell in a specific column is selected, we need to tell the code what to go look for. We can either hard code the strings into the code (inefficient) or tell the code to look in e.g. H1, I1, J1, etc. for the string to search for on the Reference sheet.

Reference Sheet

Where are the tables? How are they laid out? Where in the table is the string "Training", "Development", etc.

If we are going to look for the strings from the Input sheet, it would help if we could narrow down where on the Reference sheet to look (e.g. Column A). Once we find the string, we need to know where the data to be placed in the B2:D5 resides.

e.g. If the string "Training" is in Reference!A10, is the Training data in Reference!A11:C14? Is the data always offset from the string by the same amount in terms of both the Rows and Columns?

Am I being clear in what information we need - and why we need it?

Report •

June 3, 2010 at 06:04:35

Hopefully the following matches your requirements, but if not please post further details.

The following macro is triggered by a change event in one of the three columns H, I or J on the 'Input' worksheet.
(This can easily be expanded to include other columns).

For example if data is entered into column H, a range of cells named 'Development' in the 'Reference' worksheet is copied to the 'Input' worksheet starting at Cell D2. As the named ranges are all 3 columns wide by 4 rows, the ranges are copied to cells D2 to F5.

The range of cells copied from the 'Reference' worksheet depends on which column has data entered into it.
I named three ranges of cells on the 'Reference' worksheet - the names match the functions such as 'Development', to make the code easier to follow.

Entering data in column H copies the 'Development' range of cells, entering data in column I copies the 'Training' range of cells and entering data in column J copies a range of cells named 'Test'.

To use this macro, right-click on the name tab of the 'Input' worksheet and select 'View code'
Paste this into the Visual Basic window:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Target is the changed cell (data has just been entered into it)

On Error GoTo ErrHnd

'stop changes made by this macro from re-triggering it
Application.EnableEvents = False

'only run this code if data is entered into relevant columns
'H (8), I (9) and J (10) in this example
'extend as necessary
If Target.Column >= 8 And Target.Column <= 10 Then
    'respond according to column
    'copy relevant named 'table' to D2 on Input sheet
    'for this example there are three named data ranges:
    'Development (B2:D5), Training (F2:H5) and Test (J2:L5)
    Select Case Target.Column
        Case 8
        Worksheets("Reference").Range("Development").Copy _
        Case 9
        Worksheets("Reference").Range("Training").Copy _
        Case 10
        Worksheets("Reference").Range("Test").Copy _
    End Select
End If
're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
're-enable events
Application.EnableEvents = True
End Sub

Click Save from the VB File menu. Use Alt+f11 to return to the main Excel window

Setup named ranges on the 'Reference' worksheet - match names to names used in the code.
Enter data into a cell in column H and the range of cells named 'Development' should be copied to D2:F5 on the Input worksheet.

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'


Report •

June 3, 2010 at 07:15:16
Humar and DerbyDad03: Thank you so much for both of your responses.

Humar, I don't think your solution will work as it onnly displays the information if data is input into the cell in question. What I'm looking for is to display certain information if the cell is only selected.

DerbyDad03, your request for information makes perfect sense and I appreciate the response. Unfortunately in the last 12 hours the scope for this spreadsheet has increased dramatically and I no longer think the solution I was after will work. The new solution would be much more complex and would require different pieces of code depending on which tab was active and various other things.

In light of the changes I will probably just hard code the information in the data validation input messages for those ranges. It's not a very elegant solution, but for the compexity and timeframes it will work.

Thanks again for offering your time to help!

Report •

Related Solutions

June 3, 2010 at 07:26:50
Humar's code can easily be adapted by changing the "title" to be:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

It will then run as soon as any cell is selected, but only "copy data" if the selected cell is in H, I or J. If not, the code will just exit.

Note: I didn't test this, but it should work...

The comments would also need to be updated to eliminate any wording related to data entry.

Report •

June 3, 2010 at 08:48:11

Thanks DerbyDad03 - yes this works with
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
as the first line of the macro, after Option Explicit.

Selecting a cell in columns H, I or J causes the relevant range to be copied over.


Report •

Ask Question