1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: Referencing Values (Not cells)

Discussion in 'Business Applications' started by rockytop80, Nov 30, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. rockytop80

    rockytop80 Thread Starter

    Joined:
    Nov 30, 2011
    Messages:
    4
    Hi. I'm building an employee schedule into Excel. I am using a seperate tab for each different areas of the business. In each tab I have a column that indicates if the shift is a "Custom Program". I would like to have a seperate tab that brings all "Custom Program" shifts together on one page, drawing from all the other tabs.

    I know how to reference specific cells onto the new tab, and I know how to do conditional formatting within a tab to find and respond to a specific word, but that's not quite what I need.

    -Is there a way to reference a particular value (every time the words "Custom Program" appear) from Sheet 1 and put it in Sheet 2?

    -Once the "Custom Program" values are sent to Sheet 2, I need the rest of the data in those row to come with them. How do I do that?

    I hope I've explained that somewhat clearly. Thanks for your help.
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi rockytop80, welcome to the board, maybe I'm dense but I don't really get the idea of what you want / need.
    Could you attach a sample with non-private data where you you show what you have adn also what you want as a result.
    You also forgot to mention whcih version of Excel you're running, 2003, 2007/2010?
     
  3. rockytop80

    rockytop80 Thread Starter

    Joined:
    Nov 30, 2011
    Messages:
    4
    I'm having trouble even putting into words what I need to do.

    I have attached a file as an example. Sheet 1 is the "Archery" schedule. Sheet 2 is the "Fishing" schedule. You'll see that they each have a column labeled "Activity" and that one of the shifts on each is identified as a "Custom Program".

    Sheet 3, labeled "Custom Programs" is where I want to automatically compile any schedule that is identified as a "Custom Program" from either the "Archery" or "Fishing" tabs. In the attached example I have done it manually by copying and pasting.

    I'm running Excel 2010.

    Does that help?
     

    Attached Files:

  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I've downloaded your sample and will see if I can come up with something or more questions
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I wrote some simple code to do just that.
    It will check every sheet that has the value "Activity" in cell G1 and the name is different to "Custom Programs" and copy only those where the value in cloumn G is "Custom Program"
    The keyboard shortcut is Shift+Ctrl+K

    You'll have to accept the woekbook as trusted for the macro's to be allowed.

    Let me know if it works at your side.
     

    Attached Files:

  6. rockytop80

    rockytop80 Thread Starter

    Joined:
    Nov 30, 2011
    Messages:
    4
    Been out of the office for a few days, but I'm back now and gave this a try. I really appreciate your work on this. The attached file doesn't seem to have retained your code. Could you send the info again?
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Did you allow the macro's to be activaed and saved as xlsm file?
    If you didn't the code is lost
    I've attached it again
    You could als have picked it up from the post and make sure you save it as a macrom enabled file (extension xlsm)
     

    Attached Files:

  8. rockytop80

    rockytop80 Thread Starter

    Joined:
    Nov 30, 2011
    Messages:
    4
    Hmm, well I did save it as an xlsm file and allowed the macros, but I'm still getting just the basic data that I entered originally.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1029070

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice