+ Reply to Thread
Results 1 to 3 of 3

Linking a 'moving target' or a range of values?

  1. #1
    Registered User
    Join Date
    06-04-2010
    Location
    St Paul, MN
    MS-Off Ver
    Excel 2007
    Posts
    2

    Linking a 'moving target' or a range of values?

    I am an excel newbie, I've done pretty good for myself (thanks, templates and help page!) but I need one more step to complete my project and I can't seem to figure it out.

    I am working on vacation and sicktime tables for my employer. They automatically calculate accrural, subtract when time taken is entered, etc. I have a workbook page for each employee. What I want to do is create a final page that has everybody's name and what their current status is.

    I know how to paste special and link from other workbooks, which is how I plan to get the data I want linked to the last page. BUT some of the data I want to link is a 'moving target' per se. (ie The vacation or sick hours available changes each pay period because of accrural)

    So what I think I want to do: On my final page of totals, I have a box where I enter which pay period it is (any number 1 to 26) then link that box to choose the match from the 'pay period' column on that individual's sheet, which then would return the corresponding value from that row from the 'vacation available' column to be displayed on the final page.

    I've tried LOOKUP functions, INDEX functions, and I con't get them to work, I must be missing some step. I haven't tried match yet.

    Any tips? Thank you!

  2. #2
    Forum Contributor
    Join Date
    08-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    149

    Re: Linking a 'moving target' or a range of values?

    INDEX should do it... =INDEX(array of data, period number, column number)
    array of data is the block of data only, not the headers
    provided you have a 1 to 1 relationship between your period number and your rows of data, you can use your period for the row value
    column number is which column in your array of data is the one you want; if the data is in the fifth column then put 5

    Otherwise, edit/remove personally identifying employee info and post a workbook so we can see exactly what you're working with.

  3. #3
    Registered User
    Join Date
    06-04-2010
    Location
    St Paul, MN
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Linking a 'moving target' or a range of values?

    Thanks for the lead! I have manage to acomplish what I envisioned also using a match function nested in the index function. It works!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1