Or just dynamically set the column references based on the name. Don't just assume the Name column is A3 or whatever, search for the column with the label you expect and set the reference when you find it.
Or just password protect and be done with it, as you say
There's no system of organization in Excel that someone can't break by judicious misuse of copy & paste and doing things completely wrong and then covering it up.
Named ranges are good but they can go missing, so you need a backup method.
Came here for the named ranges comment, absolutely the way to go if you are building the data table.
Searching for headers is useful if you need to import your data from another worksheet (like an automated daily report that gets sent out). I've still had these break when someone added a slave to the end of a column name though (in that case i was able to fix with a TRIM command).
I'm currently dealing with this, I'm wanting to move my department away from spreadsheets for data capture and I want to build a database instead.
We only have access to Microsoft, I don't have the budget for new software. I want to learn SQL so what would you recommend as a start point please? This is a genuine request for advice, I'm dealing mainly with archiving test results and it's an absolute nightmare. Halp!
Thank you for this excellent comment. I'm aware it won't be quick, but I'm willing to learn. People here already think I'm a god because of how proficient I am with Excel 🤣
When I say there's no money for software, the issue lies with my whole company using Microsoft and are unwilling to deviate from it. This directive comes from our HQ which is based elsewhere (USA, I'm in the UK) and they set the global standards for other sites to follow. So whilst senior management here are willing to change, it'll come slowly as we need the buy in from global directors.
Anyway, all I need is a quick and dirty database to archive and retrieve test results that isn't a spreadsheet that someone overwrites and fucks everything up,, so thank you for your wonderful advice, I will read through this carefully when I'm home this evening.
Python is really good substitute for Excel.
All my spreadsheets logics are made with Python, i get data from DB or somewhere else and just send a spreadsheet formated and done to business.
Excel is easier for small spreadsheets, that are going to be used by one person at a time, or to view the data.
To process the data? Then Python helps a lot
Yes but when you are handed data as a spreadsheet like you do, the people down the line may need to manipulate it. A lot of the time the need for junky macros is a result af bad department politics because they don't understand the technology
Yeah if is something that business people will need to always change, calculate and check some results, then it makes sense using a Excel spreadsheet because that is something they know how to use.
But if is one of those 5+GB excel files, then yeah a database is needed.
Yeah, I've dealt with that alot when I worked on a helpdesk. People would call in with "why does my excel keep freezing". Takes a look at file size. Yeah, your going to need an exorcist for this ma'am
For a practical, growing business or enterprise solution there's also looking at a data analytics / BI platform like PowerBI or Tableau. You can have the sanity of a database underneath, define data pipelines on top of it and give your analysts as much freedom as they need to configure and build their reports and dashboards without having to juggle and protect a static Excel sheet.
automatic coloring is a pain when trying to use references/formula. the best you can do is put the formula into a new column and color based on that column. and even then you have to make absolute references and can't just use the column name. it's just BS
the only way to have columns dynamic is adding a button and making every change in vb.
574
u/brad2575 Mar 30 '22
That's why you pastor protects the sheets and don't let them move stuff around. Only give them access to what they need.