Creating a statement in Excel?

Question

Hi there,
I am creating a spreradsheet which needs to be in Excel.
I have the following tables...

Customer Details (With all the details of the customers)

Items (The items that can be bought)

Items bought (Which I have made a matric table of some sort)>>
This table has the customer ID down the side, and the Item ID at the top, you then type the number of itemds bought to the customer ID and Item ID.
I need to create a statement that will automatically create itself to which if another item is added it will then add a new line to the statement.

At the moment all i have is the statement being made to the informatiopn that is static and i dont know how to update it.

Any help will be amazing! Thanks guys :D

Answer 1

this is a database, not a spreadsheet. and you should treat it as such. especially if you want to update multiple columns or rows. learn SQL.



for instance, in SQL, to UPDATE the items column under certain conditions,



UPDATE Items SET SKU='123-BZZT', ProductDesc='Frappachino with emphasis on the Frap' WHERE ProductID='12'



to INSERT data,



INSERT INTO Items(SKU,ProductDesc,ProductID,Price) VALUES

('123-BZZT', 'Frappachino with emphasis on the Frap', '1','3.50'),

('234-MOCHA', 'Count-it Mocha', '2', '55.95'),

('789-BLAST', 'Berry Blast', 3', '3.95')



to create a table,



CREATE TABLE Items (

ProductID INT AUTO_INCREMENT NOT NULL,

SKU VARCHAR(20) NOT NULL DEFAULT '',

ProductDesc TEXT NOT NULL DEFAULT '',

Price DECIMAL(4,2) NOT NULL DEFAULT '0.0',

PRIMARY KEY(ProductID),

UNIQUE INDEX(SKU)

)





that is for SQL.

there are many ways to SELECT depending on how you want to serve up your data.

if you just want everything sorted by SKU in ASCending order, then

SELECT * FROM Items ORDER BY SKU ASC



I would skip Access and go straight for buying Visual FoxPro and PostgreSQL for a back end database engine, unless you don't have the $500 and it's not a serious database and you don't need multiuser. Access is not a real database. But if you are just throwing something together for yourself, and you have access, then use access. Access has forms and reports, and it is easy to use.



but if you need multiuser, don't stop, get foxpro. you don't even have to get postgresql.

Answer 2

This is to complex to answer here. go to Mr.Excel and you will get the answer in the forums.



First you can upload part of your excel spreadsheet, that you prepared thus far



Second you can details what you want clearer



in return someone will post the solution to you, fairly quickly.



There are some Template on the Microsoft website that would create a statement from a database. I assume that what you created.



If you just wanted to update the latest data for one or all customers on their purchases I would use a pivot table. But if you want to create a customer statement that would require some very sophisticated formulae and set-up, which is too complex for Questions. Perfect solution go to Mr. Excel, see link below

Answer 3

if you have you tables in some sort of database (mysql, access or so on) then

you may add link to data in excel sheets like this:



1. Go to File | Get External Data | Link Tables.

2. Navigate to theData File or table

3. Select the Worksheet.

4. click Next, click Finished.

then to update you may click on exclamation sign or update automatically.



it depends on source data. if you explain what kind of tables and where they are you may get better answer.

Answer 4

I'd ask why use Excel?



In Microsoft Professional Suite there is an Access Sample Sales Database the does what you are asking.