Extracting data from a P&L depending on date in the drop down list

[Potbellyyemi]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan 17 - Mar 17[/TD]
[TD]Apr 17 - Jun 17[/TD]
[TD]Jul 17 - Sep 17[/TD]
[TD]Oct 17 - Dec 17[/TD]
[/TR]
[TR]
[TD]

Establishment costs​

<strike></strike>[/TD]
[TD]<strike></strike>100[/TD]
[TD]100[/TD]
[TD]150[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]

Salaries​

<strike></strike>[/TD]
[TD]<strike></strike>1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]

Sundries​

<strike></strike>[/TD]
[TD]<strike></strike>500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]

Insurance​

<strike></strike>[/TD]
[TD]<strike></strike>200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]TOTAL P&L[/TD]
[TD]1800[/TD]
[TD]1800[/TD]
[TD]1850[/TD]
[TD]1900[/TD]
[/TR]
</tbody>[/TABLE]

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.

​

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Budget[/TD]
[TD]Forecast[/TD]
[TD]Variance[/TD]
[/TR]
[TR]
[TD]

Establishment costs​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Salaries ​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Sundries ​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I hope this all makes sense. Any help on this will be much appreciated!
Thanks!
​

 

[ranman256]

in a form youd have a combo box that loaded the date ranges from the sheets.
user picks a date range,
then click a'Start' button and run the macro: MergeData

Option Explicit
Private mcolCatas As New Collection
Private mcolVals As New Collection
Private mvDate


Public Sub MergeData()
Merge1Date cboBox
'Merge1Date "Apr 17 - Jun 17"


Set mcolCatas = Nothing
Set mcolVals = Nothing
End Sub


Sub Merge1Date(ByVal pvDate)
Const kFOR = "Forcast"
Const kBUD = "Budget"
Dim sht As Worksheet
Dim vShName, vCata, vAmt, vWord
Dim i As Integer


   'fill the variables
mvDate = pvDate
CollectCatas kFOR
CollectCatas kBUD


Sheets.Add
Set sht = ActiveSheet
     
     'post the merged catagories
Range("A2").Select
For Each vWord In mcolCatas
  ActiveCell.Value = vWord
  NextRow
Next


   'post the merge values to new sheet
Range("a1").Value = pvDate
Range("B1").Value = "Budget"
Range("c1").Value = "Forecast"


For Each vWord In mcolVals
   
   BreakupWords vWord, vShName, vCata, vAmt
   
   Select Case vShName
      Case kBUD
         i = 1
      Case kFOR
         i = 2
   End Select
   
   Range("A2").Select
   While ActiveCell.Value <> ""
      If ActiveCell.Value = vCata Then
         ActiveCell.Offset(0, i).Value = vAmt
         GoTo nextVal
      End If
      
      NextRow
   Wend
nextVal:
Next
Set sht = Nothing
End Sub


Private Sub CollectCatas(ByVal pvShtName)
Dim sCata As String
Dim iDteCol As Integer
Dim vVal, vWord


On Error Resume Next


    'find the column with the date
Sheets(pvShtName).Activate
Range("B1").Select
While ActiveCell.Value <> ""
  If ActiveCell.Value = mvDate Then
       iDteCol = ActiveCell.Column
       GoTo skipNext
  End If
  NextCol
Wend


MsgBox mvDate & " date not found in " & pvShtName


skipNext:
   'get the row catagory names
Range("A2").Select
While ActiveCell.Value <> ""
  sCata = ActiveCell.Value
  vVal = ActiveCell.Offset(0, iDteCol - 1).Value
  vWord = pvShtName & ":" & sCata & ":" & vVal
  
  mcolCatas.Add sCata, sCata
  mcolVals.Add vWord
  
  NextRow
Wend
End Sub


Private Sub NextRow()
ActiveCell.Offset(1, 0).Select
End Sub


Private Sub NextCol()
   ActiveCell.Offset(0, 1).Select
End Sub


Private Sub BreakupWords(ByVal pvWord, pvShtName, pvCata, pvAmt)
Dim i As Integer


   i = InStr(pvWord, ":")
   pvShtName = Left(pvWord, i - 1)
   pvWord = Mid(pvWord, i + 1)
   i = InStr(pvWord, ":")
   pvCata = Left(pvWord, i - 1)
   pvAmt = Mid(pvWord, i + 1)
End Sub