'SOURCE" FILE: https://1drv.ms/x/s!AqmnW28CAZhV1xs8c_HNKVme4oaP
EXAMPLE INVENTORY FILE: https://1drv.ms/x/s!AqmnW28CAZhV1x0sH3NFblGNKrP9
EXAMPLE NEW INVENTORY FILE SETUP IDEA:https://1drv.ms/x/s!AqmnW28CAZhV1x_0CKTleEHvXMt1
My field employees turn in this (hand-written) "source" file form each day once they've finished work. My company uses the info from this form for payroll, inventory, job costing, maintenance...the list goes on and on. And each department needs different portions of this info but nobody (except corporate) wants to see what they don't need to see & not all departments can have access to certain pieces of info (payroll for example), so I can't just give everyone access to the source files. We have a separate Excel source file for each project site and use tabs within those files for the dates we actually work on site. When I get the hand-written tickets, I enter all of the info onto that day's tab in the appropriate job file...and then I re-enter different portions of the info onto different spreadsheets for different departments to use. I'd REALLY love to figure out a way to have Excel auto-populate some of this info into different workbooks so that I'm not doing so much manual entry & so that the risk of data entry errors is less since the info would be typed once & then spread out based on formulas looking for "x" info.
My first thought was to get fancy with some SUM & VLOOKUP formulas in the side files but problem #1 is that I don't necessarily have a tab for each date (since we're not on every job every day) & problem #2 is that we don't necessarily send the same employees every day or use the same materials or trucks or...so I'm not sure VLOOKUP is really what I want since I won't necessarily have the "target" info on every tab in every workbook.
Example of what I'm looking for:
1. INVENTORY TRACKER FILE: Shows daily & then sums monthly the data in columns R-U, rows 3-26, for each item code & each job. A summary tab in the inventory file also sums totals for inventory used, loaded, etc. on all jobs for the month. Is there a way to tell the inventory file to look for non-blank rows in my target area, then enter the info as I've entered it? Right now I'm copying and pasting the data from my source file to my inventory file each day as I do my entry but that's just asking for trouble as I could easily forget on one sheet or one day & then that will throw our inventory off for the whole month. I'm envisioning something that will tell my inventory file to look for a non-blank cell, enter the item # for that item & then copy over the data in columns R-U for items used that day & on that job w/ the monthly total being taken care of by looking at M3 in my source file and making each month's tab only look for info when M3 is between, say, March 1 & March 31.
I think once I can figure out the basic setup of a formula to fill in secondary files w/ info IF that info is found, I can modify it to apply to different areas of my source file but right now I'm just at a loss. And I'm OK with the answer being that I'm trying to do too much with Excel...I think I might be but I'm not sure & wanted to find out if I need to talk to IT about a database of some sort.
EXAMPLE INVENTORY FILE: https://1drv.ms/x/s!AqmnW28CAZhV1x0sH3NFblGNKrP9
EXAMPLE NEW INVENTORY FILE SETUP IDEA:https://1drv.ms/x/s!AqmnW28CAZhV1x_0CKTleEHvXMt1
My field employees turn in this (hand-written) "source" file form each day once they've finished work. My company uses the info from this form for payroll, inventory, job costing, maintenance...the list goes on and on. And each department needs different portions of this info but nobody (except corporate) wants to see what they don't need to see & not all departments can have access to certain pieces of info (payroll for example), so I can't just give everyone access to the source files. We have a separate Excel source file for each project site and use tabs within those files for the dates we actually work on site. When I get the hand-written tickets, I enter all of the info onto that day's tab in the appropriate job file...and then I re-enter different portions of the info onto different spreadsheets for different departments to use. I'd REALLY love to figure out a way to have Excel auto-populate some of this info into different workbooks so that I'm not doing so much manual entry & so that the risk of data entry errors is less since the info would be typed once & then spread out based on formulas looking for "x" info.
My first thought was to get fancy with some SUM & VLOOKUP formulas in the side files but problem #1 is that I don't necessarily have a tab for each date (since we're not on every job every day) & problem #2 is that we don't necessarily send the same employees every day or use the same materials or trucks or...so I'm not sure VLOOKUP is really what I want since I won't necessarily have the "target" info on every tab in every workbook.
Example of what I'm looking for:
1. INVENTORY TRACKER FILE: Shows daily & then sums monthly the data in columns R-U, rows 3-26, for each item code & each job. A summary tab in the inventory file also sums totals for inventory used, loaded, etc. on all jobs for the month. Is there a way to tell the inventory file to look for non-blank rows in my target area, then enter the info as I've entered it? Right now I'm copying and pasting the data from my source file to my inventory file each day as I do my entry but that's just asking for trouble as I could easily forget on one sheet or one day & then that will throw our inventory off for the whole month. I'm envisioning something that will tell my inventory file to look for a non-blank cell, enter the item # for that item & then copy over the data in columns R-U for items used that day & on that job w/ the monthly total being taken care of by looking at M3 in my source file and making each month's tab only look for info when M3 is between, say, March 1 & March 31.
I think once I can figure out the basic setup of a formula to fill in secondary files w/ info IF that info is found, I can modify it to apply to different areas of my source file but right now I'm just at a loss. And I'm OK with the answer being that I'm trying to do too much with Excel...I think I might be but I'm not sure & wanted to find out if I need to talk to IT about a database of some sort.
