Having user input and a formula (that can incorporate the user input) in the same cell.

This is going to be a bit of a long one as I have a tricky set up going but I'll try and boil the background info down as much as possible while avoiding giving out company info!:

• My workbook automatically builds master lists that vary in number of rows and number of columns which users then input information into, those master lists are then outputted by macros to CSV for various analysis tools and web uploads to use.
• I use a 'template' spreadsheet (for easy modification if an option changes etc) which has all of the potential column headers (that do not change as they are used by the external programs) and one row of example cells for each which have data validation for the type of entry they need, error messages to guide users into entering the right information and conditional formatting to set styles depending on other cells on that row.
• Macros in the workbook create the master list by copying the columns needed from the template along with the example cell into the new sheet then copying that entire example row to each following row for as many times as is needed.
• This means the cell I need will move around in column and row so needs to act as an island in terms of any formula or process used with it.

In the example workbook I've tried to show a couple of these columns with all of their validation etc intact both as the template and I manually copied and jiggled bits around into a 'master list' sheet to show stuff still working once it has been moved.

What I am trying to achieve is partially demoed in the example workbook in the Tariff cost column. Currently I have a formula in there that calculates its value based on what is selected in the two options columns, I would like for the user to also be able to put a number on top of this cell and that be also added into the sum this is because there may be extra costs that need including that are not factored by the master list.

In my searchings generally I've seen the answer is a hard no and lots of recommendations to just use another cell which if push came to shove I could do but it means modifying my output macros to combine the two (as I cannot create another 'column' in the external programs I output csv files to and I need the total in once place) and it means yet another column along side the 40+ columns the users already have to work through (they seem to have difficulty scrolling and they use vlookup a lot which gets a bit tricky when they have to count so many columns) so if it is at all possible I would like to do it with just one cell.

I did however turn up what looks like the perfect answer to my problem in an old post on this forum which I'll quote now for reference:

Originally Posted by mikerickson

This UDF routine should do the job.

Put this in a normal code module.

Please Login or Register  to view this content.

And put this in the ThisWorkbook code module

Please Login or Register  to view this content.

If a spreadsheet formula involves the new function CellEntry(), entering text into that cell will not overwrite the formula. It will set the value returned by CellEntry(). For example:

Enter the formula =CellEntry() in a cell.
The cell will appear empty and the formula "=CellEntry()" will be in the formula bar
Enter "cat" into the cell
The cell now shows "cat", the value of the formula "=CellEntry()", which is shown in the formula bar.

To use it in your sitution. If you want a cell to have the formula =A1+1 unless it has been overwritten, put this formula in a cell

=IF(CellEntry()<>"",CellEntry(),A1+1)

If what has been typed in the cell is "", the formula returns A1+1.
When something has been typed into the cell, the formula returns what was typed.

NOTES:
If a cell has a formula that includes the CellEntry UDF:
a) entering a constant into the cell sets the value of CellEntry to that constant as text.

b) entering a formula into that cell changes the formula in the cell, but not its CellEntry value

c) clearing the contents of that cell sets the value of CellEntry() to vbNullString. It does not remove the formula from the cell.

To remove a CellEntry formula from a cell, either delete the cell or enter a non-CellEntry formula (eg "=3") and then delete that formula.

Copy/Pasteing a CellEntry formula cell also copies the CellEntry value.

However when I try to use this it interferes with the data validation I am already doing as it seems to use part of the data validation process to make it work so I was hoping either if the op was still on the forum they might be able to help modify it to work or if another expert is able to decipher it they could give me some pointers as it is a little beyond my skill level.

The code from what I can understand seems to work on any cell in the whole workbook so perhaps its scope could be focused to just any column titled 'Tariff Cost' wherever it ends up and the data validation for the cells in that column could be removed as any input from the user could simply be trapped with a pseudo IF NOT NUMBER THEN 0 trap.

Hi mehmetcik, thanks for replying, I'm not entirely sure how your code works, i tried to put it in the example but i couldn't see any way of it triggering and putting a value over the formula just wiped the formula as per standard behavior, are you able to modify the example to show yours working?

That's given me something to focus on, I'll give it a crack and see what I can do, thanks!

Ok so I've just got around to testing this, after some initial problems (pebkac) it does seem to work fine and plays nicely with other cells having data validation so long as the first row header is not "Tariff cost", it even works with conditional formatting rules which is great however I'm now running into the issue that the max formula length is limited by the data validation error box text limit (max 225 characters).

If I try to put in a longer formula such as "=IF(CellEntry()<>"",SUM(IFNA(SWITCH(INDEX(2:2,MATCH("Tariff Option 1",$1:$1,0)),"A",5,"B",10,"C",15,0),0),IFNA(SWITCH(INDEX(2:2,MATCH("Tariff Option 2",$1:$1,0)),"X",2.5,"Y",7.5,"Z",12.5,0),0)) + CellEntry(),SUM(IFNA(SWITCH(INDEX(2:2,MATCH("Tariff Option 1",$1:$1,0)),"A",5,"B",10,"C",15,0),0),IFNA(SWITCH(INDEX(2:2,MATCH("Tariff Option 2",$1:$1,0)),"X",2.5,"Y",7.5,"Z",12.5,0),0)))" it all breaks (382 characters).

Is there any way around this such as dumping the formula into a bigger 'holding pot' or is there a way I can put a smaller formula into the cell which references a function with the above formula in it that will resolve as if it were in that cell (my switch statement runs off a cell row referenced index match)?

Sorted! I dumped most of the formula string into a function (it was just basic calculations anyway)thus shortening the formula string it was storing. I had to play around with it a bit to make it reference from self but now the tariff cost cell is pretty idiot proof*!!

*I realize this is tempting fate for the universe to make a bigger idiot.

Massive thanks to mikerickson for his impressive code, I could not find anyone else that has tried to tackle this problem.

I've attached the working example book for anyone in the future that has this issue.