D&C GLug - Home Page

[ Date Index ] [ Thread Index ] [ <= Previous by date / thread ] [ Next by date / thread => ]

Re: [LUG] Excel Spreadsheet formula

 

I'm sure this thread got to some conclusion, all i'll say is if your persistent with 
spreadsheets , then a pivot tables and vlookups are the way to do reporting and 
segmentation, and dropdowns. Tricky to master but worth it. ... 

However, it will be a pain in the arse and in the long terms unsupportable. (human 
error vs ability to constrain input = crap data = massive waste of time & errors)

an option (and a good one for your use case :  i strongly recommend a simple file 
based database for this use case  

This means no SQL server to make life complicated 
Can be put on a share drive 
Not vendor specific 
Options to develop further 
With an ODBC plugin use from excel. 

for more info see    
http://www.sqlite.org/about.html
http://www.sqlite.org/download.html

for an Access clone on Linux (i just found this OpenSource project - this is not a 
recommendation - it could be !) 
http://kexi-project.org/wiki/wikiview/index.php@xxxxxxxxxxxx


re: SQL ... its super easy      .... Select * from table where field = 'xyz'; 
but there are lots of tools to make ths pain go away .... Excel with an ODBC 
connection is an easy as specify a data source and a cell and press ok, the fields 
maps to the cols and you can add/edit as you like to the data. 

Solving the user input dilemma:

When it comes to the user side ... you should invest a little energy in building an 
app or a web app.    toolkits like cake php can give you validated CRUD  web pages 
with looks ups and other bells a whistles with very minimal effort , investing 2-5 
days learning and building something will be a worth while investment .... you will 
easily waste the same time with excel and get no where near the final outcome. at 
least with a app route your efforts will persist, and have a positive cumulative 
effect. 

and if your worries about infrastructure , just install xamp locally and do 
everything on your localhost until you get more proficient or want to spend a few # 
on an pc to make things more reliable ... or even host it cheap web host .. there is 
certainly no need to invest in expensive hardware. 


Best of Luck 
Jay 



-- 
The Mailing List for the Devon & Cornwall LUG
http://mailman.dclug.org.uk/listinfo/list
FAQ: http://www.dcglug.org.uk/listfaq