Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a spreadsheet I am working on right now that has a problem that I can not figure out. I have one sheet that tracks the status of various projects. The status is tracked with a percentage of completeness. Then there is a column that is a Yes or No answer. All of those columns have conditional formatting on them to color them based on their values. The Yes/No column also has data validation on it to limit the entries to Yes or No.

    One a Dashboard page, I collect only the status columns on one sheet so the overall status can be viewed more easily. The percentage columns work fine, but the Yes/No column is not working right. My equation I am using to grab the values is:

    =VLOOKUP($A6,'Updates '!$A$1:$X$217,20,FALSE)

    Updates is the overall status sheet and I am matching the project line value in column A on both sheets. I had everything working fine and then someone added columns on the Updates sheet, and the Yes/No values got out of whack. If I change the 20 to 23 to reflect the movement of the desired column, I get the equation showing up in the cell on the dashboard page instead of the Yes or No values. I tried moving back to columns 22 or less and I can get column 20 to work even when formatted exactly the same as column 23. I tried removing conditional formatting or data validation. None of that seems to be the problem.

    I have no clue what to even look for since all of those columns are similar. The values in column 20 are the same since I modified them.

    Anything come to mind, or can you shove me in the right direction for what to try next. It seems like a simple issue that I should be able to see, but can not since the trees are in the way of me seeing the forest

    Thanks,

    Andy

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you attach a dummy file so we can see the setup (remove any proprietary info)?

    Steve

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, here is a neutered version of the file. If you look at the dashboard page, the problem is with the last column. The equations in that column are not consistent due to me messing around trying to get them to work.

    Thanks,

    Andy
    Attached Files Attached Files

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post
    Does the attached do what you want?

    I changed the format of the cell from Text to General. Whilst it was set as Text, any formula entered will be treated as text rather than a formula. I also updated all of the formulas in that column to reflect the sheet name 'Updates ' and to lookup column 23.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes it does. I could have sworn that I tried that

    Obviously I had dazzled myself on that one, and I may have been messing with the Updates tab column format.

    Thank you very much!

    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
  •