Hi All,
I'm hoping someone can please assist me with this (possibly tricky) query:
I have a master sheet and then a report sheet in a single workbook.
Essentially, I want the formula's in the Master sheet, to update automatically based on the report sheet month being updated. Sometimes the alignment of columns in the report sheet changes month to month.
I also need to restrict the rows to only those that have a State or "NSW".
Below is an example of the data, but essentially, I'm thinking that I need to place something along the lines of the following in Cell D2 in order to pull the data from the 'Update' Column in worksheet 'Report - July'
=VLOOKUP(A2,INDIRECT("'"&AJ2&"'!"&"Column D"),2,0)
However, this formula doesn't quite work and also doesn't remove other states. Ideally, this formula would also include a means of updating the prior month automatically within the cell formula (rather than referencing AJ2). This could look something like this (however, I'm not sure how to incorporate this into the above formula):
=CONCATENATE("'Report - "," ",(TEXT(DATE(2004,IF(MONTH(TODAY())-1>0,MONTH(TODAY())-1,12),1),"MMMM'")))
Master Sheet: Called 'Master'
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column AJ[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House names[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]State[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Postcode[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Update[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]QLD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]4075[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Bad[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Report - July[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]QLD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]4075[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Good[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Report Sheet 1: Called 'Report - July'
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Address[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House names[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]State[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Postcode[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Update[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2031[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Bad[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]NSW[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2031[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Bad[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]House 3[/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD]4075[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]House 4[/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD]4075[/TD]
[TD]Good[/TD]
[/TR]
</tbody>[/TABLE]
Report Sheet 1: Called 'Report - August'
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Address[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House names[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]State[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Postcode[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Update[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2031[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Bad[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]NSW[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Even[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]House 3[/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD]4075[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]House 4[/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD]4076[/TD]
[TD]Bad
[/TD]
[/TR]
</tbody>[/TABLE]
Thank you so much, any help is greatly appreciated.
Cheers,
Dave
I'm hoping someone can please assist me with this (possibly tricky) query:
I have a master sheet and then a report sheet in a single workbook.
Essentially, I want the formula's in the Master sheet, to update automatically based on the report sheet month being updated. Sometimes the alignment of columns in the report sheet changes month to month.
I also need to restrict the rows to only those that have a State or "NSW".
Below is an example of the data, but essentially, I'm thinking that I need to place something along the lines of the following in Cell D2 in order to pull the data from the 'Update' Column in worksheet 'Report - July'
=VLOOKUP(A2,INDIRECT("'"&AJ2&"'!"&"Column D"),2,0)
However, this formula doesn't quite work and also doesn't remove other states. Ideally, this formula would also include a means of updating the prior month automatically within the cell formula (rather than referencing AJ2). This could look something like this (however, I'm not sure how to incorporate this into the above formula):
=CONCATENATE("'Report - "," ",(TEXT(DATE(2004,IF(MONTH(TODAY())-1>0,MONTH(TODAY())-1,12),1),"MMMM'")))
Master Sheet: Called 'Master'
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column AJ[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House names[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]State[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Postcode[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Update[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]QLD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]4075[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Bad[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Report - July[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]QLD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]4075[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Good[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Report Sheet 1: Called 'Report - July'
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Address[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House names[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]State[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Postcode[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Update[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2031[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Bad[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]NSW[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2031[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Bad[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]House 3[/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD]4075[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]House 4[/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD]4075[/TD]
[TD]Good[/TD]
[/TR]
</tbody>[/TABLE]
Report Sheet 1: Called 'Report - August'
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Address[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House names[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]State[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Postcode[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Update[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2031[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Bad[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]House 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]NSW[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]2000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Even[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]House 3[/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD]4075[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]House 4[/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD]4076[/TD]
[TD]Bad
[/TD]
[/TR]
</tbody>[/TABLE]
Thank you so much, any help is greatly appreciated.
Cheers,
Dave