#1
|
|||
|
|||
One List to Rule them all
Hi, I would like to make a 'master list' of all data in other lists. Column A is a timestamp Macro watching B and C for data. Timestamp cell is locked. Column A is "date/time", Column B is "Customer Name", Column C is "Customer number". Sheet1 is City1, Sheet2 is City2. Sheet3 is State. As data gets entered in B and C, the Timestamp, name, and number from either city is copied to the State sheet. Pasting of Values preferred, occasionally the customer number is blank. All data in this Master State List would naturally be sorted by the timestamp. Essentially the State List sheet would be a changelog list of data entered in both cities, and additional cities if necessary (scalable). Is this possible with formulas? Thanks. |
#2
|
|||
|
|||
My advice is, have data entered into single sheet, with a column with Data Validation List to select city. Your macro will set a timestamp for every entry.
When you need to display entries for certain city, you can: a) Set the autofilter for entry table on this city; b) Create a report sheet, where you can select the city using Data Validation List. Whenever the city is changed, an ODBC query reads data from entry sheet and returns the log for selected city as querytable on report sheet. (With latest versions of Excel, you have to create an Open Event for workbook, which overwrites the datasource for query - otherwise the query will stop to work. And you have to change te extension to *.xlsm); c) Create a report sheet, where you can select the city using Data Validation List. Whenever the city is changed, the log for selected city is read from entry sheet using worksheet formulas. (This solution assumes, you have a lot of empty rows prepared in report sheets for future entries in entry sheet, and from time to tame you have to check, is there enough of prepared rows.) |
#3
|
|||
|
|||
Thanks, I'll look into that.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Rule to forward email using rule to me@onenote.com | anony1 | OneNote | 2 | 02-04-2023 02:39 PM |
if-then-else rule | dcriollo | Mail Merge | 1 | 07-21-2015 01:44 PM |
Setting a rule for a mailing list, driving me nuts! | ib1000 | Outlook | 0 | 05-22-2015 01:10 AM |
Rule to turn off a rule | tiger10012 | Outlook | 2 | 02-23-2013 09:50 AM |
Rule runs once, but not later | stlsailor | Outlook | 0 | 07-28-2009 08:18 PM |