Hi I have this excel spreadshee 7

[eXtacy]

Hi

I have this excel spreadsheet which contains clients information. An example of the spreadsheet is below.

id Name Amount NoOfWeeks
line1/ 1 JHON 1 1
line2/ 1 JHON 15 14
line3/ 2 PETER 2 2
line4/ 2 3 3
line5/ 3 CHRIS 8 5
line6/ 3 10 9
line7/ 4 JAMES 4 4
line8/ 4 JAMES 11 10
line9/ 5 HARRY 5 5
lin10/ 5 12 11
lin11/ 6 BRUNO 14 13
lin12/ 6 6 6
lin13/ 7 PATEL 7 6

This is what i am trying to do:

Where cells are NULL in the 'name' column only, i

------
eXtacy

 

[GeoCan]

Please, explain what you want to do. It seems to have been truncated.

Geo

 

[eXtacy]

Hi geoCan,

i dunno what happened. neway here is what i am trying to do.

I have this excel spreadsheet which contains clients information. An example of the spreadsheet is below.

id Name Amount NoOfWeeks
line1/ 1 JHON 1 1
line2/ 1 JHON 15 14
line3/ 2 PETER 2 2
line4/ 2 3 3
line5/ 3 CHRIS 8 5
line6/ 3 10 9
line7/ 4 JAMES 4 4
line8/ 4 JAMES 11 10
line9/ 5 HARRY 5 5
lin10/ 5 12 11
lin11/ 6 BRUNO 14 13
lin12/ 6 6 6
lin13/ 7 PATEL 7 6

This is what i am trying to do:

Where cells are NULL in the 'name' column only, i want these cells to be automatically filled in with data from the cell above.

For example on line 4 the name field is NULL. here i want it to be filled in with the data from the cell above which would be peter. line 6 would be filled with the name Chris, line 10 would be filled with the name Harry and so on.

I really dont know how to go about this. Please let me know what i need to do and if I need to use VBA.

Thank you very much
regards.

------
eXtacy

 

[bluedragon2]

One way is in a new column Row 1, put:

=A1

starting on row 2, put:

=IF(A2="",A1,A2)

and copy down. Then you can paste/special/values back to Col A

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[eXtacy]

Hi

Isnt there a different way......I tried it the way in the post above but i would still takes time. I want it to perform this action automatically as this table is always updated and new information is always added. This is part of my company records and it takes a real long time to update the table manually.

Any other way?

thanks


------
eXtacy

 

[bluedragon2]

Oh sure, a VBA solution would make it quite simple, just thought you were looking for a VBA alternate solution...



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[eXtacy]

Would you know the VBA code by any chance then?

------
eXtacy

 

[bluedragon2]

Here is a VBA solution:

Sub fill()

For i = 2 To 65536

If Cells(i, 2) = "" Then
End
End If
If Cells(i, 1) = "" Then
Cells(i, 1).Value = Cells(i - 1, 1).Value
End If
Next i


End Sub



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

May have to adjust the cells value since I have it looking on col A. If the names are in column B then:

Sub fill()

For i = 2 To 65536

If Cells(i, 1) = "" Then
End
End If
If Cells(i, 2) = "" Then
Cells(i, 2).Value = Cells(i - 1, 2).Value
End If
Next i

End Sub



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[eXtacy]

hi bluedragon

Thanks for that....I have a problem though..

I have copied the VBA Code in SHEET1's code window and now i am trying to run it.

I am totally new to VBA... i only know a few things....
please could you tell me how i could run the code or what other things i need to do to make it work.

would you be able to send me a sample database with code please. i would be really gratefull.

thanks a lot


------
eXtacy

 

[bluedragon2]

Should be able to run it under menu Tolls/Macros/
it should be listed in the macro box, but I will throw together a quick sheet and send it if you like.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[eXtacy]

yes please could you..........that would be greatly appreciated.....youve been most helpfull.

How can i recieve it?

------
eXtacy

 

[bluedragon2]

give me an e-mail address and I will send it to you...



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[eXtacy]

extacyman007@hotmail.com

------
eXtacy

 

[bluedragon2]

On its way



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[KenWright]

If you select all the data in the Name column, do Edit / Go To / Special / Blanks, type = (Don't hit enter yet) and then using the mouse, click on the first cell above the activecell where the = appears, and now hit CTRL+ENTER. Select all of the Name column, and copy and paste special as values.

I will do a FAQ for this this week - honest

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

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

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

 

[eXtacy]

Nice one Kenwright!!



------
eXtacy

 

[KenWright]

Thank You

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

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

 

[DeLaMartre]

Very Cool, Ken! Great creativity!

Here's another star...

Thanks,

-Bob in California

 

[cmpgeek]

that is very cool Ken! i'm bookmarking this post as i am sure i will have use for it in the future...

other than another star (which i gave you as well), i think we need an emoticon that shows someone bowing down to a superior brain <smile> ( i hate to admit that i have seen parts of Wayne's World, but the whole &quot;we're not worthy&quot; concept is coming to mind ...)

my vital stats: Crystal 8.5 / Oracle database / Windows XP...
... always in a state of ... inspiring to some day experience ...