I am using Microsoft Office Excel 365 and am developing a spreadsheet that records information relating to traveling in which one of the worksheets records information on campsites. This question focuses on two columns in the worksheet containing the Departure Location and the Destination location. For purposes of tracking monthly travel details I have divided the worksheet into 12 monthly sections and I want the last Destination Campsite for a given month to appear as the Departure Location for the first entry in the next month. Given that the number of campsites in a given month will vary from one to the next I need to examine the range of cells in the Destination Campsite column and find the last one that contains text and then report this in the first cell in the Departure Location for the new month. I have tried a number of different means of doing this such as:
=OFFSET(B4:B20,,COUNTA(B4:B20)-1) which results in #Spill!
=LOOKUP(2,1/(B4:B20<>""),B4:B20) which results in #NA
I had success with following formula =INDEX(B4:B20,COUNTA(B4:B20)), however, once a cell in the range has had text entered into it, if that text is then removed it subsequently returns a blank cell as opposed to reporting the new last text entry in the range of B4 to B20. . In the attached sample sheet the INDEX function is deployed in orange cells B24 and B35.
I have also added new a column C the purpose of which is to simply report as TRUE if the adjacent celli n Column B contains text and FALSE if it does not. Again, this works correctly when first set up but once a text entry has been made and then subsequently removed it fails to report as FALSE. NB: The cells in the range of both A4 to A 20 and B4 to B20 are set up in the General Format, however, I have tried them setup as Text cells with the same result.
I know this is probably a simple fix but I cannot find it. Can anyone please assist? Thanks in advance.