Need help getting records "LINED-UP" the proper way (Real Challenge)
Hello Experts,
So I have this Excel spreadsheet with formulas pulling values from a couple sheets.
The ISSUE :( is that the table is not "dynamically" populating adjacent rows with related data to {Column A}, {Column F}, {Column G} as it should. If data dynamically updates, this will ensure validity inherent data values to make sense.
In Tracking Sheet:
Columns which require a "SUMIF-like" formula: E, F, G, H, N, O, P, Q (in yellow)
Can you upload a non macro version of the file? I can't download macro enabled files but will happily take a look.
Thanks
Rob H
Danny Child
It looks like the macros are essential, as it's got User Defined Functions on it.
I've saved a copy as XLSX to help other experts, but that's as far as I can go!
Here's the code that I could see
'Yansane Sep 14 2016 ~~~~~~~~ CODE START
Function GetDetails( _
psInitiativeID As String _
, psOrderType As String _
, psOrderSubType As String _
) As String
'160913, 14 crystal (strive4peace)
'to call, use this formula in a cell:
' =GetDetails(A4, F4)
' where:
' 4 is whatever row number you are on
' column A is the Initiative ID
' column F is the Order Type
'
'initialize return value
GetDetails = ""
'dimension variables
Dim nRow1 As Long _
, nRow2 As Long _
, vReturn As Variant _
, nRow As Long
'initialize values
vReturn = Null
nRow1 = 2
With Sheets("Details")
'get last row of data on the Details sheet
nRow2 = .Cells(.Rows.Count, 1).End(xlUp).Row 'xlUp=-4162
'loop through rows and look for the Initiative ID
For nRow = nRow1 To nRow2
If .Cells(nRow, 1) = psInitiativeID Then
'see if Order Type is also the same
'F is column 6
If .Cells(nRow, 6) = psOrderType Then
If .Cells(nRow, 7) = psOrderSubType Then
'add to what is already there
'seperate values with comma
'H is column 8
'I is column 9
vReturn = (vReturn + ", ") & .Cells(nRow, 9) & " " & .Cells(nRow, 8)
End If
End If
End If
Next nRow
Debug.Print psInitiativeID, vReturn
End With
Your formulas in columns N to Q appear to be working, what is the issue with those?
It seems there is an issue when you have multiple lines relating to one Initiative ID, eg CA025-1-LAN-LC2 has 3 lines in both Tracking and Detail. What are you expecting against each of these?
Maybe you need to add a line identifier where there are multiple lines and then use a INDEX/MATCH combination against the Line ID.
Thanks
Rob H