My husband is a pilot and I am making an excel spreadsheet to help calculate his per diem wages at the end of the year. I have my spreadsheet set up so that if he enters the city code, say "DAL" in Column A it now prepopulates with "Dallas, Texas" in column B and I used the sumif formula to have column C prepopulate with "$71" which is the going per diem rate for Dallas. If I type in OMA in column A, column B prepopulates with "Omaha, Nebraska" and column C says $61 which is the per diem rate for Omaha. This is what I want it to do. But I have a bit of a complication with the way per diem works. If it is the last day of his trip (which is always OAK) he has to claim the day before's per diem rate. So, how do I get it to look up the per diem rate from wherever he was the day before when I type in "OAK" to column A? Is there a way to tie the word OAK to column C from the row above?
Does this make sense?
Thanks soooo much again for your help!!
Bookmarks