Formula

[jrl1208]

Hi
I'm having problems with my formula's and hope someone can guide me.

Formula 1: Please refer to attachment- Worksheet "Graph Data" - I've inserted a vlookup & match formula from range C2:CT29 but it's not picking up the values. don't know what I'm missing.
Formula 2: Worksheet "Graph Data" same formula from range C33: CT35
Formula 3: Worksheet " Goods" Is there another formula to replace the current one so that it picks up the date range in column E instead of column F?

Your help will be much appreciated.

Thanks

 

[NARAYANK991]

Hi ,

The 4th parameter of the VLOOKUP function is very significant ; use this :

=IFERROR(VLOOKUP($B13,Goods!$B$7:$CR$500,MATCH('Graph Data'!R$1,Goods!$B$7:$CT$7,0),FALSE),0)

instead of :

=IFERROR(VLOOKUP($B13,Goods!$B$7:$CR$500,MATCH('Graph Data'!R$1,Goods!$B$7:$CT$7,0)),0)

Narayan

 

[NARAYANK991]

Hi ,

As far as formula 3 is concerned , do you wish to arrive at the beginning of the month whose date is in column E ?

Given any date , the first of the month is arrived at by using something like this :

=Given_Date - DAY(Given_Date) + 1

Narayan

 

[jrl1208]

Thank-you Narayan, both my vlookup and match formula works perfect now.
as for formula 3, because my date range from cell R7:CT7 is 1st of the month so the existing formula doesn't recognise the date format in column E so I had to insert column F.
Is there a formula that recognise the date range from 1-31st of the month and dumps it in the correct month from range R7:CT7?

Are you able to write out the full formula for me as I don't know how to insert =Given_Date - DAY(Given_Date) + 1 in my existing formula.

Thank-you!

 

[NARAYANK991]

Hi ,

Your present formula in R13 would become :

=IF(($E13-DAY($E13)+1)<>R$7,0,$M13)

Copy this down and across as required.

Once you have done this , column F will not be required ; I assume that the data in column F is not used anywhere else.

Narayan

 

[jrl1208]

Thank-you Narayan, formula works fine!!!!!!