Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-01-2021, 05:09 PM
Belezeebub Belezeebub is offline Help with IF and Lookup Commands in Excel Windows 10 Help with IF and Lookup Commands in Excel Office 2016 for Mac
Novice
Help with IF and Lookup Commands in Excel
 
Join Date: Oct 2021
Posts: 1
Belezeebub is on a distinguished road
Default Help with IF and Lookup Commands in Excel

The Company I work for is in the process of a huge upgrade and I am project lead I need to provide accurate and up to the minute reports for status meetings.
To that end I have exported our computer inventory to a huge excel spreadsheet
Each tech has 140 computers to upgrade
Each tech has their own tab on my excel file
Here our my questions
Columns H,I,J,K,L are
Tech, completed on date, notes, or Unable to upgrade why?
I need these fields from eight different sheets to feed a master report sheet for management


But only when they are upgraded, i.e., PC 0001 was visited by tech BOB on 10-2, Bob finds out this machine is out of warrantee and due for replacement instead of upgrade, he checks column L and puts in his notes.
MGT. opens sheet and goes to the mgmt. report tab and instantly sees that on 10-2 PC0001 was skipped and why it can’t be upgraded but until the info is filled out it should not be on the mgmt. list.
Mgmt. only wants Computers upgraded and ones that can’t be upgraded and why they don’t need the other 31 fields I am tracking and only want info on their report when it has been interacted with so they can track progress.

So the process would be TechBob goes up updated computers, at the end of his shift he updates his tab in excel with the 20 or so PC he does that day Once he has updated the Field “L” it will place the Computer name on the master upgraded tab, the date it was upgraded and the tech that did the upgrade as well as a total count of PC updated that week.
Reply With Quote
  #2  
Old 10-01-2021, 10:03 PM
Purfleet Purfleet is offline Help with IF and Lookup Commands in Excel Windows 10 Help with IF and Lookup Commands in Excel Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Please provide an example work sheet so we can see the layout
Reply With Quote
  #3  
Old 10-02-2021, 12:18 PM
ArviLaanemets ArviLaanemets is offline Help with IF and Lookup Commands in Excel Windows 8 Help with IF and Lookup Commands in Excel Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Instead a single workbook, create for every tech his/her own workbook. And a master workbook for management, where in hidden worksheet data from tech's workbooks are read in using ODBC queries (e.g. into a single table where for every tech a certain number of rows is reserved - like 500 or 1000 rows per tech data).
And then on another worksheet, meant for management, use another ODBC query to consolidate data from hidden sheet into table you want users to see (i.e. all empty rows are filtered out, and only rows you want to show are read in). NB! You can make separate sheet for displaying all machines which are OK, and another one for displaying all machines which are not.

You can go even further. You can have the list of all machines in mangement workbook, where machines can assigned to techs. Techs workbooks use ODBC queries to read the list of machines assigned to this particular tech only into hidden page in his/her workbook. This hidden table is used as source for data validation list in visible table on another sheet, where tech registers assigned computers and fills his/her info (he/she can't enter such info directly into querytable read from management workbook, as there is no guarantee that this info remains linked to right row when query is refreshed). And you have to foresee means for tech's be aware whenever there is a new machine assigned and not added to update table, or when the machine in update table is not assigned anymore.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with IF and Lookup Commands in Excel Using IncludePicture and MailMerge to insert picture that is taken from a a picture lookup in Excel nic-nax Mail Merge 4 05-14-2019 10:34 PM
LOOKUP - Complex lookup with 2 lookups in 1 cell sglandon Excel 6 05-05-2016 09:44 AM
Help with IF and Lookup Commands in Excel Help needed with Excel Lookup... pfiggy Excel 7 11-17-2015 10:46 PM
Creating Lookup in Excel vikash kumar Excel 2 12-06-2010 06:50 AM
Excel to lookup Access Database PeterP Excel 1 06-11-2009 12:33 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:20 PM.


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