Excel help please

mercman1969

Hi folks,

Have spent the best part of 2 days reading up on excel 2003 stuff and am still unable to achieve what i want, so turning to you good folk.......

I have an excel database that i wish to alter slightly to make it work quicker and better for me...

The database currently is populated with around 120 rows (this will grow as the year goes on).......

Column A = Model of Car (Fiesta (F) / Mondeo (M) / Puma (P)

Columns B - F = Location of vehicle

Columns G - V = Colour of vehicle (Red / Gold / Blue / Green)

Obviously, each row contains the data relevant to the item as example below


Row 1
Column A = F
Column D = 1
Column T = 1

This tells me that row 1 is a Fiesta, it is located in the workshop and its colour is Red.

I populate this manually and then on a seperate worksheet also populate a table that also has an end column adding up the data.......
From this table, i produce a chart

What i would like to be able to do is for the table and chart to auto populate from my single input

Is this possible....if so.....HOW?

Many thanks in advance for your time and assistance

Bluejaye

My immediate thought is a Visual Basic Macro.
You add or amend the data in your table, run the macro and it updates everything else.

If you don't know how to create a VB Macro, someone may be able to code it for you.

Mister_aaa

Look up how to do pivot tables, this should sort out what you need based on the info you have given.

mercman1969

Been reading about pivot tables since yesterday lunchtime....

Whilst i seem to be able to get the table about 75% correct (some columns missing) I dont know how to produce or get the original data to make a seperate tally chart.........

Rows 1,5,12,26,44,45,46,78,81,82,83,90,100 could be a Fiesta in Red

The current data that i input does not give me the info and i have to manually count them.......

What i am trying to do is to cut down on the amount of data inputting i am doing....I have produced the tally chart but this means i have to input the same data twice......

Am now stuck again

rmg1

To get your pivot table to update automatiically, have a look at dynamic named ranges and use that as the datasource for your pivot-table (you'll have to refresh it by hand but I can probably code something to auto-update it but it'll slow down your data entry.
You can then add in a pivot-chart for the graph you wanted.

thebigbosh

silly question but why not use 3 columns instead of 21 and just have varied inputs in those fields? Then build your pivot chart on a dynamic range using XLend to capture all of the rows. shouldn't be too difficult

mercman1969

thebigbosh wrote: »

silly question but why not use 3 columns instead of 21 and just have varied inputs in those fields? Then build your pivot chart on a dynamic range using XLend to capture all of the rows. shouldn't be too difficult

I have not got a clue what that means.......

The columns as they stand are required entries.....
The cars are being used as an example...the actual data is a little more sensitive and i cannot post.........

Bigphil1474

I may be wrong, but the easiest way seems to be to link the data in table 1 to table 2, then the chart from table 2 will be automatically updated.
To link data from 2 worksheets, you just click on the cell you want the data to go into, type = then click the cell with the data you want. Would look like ='otherworksheetname'!cellnumber. This works if they are seperate excel files or seperate sheets within the same file. Hope that helps.

fergual2

Mercman try this
https://www.dropbox.com/s/pm498n8kencp3af/cars.xls

Second worksheet contains the data input range values in columns A B & C.

Cheers
Al

EchoLocation

A PivotTable and PivotChart should easily give you what you need. Depending on what you need your chart to show, you could just use a few simple formulas to return the results and make them your chart source.

Your data structure seems a bit odd though, unless I'm misunderstanding, you have 1 column for the Model but 5 columns for Location and 16 columns for Colour?

mercman1969

fergual2 wrote: »

Mercman try this
https://www.dropbox.com/s/pm498n8kencp3af/cars.xls

Second worksheet contains the data input range values in columns A B & C.

Cheers
Al

I looked at the link you provided and i note it shows the data.....
I looked at the secone worksheet and cant see anything regarding data input range

Havent a clue of how it works.........