Formula to populate value in cells based on a number

[ranaray]

Hi all,


This is really confusing and I hope I can explain it well.


If cell a4=3 then I want cell b1,b2 & b3 to be =3

If cell a10 = 5 i want b4:b9 = 5


so if a cell in coloumn A has the number 50 , the 50 cells above in B row should have the number 50 in them.


please help

Rana

 

[Debraj]

Hi Rana,


Is VBA acceptable..


Right Click on the SheetName (i.e Sheet1) and select ViewCode.


Paste the below Code..

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 1 Then
Target.Offset(-Target.Value, 1).Resize(Target.Value, 1) = Target.Value
End If
On Error GoTo 0
End Sub

[/pre]

Please let us know.. if worked..

Regards,

Deb

 

[Luke M]

In your two examples, the cells in col B change in different manners. In first examples, cells equal to or below in row value changed. In second example, cells in row or above changed. Can you clarify, or perhaps add more examples?

 

[jason]

this is confusing.... the first 2 lines arent.... but the last one about if a cell has the number 50 lost me completely.


if you want 1 cell to equal the other cell, its fairly simple.

a1=b1. or in your case, you could use =IF(a4=3,A4) and you would just place this formula in the needed cells in colB.


however, i dont think i follow your last example.

if a100 has the number 50 in it, you're saying that you'd need B49-B99 to have 50 as well?!

i dont see how this is possible...

1) this would would not work if the value in the cell is great than the row number of that cell.

2) if a10 has 5 and a11 has 13, this would not be possible to do as b9 would already have "5"

....i must be misunderstanding this part.


can you explain that again in a little more detail? another example? or possibly upload a sample file? (I cant open those due to work restrictions, but others will be ale to help)

 

[jason]

this is confusing.... the first 2 lines arent.... but the last one about if a cell has the number 50 lost me completely.


if you want 1 cell to equal the other cell, its fairly simple.

a1=b1. or in your case, you could use =IF(a4=3,A4) and you would just place this formula in the needed cells in colB.


however, i dont think i follow your last example.

if a100 has the number 50 in it, you're saying that you'd need B49-B99 to have 50 as well?!

i dont see how this is possible...

1) this would would not work if the value in the cell is great than the row number of that cell.

2) if a10 has 5 and a11 has 13, this would not be possible to do as b9 would already have "5"

....i must be misunderstanding this part.


can you explain that again in a little more detail? another example? or possibly upload a sample file? (I cant open those due to work restrictions, but others will be ale to help)