adding new data to another sheet from the main sheet

[ycwkpp898]

Hi i have 2 sheets in my workbook 1 called main and 1 called DVD, on the main
i have a simple enter the number of dvd and it displays the dvd info from
DVD, row B,c,d when it finds that number.
Here is my question i want to be able to if possible basically search for
the number if no infomation is there below i can add the new info eg



Dvd Name type Price
DVD Number 99 Lost Series 2 TV series £29.99


Dvd Name
Type
Price

if there is no info on a number i want to be able to enter this info below
and then get it to add the info.
I have no experience with Visual Basic, and been told i need to use Visual
Basic.

Ty for any info

 

[marcelspruit]

Considering that you have NO experience with Visual Basic, as you
said, I would think the simplest solution would be:
1. have a cell in your 'main' sheet display a message when the given
number is not in the list. Assuming the name of the DVD is supposed to
pop up in B2, put something like =IF(ISNA(B2), "The DVD is not in the
list. Click on the link below to add it!", "") in A4.

Cell A6 then would contain a link to the bottom row of the 'DVD'-
sheet. However, I'm unable to fix a 'dynamic link' to the bottom of
another sheet

Second simplest solution would be to create a button on the sheet to
go to the last Row in the DVD-sheet. Simplest way to do this is to
create the code first, then the button.

So, first press ALT-F11. This will open the Visual Basic Editor.
Insert a Module (Insert --> Module) and click on it. This will give
you an empty screen in which you can place code.

Place the following code:

Option Explicit

Sub EnterNewDvd
Worksheets("DVD").Activate
Worksheets("DVD").Range("A65536").End(xlup).Offset(1,0).Select
End Sub

Now, back to Excel, and go to View --> Commandbars --> Forms (or
something like that, I don't have an English Excel-version) to get the
Forms-commandbar that will allow you to draw a button on the sheet).
As soon as you've drawn the button, Excel will ask you which Macro you
would like to assign to the button. The Macro "EnterNewDvd" should be
in that list.

Hope this helps, Marcel