Solved: Create a database from info coming from tabs

[yasarayhanka]

hello,
here is what I am trying to do:

I have sheets which are set like a form, and for each account I will have the same sheet just named differently.

I want to be able to feed a database when a user changes the data on one of the sheets,

this sound very easy only if you have one sheet, but in the future if someone adds another sheet I need to be able to link this data to the database

at the end I want to be able to use the database sheet to create Pivot tables.

in the attachments you will find a sample account tabs and a database tab.

users will enter their data in the boxes and this will update the database, if new users are added database should be updated accordingly.

it also needs to delete the data that it refers to if the tab is deleted.


the tricky part is in the database, and I am pretty sure that there is an easier way to do this, I know this can be done in access very easily but I was told that I could not use access.

I thought of naming each cell starting with the sheet name and copying these names to the database, but I could not figure out how to get it work,


any ideas will be appreciated.


thanks,

yasar

[pnewton]

Hi Yasar

Just as luck would have it, I've recently developed a similar spreadsheet (but for job costing). I've updated your spreadsheet to include a "master sheet" which is a blank worksheet used for new clients. The master sheet is hidden by default. The Database page has a "new" button. Clicking this copies the Master and requests a new tab name (without spaces). You enter the data and it's automatically updated in the Database page. If you update any fields that are referenced, the database page updates automatically (when clicked on). If you delete a sheet, the database page will update accordingly.

Note that the Database page is referencing "fixed" cell references so hopefully your master sheet is exactly the way you want it. If you add rows or columns in any of the sheets this may throw out the referencing.

See attached file anyway and let me know if you need any further help

Cheers

Pete

[yasarayhanka]

Hi Yasar

Just as luck would have it, I've recently developed a similar spreadsheet (but for job costing). I've updated your spreadsheet to include a "master sheet" which is a blank worksheet used for new clients. The master sheet is hidden by default. The Database page has a "new" button. Clicking this copies the Master and requests a new tab name (without spaces). You enter the data and it's automatically updated in the Database page. If you update any fields that are referenced, the database page updates automatically (when clicked on). If you delete a sheet, the database page will update accordingly.

Note that the Database page is referencing "fixed" cell references so hopefully your master sheet is exactly the way you want it. If you add rows or columns in any of the sheets this may throw out the referencing.

See attached file anyway and let me know if you need any further help

Cheers

Pete


Pete,
this is great, you saved my life,
One question though,
how can I make the database sheet column A show links to each sheet more than once?

best regards, and thank you very much
yasar

[pnewton]

I don't know why you'd want to do that, except for maybe wanting to show different totals. Short answer, no.

Logically, you'd reference the fields you wanted to analyse using Pivot Tables, all on the same row.

Maybe you'd be better looking at the way the data is entered, i.e. are the two rows for each item required? Could you use a drop down to select either "Kroger" or "Other".

Happy to assist in how you design the form, drop downs, lookups, that sort of thing to make the data entry simpler.

Pete

[yasarayhanka]

I don't know why you'd want to do that, except for maybe wanting to show different totals. Short answer, no.

Logically, you'd reference the fields you wanted to analyse using Pivot Tables, all on the same row.

Maybe you'd be better looking at the way the data is entered, i.e. are the two rows for each item required? Could you use a drop down to select either "Kroger" or "Other".

Happy to assist in how you design the form, drop downs, lookups, that sort of thing to make the data entry simpler.

Pete Pete,
I was able to get the links at the column A of the database appear 20 times,
with just modifieng your code

Private Sub Worksheet_Activate()

Dim sht As Worksheet
Dim Row As Integer
Row = 1
Me.Range("A2:A65536").Clear
For Each sht In Worksheets
If sht.name <> "Database" Then
If sht.name <> "Master" Then
If sht.name <> "Pivot" Then
If sht.name <> "Index" Then

Row = Row + 1
With Worksheets("Database")
.Cells(Row, 1) = sht.name
.Hyperlinks.Add Anchor:=.Cells(Row, 1), Address:="", SubAddress:=sht.name & "!b2", TextToDisplay:=sht.name

End With
Row = Row + 1
With Worksheets("Database")
.Cells(Row, 1) = sht.name
.Hyperlinks.Add Anchor:=.Cells(Row, 1), Address:="", SubAddress:=sht.name & "!b2", TextToDisplay:=sht.name

End With
Row = Row + 1
With Worksheets("Database")
.Cells(Row, 1) = sht.name
.Hyperlinks.Add Anchor:=.Cells(Row, 1), Address:="", SubAddress:=sht.name & "!b2", TextToDisplay:=sht.name

End With
Row = Row + 1
With Worksheets("Database")
.Cells(Row, 1) = sht.name
.Hyperlinks.Add Anchor:=.Cells(Row, 1), Address:="", SubAddress:=sht.name & "!b2", TextToDisplay:=sht.name
so on


I did this so I can modify the formulas you have in the database to reflect each possible data entry point, like current, banana, kroger, volume


current banana, other, volume

I am attaching the modified sheet so you can look at my adjustments,

the only shortcome of this way of handling the data is going to be speed, after adding 20 accounts :)

if you have any suggestions to handle the data in a different manner I am open to suggestions.

Yasar

[pnewton]

I'd add columns to the Database sheet so that the client is referenced only once. These will make each row quite long but limits the data having to be reference to only once. The additional columns would be things like Apples (Kroger), then Apples (Other), etc. It also means you only have to look at particular columns should you wish to get data simply, i.e. totals for Apples (Kroger). Just a thought as it keeps it simpler and as they say, keep it simple.

Cheers
Pete