Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am creating a spreadsheet dashboard for a spreadsheet that tracks the progress of many activities as part of a project. Say I have a 100 activities that are listed by row, with various tasks listed in columns. The main sheet has some additional columns that I do not want included in the dashboard, so the dashboard only uses 4 of the columns from the full sheet and then colors them based on their percentage of completeness.

    The row numbers for both sheets are the same. So in the dashboard, I refer to the other page as:

    B4 - contains (='Updates'!B4)

    That works great, until a row is added in the Updates sheet. Then all of my references are off by a row from that point forward. I can not use an absolute reference because that will not help if a row is added either.

    I can not figure out a way for the rows to match up. Column A is duplicated in both sheets. I think that may be the way to match up say column B with column D of the appropriate row, but do not know how or if that is the best way.

    Thanks,

    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use the INDIRECT function: the formula

    =INDIRECT("Updates!B4")

    will remain the same when rows are inserted or deleted. To avoid the hassle of having to modify the formula for each row, you could use

    =INDIRECT("Updates!B"&ROW())

    This can be filled down.

    Alternatively, if the values in column A are unique, you could use VLOOKUP formulas, for example in B4:

    =VLOOKUP($A4,Updates!$A$1:$B$60000,2,FALSE)

    This formula can be filled down too.

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796993' date='08-Oct-2009 15:10']You could use the INDIRECT function: the formula

    =INDIRECT("Updates!B4")

    will remain the same when rows are inserted or deleted. To avoid the hassle of having to modify the formula for each row, you could use

    =INDIRECT("Updates!B"&ROW())

    This can be filled down.

    Alternatively, if the values in column A are unique, you could use VLOOKUP formulas, for example in B4:

    =VLOOKUP($A4,Updates!$A$1:$B$60000,2,FALSE)

    This formula can be filled down too.[/quote]

    Thanks Hans!

    VLOOKUP is the ticket. The only issue is the changing range on the other sheet. My solution was to set my lower row limit much higher than the existing 100 rows of data. I made it 200.

    Thanks again!

    Andy

Posting Permissions

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