Hi All,
OPSYS:
Mine: Win 7 Pro
Target machines: Win 7 Pro and Win 10
SOFTWARE:
Mine: Excel 2016 (Office 365)
Target machines: Microsoft Office 2010 Professional Plus
I Have a fairly simple spreadsheet for Visitor Recording (I work at a Museum)
The Kiosk Attendant just asks the visitor/customer these questions:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]DATE[/TD]
[TD="align: center"]DAY[/TD]
[TD="align: center"]SUBURB[/TD]
[TD="align: center"]ADULTS[/TD]
[TD="align: center"]CHILDREN[/TD]
[TD="align: center"]SOURCE[/TD]
[TD="align: center"]NOTES[/TD]
[TD="align: center"]POSTCODE[/TD]
[TD="align: center"]STATE[/TD]
[/TR]
[TR]
[TD]01/01/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]VALIDATION[/TD]
[TD][/TD]
[TD]INDEX/MATCH[/TD]
[TD]VLOOKUP[/TD]
[/TR]
[TR]
[TD]02/01/2010[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
1. Where are you from? (Suburb/Town, State and PostCode)
2. How many Adults and Kids in your party?
3. How did you hear about the Museum? (Dropdown with 12 Choices) EASY stuff in data validation!
I have arranged the Date to mirror the one above unless it detects a "1" in the left-adjacent column and increments +1
So far not much complexity.
The PostCode is an Index/Match from the named range (Dependant, blank if SUBURB is not present)
The State is a vlookup of the State (Dependant, blank if PostCode is not present)
The problem arises in the lookup for the Location (Suburb/Town etc)
The list of postcodes for Australia that I have is 8585 rows long.
I worked through the process at https://www.youtube.com/watch?v=srTteYoqcJs
It works, but is soooo slow (1-2 mins to sort itself out) and reacts to each character input instead of after typing.
I tried a "Containing ..." filter of a linked list of the codes on the page, which was lightning fast in comparison
but would require the list to be present on each and every page ( I was unable to remote it to a single page and ...
there's a need, to do such a search EVERY time a new visitor is recorded (basically, each row entered and some months are Huge!)
Straight Data Validation doesn't offer auto-completion, and scrolling thru 8585 lines in a Validation drop-down is Scary, believe me!
and this "Visitors Book" will be handled by various un-skilled persons. Have to apply the KISS principle I'm afraid.
I've been told an Access database would be more suitable, but lack the knowledge in that field, sadly.
I am Limited to the Office Suite, by the Museum itself, please don't suggest other DB products.
I can provide a copy of the spreadsheet (WARNING! File is MACRO-ENABLED Worksheet *.xlsm) if necessary (Haven't found how to yet, but I'm trying
)
I am Not tied to the spreadsheet, if an Access solution is better suited, I'm all ears. That said, the auto-complete that occurs after some data has been entered does speed up the Entry Process due to similar reasons in the SOURCE column) if this can be duplicated in ACCESS, I'd be impressed.
Any help or suggestions would be greatly appreciated
OPSYS:
Mine: Win 7 Pro
Target machines: Win 7 Pro and Win 10
SOFTWARE:
Mine: Excel 2016 (Office 365)
Target machines: Microsoft Office 2010 Professional Plus
I Have a fairly simple spreadsheet for Visitor Recording (I work at a Museum)
The Kiosk Attendant just asks the visitor/customer these questions:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]DATE[/TD]
[TD="align: center"]DAY[/TD]
[TD="align: center"]SUBURB[/TD]
[TD="align: center"]ADULTS[/TD]
[TD="align: center"]CHILDREN[/TD]
[TD="align: center"]SOURCE[/TD]
[TD="align: center"]NOTES[/TD]
[TD="align: center"]POSTCODE[/TD]
[TD="align: center"]STATE[/TD]
[/TR]
[TR]
[TD]01/01/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]VALIDATION[/TD]
[TD][/TD]
[TD]INDEX/MATCH[/TD]
[TD]VLOOKUP[/TD]
[/TR]
[TR]
[TD]02/01/2010[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
1. Where are you from? (Suburb/Town, State and PostCode)
2. How many Adults and Kids in your party?
3. How did you hear about the Museum? (Dropdown with 12 Choices) EASY stuff in data validation!
I have arranged the Date to mirror the one above unless it detects a "1" in the left-adjacent column and increments +1
So far not much complexity.
The PostCode is an Index/Match from the named range (Dependant, blank if SUBURB is not present)
The State is a vlookup of the State (Dependant, blank if PostCode is not present)
The problem arises in the lookup for the Location (Suburb/Town etc)
The list of postcodes for Australia that I have is 8585 rows long.
I worked through the process at https://www.youtube.com/watch?v=srTteYoqcJs
It works, but is soooo slow (1-2 mins to sort itself out) and reacts to each character input instead of after typing.
I tried a "Containing ..." filter of a linked list of the codes on the page, which was lightning fast in comparison
but would require the list to be present on each and every page ( I was unable to remote it to a single page and ...
there's a need, to do such a search EVERY time a new visitor is recorded (basically, each row entered and some months are Huge!)
Straight Data Validation doesn't offer auto-completion, and scrolling thru 8585 lines in a Validation drop-down is Scary, believe me!
and this "Visitors Book" will be handled by various un-skilled persons. Have to apply the KISS principle I'm afraid.
I've been told an Access database would be more suitable, but lack the knowledge in that field, sadly.
I am Limited to the Office Suite, by the Museum itself, please don't suggest other DB products.
I can provide a copy of the spreadsheet (WARNING! File is MACRO-ENABLED Worksheet *.xlsm) if necessary (Haven't found how to yet, but I'm trying
)I am Not tied to the spreadsheet, if an Access solution is better suited, I'm all ears. That said, the auto-complete that occurs after some data has been entered does speed up the Entry Process due to similar reasons in the SOURCE column) if this can be duplicated in ACCESS, I'd be impressed.
Any help or suggestions would be greatly appreciated
