Hello to all,
The document I am working on is generated from an accounts package's job costings module and is a list of costs associated with the jobs that the company has worked (Job Costing) on through out any given period. However, in order to summarise this information the particular job code has to be present next to all its data e.g. The individual material and labour. This has several thousand lines and takes a considerable amount of time to prepare the document.
The job number is what needs to be returned but, this only appears in a column, five places above the header for the labour breakdown section. A basic example of what the data looks like is below.
What I would like to do is insert a new colum left of the material listing and insert a formula that returns the job code (HB 100) below. However, there are hundreds of jobs and each job has a varied amount of rows for materials and labour.
The only constant I can see is that the job number always appears 5 spaces above the material or labour cost header and there is only 3 other repeated text items in the column.
I have tried a formulae that would say, if(or( cell = "" , cell="materials" , cell = "labour" ), select above cell , return selected cell ) which of course just returns the cell above or the cell it was looking at, rather than look at the cell above and then the next until the job number is found.
I apologise if my question is a little hard to follow and will clarify happily if asked specifically.
[TABLE="width: 300"]
<tbody>[TR]
[TD]HB 100[/TD]
[TD]House Build - Joe Bloggs[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Material[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Cladding[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Timber[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Cement[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Labour[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]F Mayweather[/TD]
[/TR]
</tbody>[/TABLE]
The document I am working on is generated from an accounts package's job costings module and is a list of costs associated with the jobs that the company has worked (Job Costing) on through out any given period. However, in order to summarise this information the particular job code has to be present next to all its data e.g. The individual material and labour. This has several thousand lines and takes a considerable amount of time to prepare the document.
The job number is what needs to be returned but, this only appears in a column, five places above the header for the labour breakdown section. A basic example of what the data looks like is below.
What I would like to do is insert a new colum left of the material listing and insert a formula that returns the job code (HB 100) below. However, there are hundreds of jobs and each job has a varied amount of rows for materials and labour.
The only constant I can see is that the job number always appears 5 spaces above the material or labour cost header and there is only 3 other repeated text items in the column.
I have tried a formulae that would say, if(or( cell = "" , cell="materials" , cell = "labour" ), select above cell , return selected cell ) which of course just returns the cell above or the cell it was looking at, rather than look at the cell above and then the next until the job number is found.
I apologise if my question is a little hard to follow and will clarify happily if asked specifically.
[TABLE="width: 300"]
<tbody>[TR]
[TD]HB 100[/TD]
[TD]House Build - Joe Bloggs[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Material[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Cladding[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Timber[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Cement[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Labour[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]F Mayweather[/TD]
[/TR]
</tbody>[/TABLE]