Potbellyyemi
New Member
- Joined
- Mar 16, 2018
- Messages
- 5
- Office Version
- 365
- Platform
- Windows
Hi all,
I want to extract data from my P&L budget and forecast (on different tabs in excel) based on a date I select from my drop down list. I basically want to do a variance analysis between the budget and forecast P&L. Both my budget and forecast income statement look like the below in terms of layout
<tbody>
</tbody>
And the variance analysis tab looks like the below with a little drop down list above it. I have already done the drop down list using data validation and the dates in the list look exactly like the above (i.e. Jan17 - Mar 17 etc.) but what formulas can I use to pull the above data in a P&L into the below? vlookup doesn't work as it only pulls data from one specified column, so if I change the date selection in the drop down list, the numbers wont pull.
<tbody>
</tbody>
I hope this all makes sense. Any help on this will be much appreciated!
Thanks!
I want to extract data from my P&L budget and forecast (on different tabs in excel) based on a date I select from my drop down list. I basically want to do a variance analysis between the budget and forecast P&L. Both my budget and forecast income statement look like the below in terms of layout
Jan 17 - Mar 17 | Apr 17 - Jun 17 | Jul 17 - Sep 17 | Oct 17 - Dec 17 | |
Establishment costs <strike></strike> | <strike></strike>100 | 100 | 150 | 200 |
Salaries <strike></strike> | <strike></strike>1000 | 1000 | 1000 | 1000 |
Sundries <strike></strike> | <strike></strike>500 | 500 | 500 | 500 |
Insurance <strike></strike> | <strike></strike>200 | 200 | 200 | 200 |
TOTAL P&L | 1800 | 1800 | 1850 | 1900 |
<tbody>
</tbody>
And the variance analysis tab looks like the below with a little drop down list above it. I have already done the drop down list using data validation and the dates in the list look exactly like the above (i.e. Jan17 - Mar 17 etc.) but what formulas can I use to pull the above data in a P&L into the below? vlookup doesn't work as it only pulls data from one specified column, so if I change the date selection in the drop down list, the numbers wont pull.
Budget | Forecast | Variance | |
Establishment costs <strike></strike> | <strike></strike> | ||
Salaries <strike></strike> | <strike></strike> | ||
Sundries <strike></strike> | <strike></strike> |
<tbody>
</tbody>
I hope this all makes sense. Any help on this will be much appreciated!
Thanks!