formula to return the ACTIVE cell's contents

**bcn1988** · 08-27-2012, 02:20 PM

I found a forum post in excel solutions online and it did resolve an issue. However, I still need the formlua to do something else that the below formula does not do and am hoping someone might have the solution!

I found this forum posted, please see below:

Note: You can't use B:B or B1:B65536 or the formula returns an error. Use any other large row number.

The formula listed below needs to be entered as array formula.

If you want to return the cell's contents, use this formula:

=INDEX(B1:B65000,MAX(IF(LEN(B1:B65000)>0,ROW(B1:B65000),0)),1)

I am using the forumla to return the cell's contents. In my case, it is a text string. I have data validation lists in column B with several options. On a different worksheet, I have this formula. The problem I am having is if I change lets say B10, the formula does populate the text. Then I go to B11 and select a text from the list and the formula populates this text. But if I go back to B10, the formula will not populate the new text string from B10. (i.e. the formula doesn't recognize going upwards in a column, only downwards)

As a result, I need the formula to be able to recognize ANY active cell in column B. If I have a user that is using my worksheet, I dont want them to have to go in order down rows one by one. If they would like to go from B15 back to B10 and select different text within the data validation list, they can do so and the formula will populate the new text string in B10. If you need more clarity, please let me know. HELLPPP!!

**bcn1988** · 08-30-2012, 04:35 PM

Does anyone have a response?? Please help!!

**Ace_XL** · 08-30-2012, 05:34 PM

The formula you have returns value in the last non-blank row of Column B and not value of active cell

Please Login or Register  to view this content.

What you are trying to achieve is not possible without VBA.

**Jakobshavn** · 08-30-2012, 06:25 PM

Enter the following User Defined Function in a standard module:

Please Login or Register  to view this content.

Insert the following event macro in the workbook code area:

Please Login or Register  to view this content.

You will get a warning about possible circular references, but it should be O.K.
Pick a worksheet cell and enter:
=ActiveCons()

**bcn1988** · 08-31-2012, 10:04 AM

Hey Jakobshavn,

I did exactly what you said to do..and the cell that I put the formula =ActiveCons() only updates the first cell I click on. But it won't update on the cells I click on after that. For example, after following your steps the first cell I clicked on was B17 which had the text string "Event". The cell with the formula =ActiveCons() updated with "Event". The next cell I clicked on was B18 which had the text string "Trip", however the cell with the formula =ActiveCons() still stayed with "Event". Any other ideas? I appreciate the help!

**icestationzbra** · 08-31-2012, 10:21 AM

Originally Posted by bcn1988

Note: You can't use B:B or B1:B65536 or the formula returns an error. Use any other large row number.

this is not the case with Excel 2010. you can use whatever ranges that you want to, albeit, depending on the formula / function being employed, performance may be severely impeded.

**Jakobshavn** · 08-31-2012, 10:28 AM

Make sure Worksheet_SelectionChange is in the proper place. If it is in the same module as the function, erase it and follow these instructions:

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

**bcn1988** · 08-31-2012, 10:50 AM

These are the steps I took...

1) Add new module in VBE.
2) Entered the first code you gave me.

3) Select the workbook node
4) Pasted the code in this module.

The only thing I can think of is I have am calling a macro for when I open the workbook and this is in the workbook module. So the code you gave me is the second sub code. Would this have any affect on your coding?
5) Went to excel spreadsheet, and entered the formula =ActiveCons()