Hi,
I try to solve a VBA problem. I like to have a dropdown list with the choices of January to December in the worksheet "Net Sales-Budg-FC-PY". This worksheet links to 3 different worksheets with numbers of each month (jan-dec) in a different column (5-16). Each worksheet has the same column number from jan-dec.
I like with VBA to change the formula on each cell Range("E5:S238") when I choice a different month.
The difficulty is that the formula "Find and Replace" don't replace a part of a formula or the column number of the formula between semicolon ";". My formula look like this each cell Range("E5:S238"):
- cell E5
=IF(ISNB(VLOOKUP($A5;'Net Sales per brand'!$A$4:$AH$301;5;FALSE))=TRUE;0VLOOKUP($A5;'Net Sales per brand'!$A$4:$AH$301;5;FALSE))
- cell E6
=IF(ISNB(VLOOKUP($A6;'Net Sales per brand'!$A$4:$AH$301;5;FALSE))=TRUE;0;VLOOKUP($A6;'Net Sales per brand'!$A$4:$AH$301;5;FALSE))
and so on...
And
- cell F5
=IF(ISNB(VLOOKUP($A5;'Budget 2016'!$A$6:$AJ$393;5;FALSE))=TRUE;0;VLOOKUP($A5;'Budget 2016'!$A$6:$AJ$393;5;FALSE))
- cell F6
=IF(ISNB(VLOOKUP($A6;'Budget 2016'!$A$6:$AJ$393;5;FALSE))=TRUE;0;VLOOKUP($A6;'Budget 2016'!$A$6:$AJ$393;5;FALSE))
and so on...
Dropdown list:
To choose February: January with the column ;5; have to be changed in February with column ;6;
To choose March: February with the column ;6; have to be changed in March with column ;7;
and so on...
Can somebody help me with the wright formula? Thanks in advance!
Bookmarks