Bit of excel help?

WhiteCobra

WhiteCobra

WhiteCobra

Associate
Joined
23 Mar 2008
Posts
275
Work has assigned me a task of saving them time by automating a report... They've got their own excel spreadsheet of production infortmation... They then have another sheet, with most of the data which is on the production spreadsheet, which is a pretty report for their customers.

They want the ability to click a button and this pretty report will fill it self :)

On the production information each product has it's own row.. So basically i was thinking of recording a macro of me copying a single row... And then maybe figure out a way of asking the user "What row to copy" and use their number entered in the macro's...

Does this sound possible? Or is there a much easier way im missing?

Thanks :)
 
Yeah. Basically as of now they've been typing out the data practically twice.. Which they want to eliminate... So basically she'd type the data into the production sheet and then wants this data to magically appear in the report sheet :)
 
What's the "pretty" version going to look like? Pie charts and bar graph's etc based on the original data?

Could you have 1 tab for the original report (assuming that this data is ALWAYS in the same format) then create a 2nd tab that reads the data in tab 1 and pretty's it up?

That way whenever the 1st tab is updated the 2nd tab will automatically update, meaning nothing will need to be done in order to pretty it up once the initial effort is put in.
 
Just link the cells in the sheet you want them to appear in, or do you wanted selected rows based on a criteria? If you juts want to duplicate the sheet "live" as it were do this for example on Sheet2, cell A1 if I type

=Sheet1!A1

Then that cell will populate with the contents of cell A1 on sheet one. Then simply drag the formula across to complete the row, drag the row down to fill the rows down.

If you only want certain rows copied then you may still be able to do it with a formula (preferable to macros for security and no need for a button) but let me know what the criteria is.
 
If it is two sheets within the same workbook then you can reference a cell on another sheet for example you have two sheets - Sheet1 and Sheet2 , you can reference cell A1 on Sheet1 in any cell within sheet2 using the the formular =Sheet1!A1

If they are in two completly different files then you can use an odbc link to lift the data from the initial woorkbook where the data is keyed, updating the sheet from the toolbar (effectively linking the two files).
 
The production sheet is in a separate file from the pretty version...

The production sheet is being added to every day as new productions are made... Each product has it's own row.

The pretty is used when the customer requests a report on their production. This report will obviously only contain 1 of the productions from the production sheet... This is why the row used will change constantly.. So i need some macro that will change what row it will copy from depending on what the user enters..



The reason for having 2 seperate sheets is that the pretty sheet leaves out certain info the customer doesnt need to see
 
I was thinking of keeping the template of the pretty sheet in a seperate tab on the production file to make life easier.. And then making a quick macro button to export that tab to a seperate work sheet
 
From the data bar in connections use microsoft query to query data within the original file. If there are ranges named in the initial spreadsheet you can capture when the range changes or a row is added. you can update the query with the touch of a button as well.

You could always use a pivot table to organise the data (missing those details you don't want anybody to see) and auto update the charts with a button.
 
Last edited:
Pivot tables can be used to simplify any data set so if its a row you want then there is no reason why not. Have a look here

http://en.wikipedia.org/wiki/Pivot_table

There is not a lot you cant do with a pivot table and an odbc link to the initial file if you know what information you want, if you are looking for the latest entry in a range then you could just use an odbc into the other file and display the range.

edit: reading back over this I think you need to be absolutly clear what you need to achieve first if you want to make a new file and query data on another then ms query is what you are looking for its built into the excel toolbar under data.

If its getting information from data automatically then you can update a pivot table from another sheet or workbook.
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom