would I use a macro or formula for this scenario... 1

[pcaok]

On Sheet1!A1, a numerical value is entered. This value is linked to Sheet2, cell A1.
The next time a number is entered on sheet1!A1, the value would be displayed on Sheet2!A2.
Everytime a number is entered on Sheet1!A1, it will increment to the next empty cell on Sheet2!A

Any help would be appreciated. Thanks !

 

[jerseyboy]

Are you saying you want to keep track of how many time the number was updated? <or> are you saying you want a number to cascade to another sheet as an equal?

Example of Incrementing Number

Sheet1!A1 = 4, Sheet2!A1 = 4, Sheet2,B1 =1

Change Sheet1!A1 to equal 20 ...

Sheet1!A1 = 20, Sheet2!A1 = 20, Sheet2,B1 =2

(Where 2 signafies that the value has in A1 and B1 have changed twice) Is this what you are asking?

If so, I am not aware of any incrementing formulas, but there is an VBA function that will increment, I believe that it is an ON CHANGE EVENT.

JerseyBoy
Remember: self-praise is no recommendation

 

[jerseyboy]

Sorry, I was distracted and missed finishing my thought..
I do not know EXCEL (more of an Access VBA person) but I think the basic syntax would go like this:

Public Sub ONCHANGE(CellName)
SheetName!IncrementCellName = SheetName!IncrementCellName + 1
End Sub

Perhaps one of the more adept office programmers could clean this up so it would really work.

Gook luck, hope it starts you in the right direction.

JerseyBoy
Remember: self-praise is no recommendation

 

[KenWright]

Right click the tab on sheet1 and paste this in. Change sheet references to suit:-

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Set sht2 = Worksheets(&quot;Sheet2&quot

If sht2.Cells(1, 1).Value = &quot;&quot; Then
lrow = 0
Else
lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
End If

If Not Application.Intersect(Target, Range(&quot;A1&quot) Is Nothing Then
sht2.Cells(lrow + 1, 1).Value = Target(1).Value
End If
Application.EnableEvents = True

End Sub

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[TonyJollans]

Hi pcaok,

I think it was late at night when Ken posted. His code is trying to find the row in Sheet2 based on other cells used in Sheet1. I think he meant the line ..

lrow = ActiveSheet.UsedRange.Row - 1 + _
       ActiveSheet.UsedRange.Rows.Count

.. to be ..

lrow =

sht2

.UsedRange.Row - 1 + _

sht2

.UsedRange.Rows.Count

That checks the whole of Sheet2 and uses the first empty row. I think, however, what you want is to use the first empty row in Column A regardless of other data on Sheet2 and I would suggest a further amendment, replacing the line with ..

lrow = sht2.Cells(65536, 1).End(xlUp).Row

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[KenWright]

LOL - Absolutely correct on all counts. It was nearly 1am and my littlun wasn't very well, so I kept having to rush upstairs. Just ended up frigging the code to try and make it work, but couldn't for the life of me work out why it kept starting a row down initially. :-(

Anyway, cheers Tony - Spot on, so have a star on me for the catch, and for pointing out that usedrange probably won't always be correct - Should have thought of that.

That aside, I didn't give correct instructions as I forgot the bit about choosing 'view code' from the tab, so here we go again:-

Right click the tab on sheet1 and from the options choose 'View Code' and then in the big white space that appears, paste this in (Change sheet references to suit). Now hit File / 'Close and return to Microsoft Excel'. Change sheet references to suit:-


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Set sht2 = Worksheets(&quot;Sheet2&quot

If sht2.Cells(1, 1).Value = &quot;&quot; Then
lrow = 0
Else
lrow = sht2.Cells(Rows.Count, 1).End(xlUp).Row
End If

If Not Application.Intersect(Target, Range(&quot;A1&quot) Is Nothing Then
sht2.Cells(lrow + 1, 1).Value = Target(1).Value
End If
Application.EnableEvents = True

End Sub

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[TonyJollans]

Thanks, Ken

I do post at 1am sometimes (it IS an addiction after all) but my little'uns are not that little any more - my problem is that they want to use the Internet.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.