VLOOKUP issue in Excel 2007

Topic

New Reply

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

Reply | Quote

Replies

Could you attach a dummy file so we can see the setup (remove any proprietary info)?

Steve

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote