General discussion

Locked

Linking key fields in Excel

By ngunity ·
I want to know if it's possible, and if so how to link "key" fields in MS Excel.

I have user who need to import data from a unix database of store sales. I can convert the data into excel format but based on fluctuations of the stores, that is sometimes a new store opens or one shuts, I cannot import the data direct to a master spreadsheet unless the stores are dynamically linked to each other. Each store has a store code which will be a suitable field for the key.

I want to achieve this link within Excel, database software is not an option.

Thanks

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Linking key fields in Excel

by leirags In reply to Linking key fields in Exc ...

I like to know, how the "User" iteract whit excel.

You like a Drop Down List, with the stores or some like that.
If so then: save the stores on file first, then try to import to DDL (Drop Down List) and lets make a user selection; and when the user hit a button apply your "Query.."

And i need more details...

Collapse -

Linking key fields in Excel

by ngunity In reply to Linking key fields in Exc ...

OKay firstly drop down lists I don't think will be the solution I am after, let me clarify the problem for you.

I have one spreadsheet which contains data like Manager's names, locations, addresses, etc. This spreadsheet is useful for mail merging letters to all the stores.

I have another spreadsheet which contains sales data for each of the stores. It contains such data as : net sales, gross sales, etc for each month.

Each spreadsheet has a field called "store code" which contains a text-numerical code to identify each store. This is the only field other than the store names, which would make a good primary key (speaking in Access database terms).

I want to somehow setup a query/macro or whatever it takes to associate the sales data of a given store, with it's same store in the mail merge spreadsheet. Since occasionally a store will close no more monthly sales figures will show up and so the fields would need to be dynamically linked.

When I copy/ import monthly salesfigures into a spreadsheet containing mail merge data I want the sales data to match the correct store data by using the store code link.

I hope this makes things clear

Collapse -

Linking key fields in Excel

by ngunity In reply to Linking key fields in Exc ...

OKay firstly drop down lists I don't think will be the solution I am after, let me clarify the problem for you.

I have one spreadsheet which contains data like Manager's names, locations, addresses, etc. This spreadsheet is useful for mail merging letters to all the stores.

I have another spreadsheet which contains sales data for each of the stores. It contains such data as : net sales, gross sales, etc for each month.

Each spreadsheet has a field called "store code" which contains a text-numerical code to identify each store. This is the only field other than the store names, which would make a good primary key (speaking in Access database terms).

I want to somehow setup a query/macro or whatever it takes to associate the sales data of a given store, with it's same store in the mail merge spreadsheet. Since occasionally a store will close no more monthly sales figures will show up and so the fields would need to be dynamically linked.

When I copy/ import monthly salesfigures into a spreadsheet containing mail merge data I want the sales data to match the correct store data by using the store code link.

I hope this makes things clear

Collapse -

Linking key fields in Excel

by leirags In reply to Linking key fields in Exc ...

Here are your Macro
----Start Here
Sub get_a_month_sales()
'
' first we take a store code from cell B1
' on "results" sheet from "mail-stores.xls"
Windows("mail-stores.xls").Activate
Worksheets("results").Select
Store = Cells(1, 2)
' Now clear the result area
Range("A6:E2000").Select
Selection.Clear
' The range is large than fields you have on
' sales data
' Now go to sales and look for
' a store and bring the data to
' the sheet "results"
RAct = 2 ' Put here the row on wihch the data begin
OAct = 0 ' The row count for extraction
' The workbook with sales data
Windows("sales-data.xls").Activate
' The sheet with the mount
Worksheets("jan-2000").Select
Do While Cells(RAct, 1) <> "" ' Do for search on each row with not empty store-code
StoreX = Cells(RAct, 1)
If StoreX = Store Then
' If the store is equal to search store
' then bring data to the other sheet and workbook if apply
Range(Cells(RAct, 1), Cells(RAct, 5)).Select ' The range to copy ej: Range("A2:E2").Select
Application.CutCopyMode = False ' Make not cut option for this operation
Selection.Copy ' Take data to the ClipBoard
' Now Change to the new WoorkBook
Windows("mail-stores.xls").Activate
' Now change (for security) to the apropiete sheet
Worksheets("results").Select
' Locate to apropiete row
OAct = OAct + 1
' The "5" on next line are offset for the "result" sheet aspect
Range(Cells(OAct + 5, 1), Cells(OAct + 5, 1)).Select
ActiveSheet.Paste
' Now Return to the WorkBook and Sheet where the data are stored
Windows("sales-data.xls").Activate
Worksheets("jan-2000").Select
End If
RAct = RAct + 1 ' jump to the next row
Loop
End Sub
----End Here
i made and probe a lot of times

Collapse -

Linking key fields in Excel

by ngunity In reply to Linking key fields in Exc ...

G'day,

I have been caught up in other problems so this one hasn't been much of a priority.

It looks like you've got the goods here though so I will get around to testing it someday and getting back to you..

thanks

Collapse -

Linking key fields in Excel

by ngunity In reply to Linking key fields in Exc ...

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums