2

I don't like spreadsheets because they are not typed, any cell can have a value of any type. That can cause errors. I'd like to be able to define a type for any cell and the software would prevent the cell from having a value of any other type.

For example: I'd have a spreadsheet with ID of people as integers, their names as strings and their phone numbers as strings too. Phone numbers have only digits so they would be converted to a number in a spreadsheet but not here because the type is defined as string.

This is similar to databases but I want it to be used as a spredsheet, that is with formulas, ordered rows and referencing other cells. For example, the first ID is 1 and every other ID is greater by one than the cell 1 row above.

  • Maybe PollUnit's "free table" feature is something for you. This is about requesting information from participants but not about macros and similar - just a hint. pollunit.com/en/table – Markus Feb 2 at 0:10
1

Spreadsheets do support data validation (see Data -> Validity). You can also use conditional formatting to highlight any issues.

|improve this answer|||||
1

You need a database application to do that, but you'd need to design a custom interface for manipulating data. For example, the free LibreOffice database, Base, enables you to create custom forms.

The data is stored in the "back end", or relational database, and the information is entered, searched and manipulated in the "front end", or user interface. Most database applications also have a Structured Query Language (SQL) command system, so that the data can be defined with fixed types, and even validated, as well as searched, entered or deleted. E.G., a Canadian Postal Code field could validate that the only characters are digits (D), capital letters (A) and space, in the format ADA DAD. Also, see this video on SQL in Base.

|improve this answer|||||
  • FYI Canadian postal codes use a space, not a dash. – Tanath Jan 31 at 21:30
  • @Tanath, merci beaucoup! No wonder my mail was never received. – DrMoishe Pippik Feb 2 at 1:17
0

In Excel you can use a sheet as input form combine with intersect(target) using Worksheet_Change you can check data type that was write in cell and modify / delete value after all needed data was write with VBA save in hidden sheet used as 'table'.

Check data is number

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A5")) Is Nothing Then CheckIfNumber(Target) 
End Sub
Sub CheckIfNumber(Target)  
    If not isnumber(Target) then
        Target="" 
    End if
End Sub

If you don't like sheet as input form, you can build a Excel form in VBA editor.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.