Data Reference Question

lownote14

New Member
Joined
Apr 7, 2011
Messages
19
Hey everyone. Just wanted to ask a quick question.

Lets say I had a data set in a Data Validation drop down menu. I need for when a specific piece of data, (in this case names) is selected for information to be gathered from a seperate sheet with that name as the title of the sheet.

For example I select Sam from Data Validation, and then all my looks ups on the sheet to reference from a differnet sheet called Sam.


To make things more complicated I would like for the ability to look these things up on completely different work books, if possible.

Thanks for your help ahead of time. I feel like others have asked this question before, but am so new to excel that I would not know how to even search for these answers.

Again many thanks to you.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,785
Yes...

1) Declare a variable to point to your worksheet:-
Code:
dim ws as worksheet

Then when you select a new value from your drop-down, use the Worksheet_Change event to set ws appropriately:-
Code:
if target.address="$A$1" then set ws=thisworkbook.sheets(range("a1").value)
(assuming your dropdown is in A1)

Then use ws. to reference the cells in the sheet.

I think... :)
 

lownote14

New Member
Joined
Apr 7, 2011
Messages
19
Wow this is probably exactly what I want. Sadly the only coding experience I have is from high school three years ago.

Would I be putting this in VBA?

If so I know that explaining VBA here would be to much. Instead do you think you could point me in the right direction for a good tutorial. Similar to Mr. Excel's tutorials?

Again many thanks unto you.
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,785
Yes, that was VBA, but there's a worksheet function too.

If your sheet name is in A1 - let's say it's "SAM" - and you want cell Z99 from SAM, then this will do it:-
Code:
=INDIRECT([COLOR=blue]A1[/COLOR]&"![COLOR=magenta]Z99[/COLOR]")
If you change A1 to "DAVE" then that formula will return the value that's in cell Z99 of DAVE.

Extra lesson: if SAM is in A1 and F44 is in B1, then this will return cell F44 from SAM:-
Code:
=INDIRECT([COLOR=blue]A1[/COLOR]&"!"&[COLOR=red]B1[/COLOR])
If you change A1 to "DAVE" B1 to H77, the same formula will return cell H77 from DAVE.

INDIRECT - that's the banana. Check Excel help.

(VBA hasn't changed much since you left high school.)
 

lownote14

New Member
Joined
Apr 7, 2011
Messages
19
Oh Awesome this is exactly what I need to get the ball rolling on my project.

A thousand blessings on you and your family.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,593
Members
409,653
Latest member
rishir

This Week's Hot Topics

Top