Index and Vlookup Question

[DM_86]

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'

	Column A	Column B	Column C	Column D	Column AJ
Row 1	House names <tbody> </tbody>	State <tbody> </tbody>	Postcode <tbody> </tbody>	Update <tbody> </tbody>
Row 2	House 3 <tbody> </tbody>	QLD <tbody> </tbody>	4075 <tbody> </tbody>	Bad <tbody> </tbody>	Report - July
Row 3	House 4 <tbody> </tbody>	QLD <tbody> </tbody>	4075 <tbody> </tbody>	Good <tbody> </tbody>

<tbody>
</tbody>

Report Sheet 1: Called 'Report - July'

	Column A	Column B	Address	Column C	Column D
Row 1	House names <tbody> </tbody>	State <tbody> </tbody>		Postcode <tbody> </tbody>	Update <tbody> </tbody>
Row 2	House 1 <tbody> </tbody>	NSW		2031 <tbody> </tbody>	Bad <tbody> </tbody>
Row 3	House 2 <tbody> </tbody>	NSW <tbody> </tbody>		2031 <tbody> </tbody>	Bad <tbody> </tbody>
Row 4	House 3	QLD		4075	Bad
Row 4	House 4	QLD		4075	Good

<tbody>
</tbody>

Report Sheet 1: Called 'Report - August'

	Column A	Column B	Address	Column C	Column D
Row 1	House names <tbody> </tbody>	State <tbody> </tbody>		Postcode <tbody> </tbody>	Update <tbody> </tbody>
Row 2	House 1 <tbody> </tbody>	NSW		2031 <tbody> </tbody>	Bad <tbody> </tbody>
Row 3	House 2 <tbody> </tbody>	NSW <tbody> </tbody>		2000 <tbody> </tbody>	Even
Row 4	House 3	QLD		4075	Bad
Row 4	House 4	QLD		4076	Bad

<tbody>
</tbody>

Thank you so much, any help is greatly appreciated.

Cheers,

Dave

 

[mrmmickle1]

Try something like this... I did a few tests and got this to look up data off of a sheet name "Report - July"

=VLOOKUP(A2,INDIRECT(CONCATENATE("'Report - ",(TEXT(DATE(2004,IF(MONTH(TODAY())-1>0,MONTH(TODAY())-1,12),1),"MMMM")))&"'!"&"A:D"),2,0)

 

[DM_86]

Hi mrmmickle1,

Thank you very much; this answer is very close and seems to be on the right track but doesn't quite return the correct result.

If I put this formula in cell D2 it returns the value in the 'Report - July' worksheet but results in cell B2 rather than F2. In other words it puts the state column in the update column.

Apologies if I didn't explain this very well.

 

[mrmmickle1]

You need to change the reference in the Vlookup formula..... 2 = Column 2 which is the State Column.... if you need another column's data just adjust accordingly. In addition you may need to adjust A:D to A:F if you are looking for a value in Column F (Column 6):

=VLOOKUP(A2,INDIRECT(CONCATENATE("'Report - ",(TEXT(DATE(2004,IF(MONTH(TODAY())-1>0,MONTH(TODAY())-1,12),1),"MMMM")))&"'!"&"[B][COLOR=#FF0000]A:D[/COLOR][/B]"),[COLOR=#FF0000][B]2[/B][/COLOR],0)

maybe this will work:

=VLOOKUP(A2,INDIRECT(CONCATENATE("'Report - ",(TEXT(DATE(2004,IF(MONTH(TODAY())-1>0,MONTH(TODAY())-1,12),1),"MMMM")))&"'!"&"[B][COLOR=#FF0000]A:F[/COLOR][/B]"),[B][COLOR=#ff0000]6[/COLOR][/B],0)

 

[DM_86]

Thank you very much Matt,

That works perfectly!

Your help is greatly appreciated.

Cheers,

David