Excel Report Advice

  • Posted:
  • Proposals: 3
  • Remote
  • #38551
  • Expired
Excel Gurus ..Luke D.William C. have already sent a proposal.
  • 4


Experience Level: Expert
Hi. Please see the below ad and if appropriate give me an idea of the time required to advise or perfect the file. Please consider this matter strictly confidential. If you can help, I can send you files separately as they must not be publicly avaialble. Cheers.

We operate a process monitoring system in a bar. The system uses valves and sensors to produce data on beer flows, line cleans,
financial data and other key things.

Each day, this data is exported from the system in the pub and populates a CSV file (attached \"DailyData_2010.txt\"). A new row is added to this CSV file
for each new day\'s data ad infinitum.

We import this file into our reporting file (attached \"Osmatic Reporting.xls\") whenever we want to generate reports and charts. You will see the CSV data
in the tab called \"Raw CSV Data\". I also have an ODS file version (attached \"Club JRW.ods\") which might be better for this. I know Excel versions before 2007 only have 256 columns so we need to use Excel 2007 or Open Office but Open Office is not as good on charts

Here are the problems that need solving :

1. In the XL file, the first data import was fine but since then it doesnt seem to want to work. What it needs ideally is a command button placed on
the \"Data Sheet\" page, which automates the CSV file import and overwrites whatever is in the \"Raw CSV Data\" page. The ODS file at least managed to
record a macro but still I cant assign this macro to a command button

2. The CSV file will carry a full years worth of data from the pubs\' operations. So in effect at some stage there will be 365 lines of data. Sure this
will populate the \"Data Sheet\" page OK but the whole process needs a date search (like the mock up in tab \"Date Search\") and then populate a
\"Data Sheet\" page with only that data represented

3. You will see we have imported 4 days of data from the pub and so the \"Data Sheet\" page shows 4 columns of data and performs various calculations and charts. The formulae set in each of the relevant columns D-G (grey cells) need to be copied across a full 365 columns to ensure that the maximum amount of data can be imported and reported on. But if I copy and paste formulae cells across, it doesn\'t retain the formulae I need and threatens days of manual entry. So I need a way to propoagate the correct formulae across all 365 columns quickly and easily

4. Charts/Graphs - I have done a few sample charts in the \"Charts\" tab. These again are set to report on only our current 4 lines of data from
columns D-G. They need to report on a possible 365 days of data though but if I enter ranges for a full 365 days when there is less data available
than 365 columns, most of the charts are just empty and not much use to view. So can we set the ranges to the maximum 365 days but only show actual data - not empty values?

For each pub, there will be different beer brands and so +\\- x number of brand entries. So we will need to know how the above solutions work so
that we can construct a new XL file for each bar. Maybe the best way to help is to get me across instructions on how to achieve the solutions
so I can then apply those solutions to each new pubs XL file.

Further, what if a client has multiple pubs? He will want to suck out CSVs for each pub and report on them both separately and combined. The above tackles the separately bit but its slightly different for multiple pubs.

Each pub (say 10 pubs) will have its own CSV file. The pub group will want to suck out CSVs for each pub (perhaps with a single button push in the
reporting page). These 10 CSVs will populate 10 data tabs in the excel file. The main reporting page will have 10 buttons each pulling in a specific
pubs data and so showing separate pub reports.

They will also want to report on all pubs combined. The main reporting page will have an 11th button to combine all the data (How) and bring it in
to the reporting page as an all pub combined report.

I am told that folks that know Excel can do this quite quickly so any thoughts most welcome.


New Proposal

Create an account now and send a proposal now to get this job.

Sign up

Clarification Board Ask a Question

    There are no clarification messages.