Microsoft Office Forums One List to Rule them all

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-04-2019, 06:00 PM
0rion 0rion is offline One List to Rule them all Windows 10 One List to Rule them all Office 2010
Novice
One List to Rule them all
 
Join Date: Jan 2019
Posts: 6
0rion is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 02-04-2019, 11:42 PM
ArviLaanemets ArviLaanemets is offline One List to Rule them all Windows 8 One List to Rule them all Office 2016
Expert
 
Join Date: May 2017
Posts: 463
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

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.)
Reply With Quote
  #3  
Old 02-05-2019, 02:34 PM
0rion 0rion is offline One List to Rule them all Windows 10 One List to Rule them all Office 2010
Novice
One List to Rule them all
 
Join Date: Jan 2019
Posts: 6
0rion is on a distinguished road
Default

Thanks, I'll look into that.
Reply With Quote
Reply

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 1 11-16-2015 07:40 PM
One List to Rule them all 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


All times are GMT -7. The time now is 07:46 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft