Use of Dynamic Formula to Consolidate two Tabs where Data Validation is done twice

[raghav.bhola]

Hi ,

am trying to consolidate in Consol Tab , sum of two cities data for each of P&L Line items ( all rows Basically ) with two data validation :
1. IF the figures are in USD , it should basically divide City 3 Data by exchange rate in Cell B3 of Consol Tab and add tabs City 1 Forecast & City 3 Forecast

2 Also , the second data validation is If year is changed in Cell E1 , then it it should total for 12 months of that year only from two tabs City 1 Forecast and City 3 Forecast .
Am trying to use offset function along with IF and SUM Function across the Consol tab to data validation work but this is not working.
Kindly help at urgent level

[Ochimus]

raghav,

Think the attached does what you want.

There was no "Exchange Rate" in B3 of the Consol tab, so I added one.

I added a Year DropList in H4 of the Consol tab, which sets the months in Row 4, enabling the formula to find the relevant columns in the two Forecasting sheets.

Code in B6 of the Consol sheet works as follows:

In row 6 of the Consol sheet ("BlackCar"), it puts into each Column the values in the matching cell in the City 1 Forecasting sheets, and adds the values in the matching cell in the City 3 Forecasting sheets

If both the Consol and City Forecast use the same currency, the numbers are copied "as is".

If either Forecast sheet uses a different currency to your choice in the Consol sheet, the value copied across is set by the Consol sheet.
If the Consul is set for Dollars, and the Forecast is in Euros, it multiples the Forecast by the Exchange rate (1 Euro = $1.14)
If the Consol is set for Euros, and the Forecast is in Dollars, it divides the Forecast by the Exchange rate ($1 = $0.87)

Copy the Code across all twelve columns, and down each row, adjusting the references appropriately.

You can test it by (a) changing the currency on any of the sheets to see the effect, (b) change the Exchange Rate, and (c) Select a different Year in H1.

=IF(B1='City1 Forecast'!$D$1,INDEX('City1 Forecast'!$B5:$AJ5,MATCH(B$4,'City1 Forecast'!$B$3:$AK$3)),IF(B1="USD",INDEX('City1 Forecast'!$B$5:$AJ$5,MATCH(B$4,'City1 Forecast'!$B$3:$AK$3)*($B$2)),INDEX('City1 Forecast'!$B$5:$AJ$5,MATCH($B$4,'City1 Forecast'!$B$3:$AK$3)/$B$2)))+IF($B$1='City 3 Forecast'!$C$1,INDEX('City 3 Forecast'!$B$5:$AN$5,MATCH(B$4,'City 3 Forecast'!$B$3:$AN$3)),IF(B1="USD",INDEX('City 3 Forecast'!$B$5:$AN$5,MATCH(B$4,'City 3 Forecast'!$B$3:$AN$3)*$B$2),INDEX('City 3 Forecast'!$B$5:$AN$5,MATCH($B$4,'City 3 Forecast'!$B$3:$AN$3)*$B$2)))

(if the currency in D1 of City 1 Forecast matches the currency in B1 on the Consol, find in Forecast row 5 the column matching the date in B4.
If the currencies differ, multiply or divide the value by the exchange rate.
Add the same calculation for the City 3 Forecast)

Ochimus