Linking cells from one sheet to another

  • I am creating a file that summarizes commissions paid for the entire year.


    One sheet (the top portion of the file) contains all salemen detail (quota, payout rates, revenue, adjustments, bonuses, etc.)


    The bottom part of the sheet is the destination sheet (where I would put the formulas). What kind of formulas (Index, offset, Match, Choose, etc) would I use to pull the information from the detail sheet? At present, I am using the index function to do this. My main concern is that at the year progresses, rows may be added which may affect the links. This is why I was thinking about using the match function but don't know how to use it in this information.


    Vlookup I feel won't work because of the table structure.


    Thanks

  • Re: Linking cells from one sheet to another (Revised)


    It has come to my attention that no one knew what I was asking.


    I have revised the file with arrows pointing at the cells I need linked. The bottom section is to linked to the top section (both on separate sheets).


    Is there someway to use a find or match attached to Joe Blow so that if any columns and rows are added it would still match?


    Thanks

  • Re: Linking cells from one sheet to another


    you could also Define the cells as well by naming them.


    insert-> name -> Define


    for each variable. that way no matter when you add or delete rows (as long as the relevant data is not deleted), then the sheet should never "bust", but simply adding and deleting lines should not affect the references (they should automatically adjust.)


    Hope that helped.

  • Re: Linking cells from one sheet to another


    Yes, I am extracting information from the detail sheet to another. I will monthly update the detail sheet and I want it to populate the the Summary sheet.


    You mentioned defining names, are you suggesting have a range name for each column. The saleman data is all the same, quota, month revenue, percentages, etc.


    I could point and link but I think there might be a more efficient way of doing this. I don't want to lose the cell references.


    Thanks

  • Re: Linking cells from one sheet to another


    i think you need to have a couple trial months with what you have. you may be overcomplicating it. the cell references work fine from my end, simply by cutting and pasting the data you already have into another workbook. without seeing all this "adding" and "deleting" of lines, i don't think i can be much more of help.


    perhaps somebody else has a better solution. sorry.

  • Re: Linking cells from one sheet to another


    I don't quite understand what you mean by using the names. Are you suggesting creating a cell name for each person and using this in the formulas.? Like Offset("Joe Blow", 2,3)? to reference two rows below and three columns to the left?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!