Cell that allows user input and formula

Hello,

I am new to this forum and have been looking at past threads to see if my issue can be resolved but with no luck.

I am trying to allow the user to input a value if a specific cell says "INSERT WALL" but if it does not, then there is an index to another worksheet that displays the value.

It seems like there is a way to do this through VBA, but I am unfamiliar with VBA, so some help would be great. Here is the basic set up:

Cell D3 is a drop down list that says "INPUT WALL" or other various things.
IF D3 = "INPUT WALL" I want the user to be able to input a value into cell E3
else, I want E3 to be indexed from another worksheet with matching, (which I know how to do)

Thanks!

Hi brickwall. Place this code into the code module for your worksheet (not into a regualr module).

Please Login or Register  to view this content.

This is my INDEX code, but VBA is not accepting the $ and :

INDEX(Piping!$B$2:$AC$27,MATCH(C3,Piping!$B$2:$B$27,),MATCH(B3,Piping!$B$2:$AC$2,))

What do I do?

Try:

Please Login or Register  to view this content.

So, just to be clear, it will prompt the user to input a value in a pop up box if D3 says "INSERT WALL" otherwise, it will perform the index, correct? And there should not be any function in the cell in the worksheet?

Is there an alternate way to do it without the popup? and how to I save the macro to allow it to run in the worksheet?

And Finally, I want to do this for a range of cells, from D3:E16, can I say Range("D3:D16") for example for the second line and then Range("E3:E16") for the 8th line of the code?

That is correct. When the user is prompted to enter a value in the pop-up, E3 will be automatically populated with that value. I fixed a typo in my original code. Please use this one:

Please Login or Register  to view this content.

The macro is a Worksheet_Change event so it goes in the worksheet code module. Do the following:
-right click the tab that has your data
-click 'View Code'
-paste the code in the empty code module that appears
-close the code window
Make your choice in D3, exit the cell by clicking in another cell and see if it works properly for you.

Sorry brickwall. I missed your post #6. The answer is "Yes" to both questions.

I got the index portion to work, but there is no pop up, if "INPUT WALL" is in D3, it just has #N/A in E3

Could you post a copy of your file?

I would rather not, if there is a way to not have the input box, but just to have the cell be blank if it says "INSERT WALL" that would be fine

Try:

Please Login or Register  to view this content.

Thank you for all your help.

It still is not working... This is the code I put in, including the Ranges. But since there is the range, if I change one value, it changes them all, and for some reason, if cell D4 says "INPUT WALL" then it changes them all (E3:E16) to empty cells, but that is the ONLY cell that it works for. if any other cells are selected to INPUT WALL, it just shows #N/A

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D3:D16")) Is Nothing Then Exit Sub
If Target = "INPUT WALL" Then
Range("E3:E16") = ""
Else
Range("E3:E16").Formula = "=INDEX(Piping!$B$2:$AC$27,MATCH(C3,Piping!$B$2:$B$27,),MATCH(D3,Piping!$B$2:$AC$2,))"
End If
End Sub

And my mistake about the INDEX code, it is C3 and D3 (not B3)

Well I just realized why only one cell was working for the "INSERT WALL" part of the code. So, now it works for all the cells, but it clears out the whole column, not just that cell. It has to do with the range, im sure, but I don't know how to fix it

Try changing this line:

Please Login or Register  to view this content.

to this line:

Please Login or Register  to view this content.

If this doesn't work, it would be very helpful to see your actual file. If it has any confidential information, perhaps you could replace it with generic data.

It works!!! Thank you so much!

One more question, if you don't mind. Can I add a line so that if it says "INPUT WALL" then cell E3 will have a different background color

it works!!! thank you so much!

One more question, if you don't mind...

Is there a way to add a line in so that if it says "INPUT WALL" the background color in cell E3 changes color from white to light grey?

nevermind I was able to figure it out.

Thank you so much again for your help!!

My pleasure.

Thank you so much again for your help however, now I am coming across a new problem.

If I have row 4 that has an "INPUT WALL" in D4, and I manually input a value in the cell it all works fine.
Then if I go down to the next row and start inputing my information and D5 does NOT have "INPUT WALL", then the value I input for E4 becomes #N/A

Just to remind you, here is my code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D4:D40")) Is Nothing Then Exit Sub
If Target = "INPUT WALL" Then
Cells(Target.Row, "E") = " "
Cells(Target.Row, "E").Interior.ColorIndex = 15

Else
Range("E4:E40").Formula = "=IF(B4=0,0, INDEX(Piping!$B$2:$AC$27,MATCH(C4,Piping!$B$2:$B$27,),MATCH(D4,Piping!$B$2:$AC$2,)))"
Cells(Target.Row, "E").Interior.ColorIndex = 2
End If
End Sub

If you enter "INPUT WALL" in any cell in D4:D40, then the corresponding cell in column E will be blank and the color will change. If any cell in D4:D40 does not have "INPUT WALL" in it, E4:E40 will have a formula in it. The #N/A is telling you that the formula is returning an error.

Right, that is what is happening. Is there a way to change the code so that the rows are independent?

I'm not sure what you mean by

the rows are independent

If you want the formula inserted only in the target row, then change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

the formula isn't working at all now. Whether it says "INPUT WALL" or not. And the color index is not working.

What I mean by independent is like what you said, each cell in row E has its own formula and it does not change with the other cells in other rows.

My apologies. Formulas are not one of my strengths. Perhaps someone else with more expertise in that area may be able to help. In any case, it would be diffcult to see exactly what is going on without seeing the actual file.

if I upload that workbook, could you possibly help me?

As I mentioned in my previous post, formulas are not my strength. If you post your actual file with a very detailed explanation of what you want to do, I'll be glad to have a look at it. Perhaps what you want to do can be done without using formulas. Include a description of what your formulas are supposed to do as well. I'm going away for this weekend today and won't be back until Monday night (local time) so I won't be able to look at it until Tuseday.