[JCel]

Hi there,

I want to connect two different excel sheet in the manner as follows:

Consider I'm using sheet1 with fields like prod_id & prod_name with their data (1,2,3,4,5) and (a,b,c,d,e) respectively.

Now if I enter the prod_id as 3 in sheet2, then I need all the other fields related to it, to be automatically retrived from sheet1..
i.e the prod_name related to the given prod_id... e.g.. If I enter 3 as prod_id then I should get its prod_name as c!

Is it possible to do with excel? If yes,Please do guide me with step by step instructions..

[ScottGem]

Its possible, But this is really better suited for a database. Do you have Access?

[JCel]

Its possible, But this is really better suited for a database. Do you have Access?

Yes I do have Access 07'

[ScottGem]

What is the purpose of your application? There may be a template you can start from.

[JBeaucaire]

In Excel, the formulas you can use to lookup row values from a matching "code" are:

LOOKUP() - (database sheet must be sorted ascending, but whole rows/columns are allowed)
VLOOKUP() - (data doesn't have to be sorted, but only returns values to the right, and whole columns are not allowed)
HLOOKUP() - (data doesn't have to be sorted, but only returns values from below, and whole rows are not allowed)
INDEX/MATCH - (suffers from no limitations described above, whole columns/rows can be used and results can be returned from any direction)

Here's a sample sheet showing items being retrieved from a row-style database on one sheet to another sheet. You can see the structure of all three approaches. They all work fine, but obviously I'm a fan of INDEX/MATCH first and foremost.

Index/Match take a little more effort to learn, but not much, and it completely replaces all those other functions, so that's my vote. But use whichever you find most useful for you situation.

[ScottGem]

Like I said and JB showed it is possible to do in Excel. However, your data has to be in a table format to use any of the functions he listed. I got the impression you were entering data more as a form (why would you need a separate sheet for each record otherwise).

Excel is fine as a database when used for flat data. But when you want to relate your data, as your question indicates you do, then a real database is a better tool.

[JBeaucaire]

I got the impression you were entering data more as a form (why would you need a separate sheet for each record otherwise).

From this:

I'm using sheet1 with fields like
prod_id, prod_name with their data (1,2,3,4,5) and (a,b,c,d,e) respectively.

Now if i enter the prod_id as 3 in sheet2, then i need all the other fields related to it, to be automatically retrieved from sheet1..

I read that as one database sheet and other sheet(s) he wants to draw info TO from that database sheet, exactly like you and I are saying. I don't think that's 1 sheet per record.

[JCel]

Hi all,

Thank you for your replies... Since I'm new to Excel I don't have clear idea about its conditional functionalities but now I'm clear with some concepts like LookUp... Thanks for that.

Ok My actual scenario is :

(Sheet_1-->purchase details) in which I have already stored 500 records so I don't know how to switch over to the Relational Databases.

Now in (Sheet_2---->Sales Details) I sold only 100-150 products that's why I want to relate my Sheet_1 with Sheet_2.

Also here in sheet_2 I should not enter the same prod_id twice bcoz I can sell my product only once, so if I enter the id again it should give me an alert as "You have already entered this prod_id!"

Hope you understood the scenario...

Regards,
JCel.

[ScottGem]

There are some invoicing or Order and Inventory templates for Access, that I suggest you look at.

So, what it looks like you are doing is tracking inventory and sales, correct?

If so, a database is truly the best tool for this. If you look at some of the templates you may see that.

Unless you are tracking each specific item you sell individually your premise is incorrect. If you are selling a big ticket item like cars or boats or heavy machinery, you might track each individual item by VIN or serial number or other such identifier.

But if you are tracking a fairly large number of products that you make or sell, then tracking in individual toaster (for example) is inappropriate. The Inventory tracking model that involves movement of items generally uses a structure with tables like so; Products, Sales, Purchase Orders and Transactions. The Products table contains information about the products you buy, sell and/or manufacture. The sales table contains info about each sale to a customer. The PO table contains info about each purchase of product. The Transactions table ties everything together but providing detail about each movement of stock.

So, lets say you purchase 1000 widgets and 1000 thingees from Supplier A. You would create a PO record of the purchase with 2 transactions indicating the purchase. The next day, you sell 100 widgets and 50 thingees to Customer A. So you create a Sales record and 2 more transactions indicating the sale. If you want to calculate inventory you add the Incoming transactions and subtract the outgoing to get your inventory.

It shouldn't be difficult to import your existing data into Access. Its simply a matter of matching the fields.

[JCel]

Hi Scott,

What you are saying is correct... but I don't know how to import Excel into Access! And the inventory templates are not satisfactory..

Also if I enter the prod_id again it should give me an alert as "You have already entered this prod_id!"-How to get this?

[ScottGem]

You set Prod_ID as a primary key for that table. Where it should probably be a Foreign Key (Duplicates OK) in the table you want to enter another one.

As to getting the Excel data, link to the excel table and then create an Append query.