Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Using the INDEX() Function

0

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.

Post Edited
Title Not Appropriate: Title was not appropriate. (could be all caps, annoying punctuation, etc.)
Answer
Discuss

Answers

0

Let's try the easy way first. How about this formula for cell A24?

 =$B20

You can copy this formula from A24 to A35, in fact to the first entry of any month. It builds on the fact that there are 3 blank rows between that row and the last row of the preceding month. If this doesn't work for you we can still look at more complicated solutions, just tell us why not this. 

BTW, your LOOKUP function should also be suitable but it won't be able to deal with the merged cells you have in the intervening rows.

Discuss

Discussion

Thanks for your assistance but sadly nothing has been resolved. Not sure of the significance of the “Select All” but of course the formula “=$B20” will return the value of cell B20. I am aware that there is a select function, but other than selecting a whole worksheet to copy it I am not familiar with how to use it.     To reiterate my issue I want to return the value in last cell used column B in each month which will not necessarily be the last one in the range available in any given month, eg, for the month of January the last entry may be anywhere in the range between B4 and B20. I am not sure if it is related but, in regards to the “TRUE” or “FALSE” test column that I inserted as Column C (based on “=B4<>" etc ) when the adjacent cell in Column B is empty it returns a “FALSE” value and when I have entered a text value into the it  returns a “TRUE” both the results that you would expect.   However, what I do not understand is that if I remove this text value it still returns a “TRUE” where-as I would have expected it to have returned to “FALSE”. Additionally, in regards to the LOOKUP formula, I unmerged all cells in the columns concerned but this did not make any difference to the result.
geodav52 Nov 17, '20 at 1:58 am
I may understand your requirement or maybe I don't. Reiteration will not help in either case. My understanding will be enhanced by the answer to my question: what does =B20, when entered in A24 of your sample, not do that you need to have done? Please test and advise.
"Select all" copies the formula to your clipboiard. That's useful for copying code. It will copy "=B20" too but you may not need it.
Variatus (rep: 4889) Nov 17, '20 at 3:56 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login