[Solved] Sort altering IF statements

[brookc84]

When I conduct a sort on one column within a a spreadsheet, it performs correctly (along with properly sorting adjacent columns)... but it changes some of the values in "if" statements in other columns. Any ideas?? Version 4.1.3 using Win 10 Home.

[acknak]

Greetings and welcome to the community forum!

It's hard to make a specific suggestion without having your sheet to study, but in general, formulas are ok in a sort as long as they refer to cells in the same row, or to cells outside the sorted range. If the formula refers to cells on other rows, within the sorted range, the sort will probably disrupt the formula, and you'll have to work around it or try a different approach.

[brookc84]

Thanks. Yes... the formulas are referring to cells outside the sort and in different rows.

[acknak]

Ok, so if you want specific suggestions, attach a sample document that we can take a look at.

Click "POST REPLY", then use the "Upload Attachment" link (below the message entry area). For tips on posting large or confidential documents, see: [Forum] How to attach a document here

[brookc84]

Thank you for your help.

Attached is my spreadsheet, and I'm working with rows 1-50. Columns A, B and H were entered manually, and columns C-G use formulas. Column H is the same as A. When I try to sort, it's on column G (descending, and only between a red and black line... columns A-G). The formula used in column D seems to work as intended... EXCEPT for the name Dan Jackson, which produces a false value. Also, I can't sort the way I would like to. Any suggestions would be most appreciated.

[keme]

LOOKUP() only works reliably on sorted data. Dan Ghent should be before Dan Jackson. Sort the table properly and all is well.

VLOOKUP can search in unsorted data, and can also be made to only accept exact matches. I gave more detail in your other thread about this.

[brookc84]

I switched the order of Dan Jackson and Dan Ghent and also changed to the VLOOKUP statement. There is still a problem. Dan Jackson is correct on line 6 but not on line 17. Also, Brook Crawford is incorrect on line 15... but correct on line 35. Still can't sort on column G (between red and black lines). Am I missing something? Thanks.

[keme]

Formulas in column D use the value in column H as the lookup value. In row 15 that is "x", not Brook Crawford. This corresponds to value "Not Available" (error) because "x" is not a person in the lookup table. Likewise, row 17 has Brook Crawford, not Dan Jackson, in column H. The lookup returns the correct day result for that name.

If you want to use column A as the name to look for, you need to change your formulas.

[brookc84]

Oops. I must be going blind... working too long looking at the same page . Thanks. With the help of you and acknak I've got everything working the way I intended. At 71, this was my first attempt at creating a spreadsheet. Thanks again.