#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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. Last edited by p45cal; 04-27-2018 at 07:41 AM. |
#3
|
||||
|
||||
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] |
#4
|
|||
|
|||
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 |
#5
|
||||
|
||||
Attachment restored to msg#2
|
#6
|
|||
|
|||
This appears to work for what i need
Thank you |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
lookup dates and return corresponding data | newton.rogers | Excel | 7 | 08-15-2011 11:50 AM |
Lookup a value from non-sorted data | udea | Excel | 12 | 04-25-2011 04:34 AM |