Is it possible to populate a set of cells manually inputting data into one cell?

[ImportantDonut]

I'm an excel novice and only know the very basic stuff.

Let's say I have a set of 10 cells, B1-B10. I want to fill it up by manually entering numbers into A1 continuously. Take for example a set of numbers that begins with 3 and 6. 3 would be in B1 after entering it, but when 6 is entered into A1 3 would drop to B2 with 6 replacing it in B1. This would continue until it is filled up, and upon the 11th number being entered 3 wouldn't drop to B11 instead just being replaced by 6 in B10, so that there is only 10 cells of numbers at a time. Is this possible? Appreciate the help.

[TMS]

Yes, it is possible. You would need a VBA Worksheet Change Event handler monitoring cell A1.

Do you want to use VBA?

[BadlySpelledBuoy]

If VBA is an option for you (you will struggle to do this without it), then try the attached.

Code to make it work:

Please Login or Register  to view this content.

There will be other ways of accomplishing the same. This is just one easy to follow method.

BSB

[TMS]

@BSB: you would need to put Application.EnableEvents = False/True in there otherwise you will fire the event handler several times unnecessarily.

[ImportantDonut]

Yes, it is possible. You would need a VBA Worksheet Change Event handler monitoring cell A1.

Do you want to use VBA?

Sure. I might as well start learning to use it.

[TMS]

See post #3

[BadlySpelledBuoy]

@BSB: you would need to put Application.EnableEvents = False/True in there otherwise you will fire the event handler several times unnecessarily.

Good point, well made. It does work without it and because the code is so short it doesn't cause much harm, but for the sake of completeness:

Please Login or Register  to view this content.

BSB

[ImportantDonut]

Thanks, Buoy

[BadlySpelledBuoy]

Happy to help.

I'd use the version in post #7 rather than the one in post #3 as it includes TMS's very valid point.

Let us know if you need any of it explaining.

BSB

[ImportantDonut]

Happy to help.

I'd use the version in post #7 rather than the one in post #3 as it includes TMS's very valid point.

Let us know if you need any of it explaining.

BSB

Will do. Appreciate it.

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[ImportantDonut]

I have another question, but instead of starting a new thread I figured I'd ask it in here even though I already noted the original question as solved because they are related. How would I go about getting the sum and abs of B1 & B2 in the same scenario? I've tried several different ways, but when a new number is entered into A1, the resulting sum/abs stay the same as they are now using B2&B3 instead of being updated with B1 & B2. This continues for each number added. New number,B3&B4, new number B4&B5, etc. Thanks.

[BadlySpelledBuoy]

Not sure I've understood 100% so guessing a bit here. If you want to sum B1:B2 in C1 for example, then you can add a line to put that formula in each time.

Please Login or Register  to view this content.

If that's does what you mean then you can add in another line, like the red one, just replaceing the SUM formula for whatever other formula you're using.

The reason your formula target chages is because the code above inserts a cell at B1 to shift things down and therefore updates the target of the formula.

BSB

[ImportantDonut]

Not sure I've understood 100% so guessing a bit here. If you want to sum B1:B2 in C1 for example, then you can add a line to put that formula in each time.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Range("B10").ClearContents
Range("B1").Insert
Range("B1") = Range("A1")
Range("C1").Formula = "=SUM(B1:B2)"
Application.EnableEvents = True
End If
End Sub

If that's does what you mean then you can add in another line, like the red one, just replaceing the SUM formula for whatever other formula you're using.

The reason your formula target chages is because the code above inserts a cell at B1 to shift things down and therefore updates the target of the formula.

BSB

Thanks! That worked. After seeing your answer it seems like it was a silly question, but as I said I know very little with regards to excel, so I appreciate your help.

[BadlySpelledBuoy]

We're here to help no matter how silly the question may seem to you.

When you're learning something new, the only silly question is the one you don't ask!

BSB

[TMS]

You're welcome. Thanks for the rep.


BSB did all the heavy lifting. I just ran interference.

[ImportantDonut]

I'm hoping y'all can help me with another issue I can't quite figure out completely. Let's say I have two cells with numbers in them, B1 & C1. The numbers are not static and will be changing. In another sheet I have a table(preview of it as the attachment). I'm trying to figure out a way to use the value in B1 to determine the column excel will search, followed by it determining if the value in C1 is contained in said column.If it is it would return a set value, likewise if it doesn't.For example with the attachment,B1 currently has the number 5 in it, and C1 currently has 23. So it would go to the column with the 5 header in blue, and then search to see whether that column contains 23. Since it does it would return a Yes to cell D1 in the original sheet.

[BadlySpelledBuoy]

See attached for what I think you mean. Always easier, and prevents guesswork, to work from a sample file rather than a pic.

Formula for D1:

Please Login or Register  to view this content.

Have a look and see if that does what you mean.

BSB

[ImportantDonut]

I attached the file this time. Basically what I'm trying to do is for Sheet2E2-E6 I want to use the value in that E cell to determine which column to use in sheet 3. Then I want it to determine whether the value in Sheet2B1 is within the data of that specific column on sheet3.If it is,Id like it to return "Y" into the F Cell, or "N" if not, directly next to the original E cell that was used for the value to determine the column.Hopefully I'm making sense. Thanks for the help.

[BadlySpelledBuoy]

Try the below in F2 on Sheet2 and copy down.

Please Login or Register  to view this content.

BSB

[AndyJr]

Try this code,


Sub Shift_Cells()
Dim i As Integer
For i = 1 To 10
Cells(i, 2).Value = Cells(i, 1).Value
Next i
End Sub

[BadlySpelledBuoy]

Try this code,


Sub Shift_Cells()
Dim i As Integer
For i = 1 To 10
Cells(i, 2).Value = Cells(i, 1).Value
Next i
End Sub

@AndyJr - That won't do what the OP has asked for at all.
Also, don't forget your code tags!

BSB

[ImportantDonut]

Try the below in F2 on Sheet2 and copy down.

Please Login or Register  to view this content.

BSB

That worked. Thanks! Y'all are quite helpful.

[BadlySpelledBuoy]

That worked. Thanks! Y'all are quite helpful.

Haha! It keeps us out of trouble

BSB

[ImportantDonut]

Haha! It keeps us out of trouble

BSB

Well, I need some more help. I tried to figure it out on my own several different ways, but obviously I never got it. All I got was a headache and less patience. I'm trying to use cell Sheet2G2 to determine the column in sheet8 that should be searched for the value in Sheet2B2. I got that part figured out with your help earlier. The part that gave me trouble was that I'm trying to get it return something besides just "Yes". I want it to return the value in Column AL Sheet 8 in the corresponding row that the value in cell sheet2g2 was found. All the different ways I've tried have led to nothing but errors, so I'd appreciate your help.