Hi,
I am trying to link data between two excel worksheets.
One worksheet, lets call it, 'Resource Sheet' has information on work descriptions, hours or costs allocated with that task and resource types. A Budget total adds up the totals at the bottom.
A macro has been created that adds new work description rows, so the position of the budget totals field varies depending on how many work descriptions there are. The amouont of Resources in the spreadsheet can also vary and all the resources may not be used on one job, so some Budget totals may remain blank.
The second worksheet, is called 'Fixed Price Quote' has a number of different columns in it, the ones I would like to concentrate on is Work Description, Resource Type, Activity Type and Hours.
I want a formula in the 'Fixed Price Quote' sheet In column C (Description field) to return the Description in row 1 of the 'Resource Sheet' but only if the corresponding value in the 'Budget total' row is greater than 0.
If the value of the budget total row for that Description is blank, then i want the formula to move to the next column until it finds a a budget total for a description field that is greater than 0.
Once it finds that value that is greater than 0, it is to return the description type in the cell, e.g. C26.
After it has found a suitable description that meets the criteria, it then repeats the above steps and returns the relevant value in C27, but this time the formula begins searching in the next column of the Resource Sheet, e.g. if it was found that PMO had a budget total greater than 0 in column D of the resource sheet, then the formula on the next line down on the fixed price quote sheet should begin its search in Column E, in order not to return any duplicate information.
Again keep in mind that the position of the "Budget Totals" row in the 'Resource Sheet' is always changing depending on the amount of rows there are for Work Descriptions.
I have attached a spreadsheet as an example of the sheets I am reffering to. Located at -
If it is easier to do this via a macro then I don't mind, I would really appreciate it if somebody would help me out with this.
Thanks in advance.
Bookmarks