Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-26-2018, 03:17 PM
sv29 sv29 is offline Data lookup formulas Windows XP Data lookup formulas Office 2010 32bit
Novice
Data lookup formulas
 
Join Date: Feb 2017
Posts: 8
sv29 is on a distinguished road
Default Data lookup formulas

I was hoping someone could help me with a problem I am currently working on:



I have 2 different data sets which are dumped into 2 separate tabs in excel, 1 of the date set is all the costs i have on a project and will be updated monthly by simply adding the previous months costs to the bottom next available rows (not doing a complete data dump). The second tab will be a data dump breaking down the PAYROLL costs into each person, again this data will be updated monthly, however this can be a complete redump of all the months data if need be.

Please see attached example, my goal is to have the PAYROLL data in the Cost Dump tab to breakdown and show all the detail of these costs on the same tab and produce a result as per the Expected Tab. The Cost Dump tab will essentially be the master tab as i will be also added comments/notes against the other costs (ex Rental, Lease, consultant, etc.), so i don't want that information lost every time i do a dump. My vision would be that every month i would add the additional lines of costs to the Cost Dump tab and then as i update the Labour Dump tab, the Cost Dump tab would just add in the additional rows and populate the columns D-H automatically.

Could someone please help with a formula or macro to do this?

Thanks
Attached Files
File Type: xlsx example.xlsx (13.9 KB, 10 views)
Reply With Quote
  #2  
Old 04-27-2018, 03:24 AM
p45cal's Avatar
p45cal p45cal is offline Data lookup formulas Windows 10 Data lookup formulas Office 2010 32bit
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

This would be hard work to do this programmatically, or even with formulae, however, in the attached, not quite what you asked for, but relatively easy to obtain, is a pivot table next to your expected results.
This pivot is based upon a new consolidated table in a sheet Lab&Cost. You can see how this table is made from your two dumps. Each month you'd add to the bottom of the Lab&Cost table.
This monthly process could be automated (in fact, if you made this table a proper Excel Table, after adding the data you'd only need to refresh the pivot).
Note I've added a comments column which would allow you to keep the comments month to month.

It's only an idea, but to get exactly what you want I think people here wouldn't be prepared to spend the time necessary to do it (you watch, having said that, someone will do it!). I think you'd have to pay someone to do it.

Edit post posting:
Grrrr. Cross posting without links AGAIN!
Attachment removed until sv29 complies with rules.

Edit2 post posting:
Attachment restored to this message.
Attached Files
File Type: xlsx msOfficeForums38908example.xlsx (23.5 KB, 8 views)

Last edited by p45cal; 04-27-2018 at 07:41 AM.
Reply With Quote
  #3  
Old 04-27-2018, 05:01 AM
macropod's Avatar
macropod macropod is offline Data lookup formulas Windows 7 64bit Data lookup formulas Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

sv29: Once again, you have ignored the cross-posting etiquette, for which please read: http://www.excelguru.ca/content.php?184
Kindly provide all cross-post links.

Note also that, if you make a habit of not providing the links, you risk having your account here terminated.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 04-27-2018, 07:04 AM
sv29 sv29 is offline Data lookup formulas Windows XP Data lookup formulas Office 2010 32bit
Novice
Data lookup formulas
 
Join Date: Feb 2017
Posts: 8
sv29 is on a distinguished road
Default

Sorry, I didn't realize all forums are connected and cross-posting inst allowed without posting the links to the other forums, my mistake, ill make sure it doesn't happen again.

I have this same topic posted on the following sites/forums:
http://www.vbaexpress.com/forum/show...ookup-formulas

https://www.ozgrid.com/forum/forum/h...ula......topic has been closed

https://www.ozgrid.com/forum/forum/h...lookup-problem

That being said, i do no not have a solution to this problem yet, so any help would be greatly appreciated.

Thanks
Reply With Quote
  #5  
Old 04-27-2018, 07:40 AM
p45cal's Avatar
p45cal p45cal is offline Data lookup formulas Windows 10 Data lookup formulas Office 2010 32bit
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Attachment restored to msg#2
Reply With Quote
  #6  
Old 05-04-2018, 10:07 AM
sv29 sv29 is offline Data lookup formulas Windows XP Data lookup formulas Office 2010 32bit
Novice
Data lookup formulas
 
Join Date: Feb 2017
Posts: 8
sv29 is on a distinguished road
Default

This appears to work for what i need

Thank you
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data lookup formulas lookup or filter data, update it and save it fre_ Excel Programming 3 06-06-2016 08:54 PM
Cross-reference / lookup data from one tab and display on another - How to lonniepoet Excel 0 02-24-2016 01:31 PM
Lookup problem for data input zulugandalf Excel 2 09-22-2015 03:21 AM
Data lookup formulas lookup dates and return corresponding data newton.rogers Excel 7 08-15-2011 11:50 AM
Data lookup formulas Lookup a value from non-sorted data udea Excel 12 04-25-2011 04:34 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:10 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