Bruce54

New Member
Joined
Aug 2, 2017
Messages
15
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:

DATEDAYSUBURBADULTSCHILDRENSOURCENOTESPOSTCODESTATE
01/01/2010VALIDATIONINDEX/MATCHVLOOKUP
02/01/20101

<tbody>
</tbody>


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 :mad:)

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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top