Data Reference Question

[lownote14]

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.

 

[Ruddles]

Yes...

1) Declare a variable to point to your worksheet:-

dim ws as worksheet

Then when you select a new value from your drop-down, use the Worksheet_Change event to set ws appropriately:-

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]

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]

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:-

=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:-

=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]

Oh Awesome this is exactly what I need to get the ball rolling on my project.

A thousand blessings on you and your family.