Go Back   DeveloperBarn Forums > Operating Systems, Servers & Software > Microsoft Office

Sponsored Links

Discuss "Dynamically Reference Worksheet Cell/Range" in the Microsoft Office forum.

Microsoft Office - Learn helpful tips and tricks to get the best out of Office suite, using Excel, Word, PowerPoint and more. Discuss how to automate repetitive tasks and more.


Reply « Previous Thread | Next Thread »  
 
LinkBack Thread Tools Display Modes
  #1  
Old June 18th, 2008, 08:29 AM
BLaaaaaaaaaarche's Avatar
Administrator


 
Join Date: Mar 2008
Posts: 55
Thanks: 10
Thanked 7 Times in 5 Posts
Rep Power: 1
BLaaaaaaaaaarche is on a distinguished road

Awards Showcase
HTML & CSS Classic ASP 
Total Awards: 2

Default Dynamically Reference Worksheet Cell/Range

Okay, I am not sure if this is already a built-in function in Excel, but I have not yet found it. What I need to do, is build a function, so one worksheet can reference another worksheets cell/range dynamically.

Basically, say I have a worksheet named Summary. In this worksheet, it summarizes a list of part numbers. So, in column A, it will list part numbers. These part numbers each have their own individual worksheet, which is named the same as the part number.

How do I create a function to reference the name of the other worksheet from the part number in the Summary tab.

For instance, if I want to reference the worksheet for part number 123456789, and cell B25, how would I build this function. The code in excel to reference such worksheet from another is:

Code:
='123456789'!B25
You cannot create that dynamically, hence why I think I need a function. I would also like the ability to reference a range as well and not just a single cell.
__________________
"You'll never be as perfect as BLaaaaaaaaarche."
Reply With Quote
Sponsored Links
  #2  
Old June 18th, 2008, 09:21 AM
mehere's Avatar
Super Sarcasm Mistress


 
Join Date: Mar 2008
Location: Wide Awake In Dreamland
Posts: 143
Thanks: 10
Thanked 27 Times in 25 Posts
Rep Power: 1
mehere will become famous soon enough

Awards Showcase
Microsoft SQL Server Classic ASP 
Total Awards: 2

Default

have a look at the INDIRECT function. something like this:
Code:
=INDIRECT(A1& "!B3")
A1 being the field that contains the worksheet name
B3 being the cell that contains the value.

Comments on this post
BLaaaaaaaaaarche agrees:
jmurrayhead agrees: good job mehere
__________________
Quote of the Month:
Regret: It hurts to admit when you make mistakes - but when they're big enough, the pain only lasts a second.

Questions to Ponder:
Could it be that all those trick-or-treaters wearing sheets aren’t going as ghosts but as mattresses?

iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
copyright © 2008 sbenj69
Reply With Quote
The Following 2 Users Say Thank You to mehere For This Useful Post:
BLaaaaaaaaaarche (June 18th, 2008), Rebelle (June 19th, 2008)
  #3  
Old June 18th, 2008, 03:59 PM
BLaaaaaaaaaarche's Avatar
Administrator


 
Join Date: Mar 2008
Posts: 55
Thanks: 10
Thanked 7 Times in 5 Posts
Rep Power: 1
BLaaaaaaaaaarche is on a distinguished road

Awards Showcase
HTML & CSS Classic ASP 
Total Awards: 2

Default

Yep, that with a combination of ADDRESS() worked perfectly.

Thanks, mehere.
Reply With Quote
Reply

  DeveloperBarn Forums > Operating Systems, Servers & Software > Microsoft Office

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
VBScript Functions Reference jmurrayhead ASP Development 25 October 21st, 2008 08:31 AM
dynamically created dropdowns insert to sql peebman2000 .Net Development 14 May 19th, 2008 11:00 AM
update gridview dynamically peebman2000 .Net Development 27 May 8th, 2008 10:03 PM


All times are GMT -4. The time now is 03:38 PM.



Content Relevant URLs by vBSEO 3.2.0