Can't get formula to recognize correct data

cklassen · #1 05-17-2012, 10:31 AM

What I need the spreadsheet formulas to do is for cell A20 = cell AF5, cell B20 = cell AG5, cell C20 = cell AH5, etc. and then cell A21 = cell AF8, cell B21 = cell AG8, cell C21 = cell AH8, etc., until the data reaches AF38 (which says Invoice Number). At that point the rest of the cells on the cheque stub should return no data. This way I can create custom cheque stubs for each cheque no matter how many invoices are paid on each cheque.
The formula I currently have in A20 is like B6, B9, B10, B11, etc. but it's not doing what I want it to.

Please help, I've been working on trying to get this set up for at least 20 hrs of my time.

macropod · #2 05-19-2012, 01:25 AM

Hi cklassen,

Your data are not exactly structured for ease of processing. It looks rather like it's been downloaded from some other system without any effort to organise it into a useful layout. Your Info sheet, in particular would benefit from being cleaned up via a macro before you attempt any further processing. For example:

Code:

Sub Demo()
Application.ScreenUpdating = False
Dim LastRow As Long, i As Long, j As Long
With ThisWorkbook
  With .Worksheets("Info")
    LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    For i = 1 To LastRow
      If .Cells(i, 28).Value <> "" Then
        If .Cells(i, 28).Value = "JobCat" Then
          .Cells(i, 29).Value = .Cells(i, 27).Value
          .Cells(i, 27).Value = "Tax"
        Else
          .Cells(i - 1, 28).Value = .Cells(i, 28).Value
          .Cells(i, 28).Value = ""
          .Cells(i - 1, 29).Value = .Cells(i, 27).Value
          .Cells(i, 27).Value = ""
        End If
      End If
    Next
    .Range("S1:AD1").Rows.Delete Shift:=xlUp
    .Range("S2:S" & LastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    LastRow = .Range("S" & .Cells.SpecialCells(xlCellTypeLastCell).Row).End(xlUp).Row
    .Range("L1:R1").EntireColumn.Delete Shift:=xlLeft
    j = 2
    For i = 3 To LastRow
      If .Cells(i, 1).Value <> "" Then
        j = i
      Else
        .Cells(i, 4).Value = .Cells(j, 4).Value
        .Cells(i, 5).Value = .Cells(j, 5).Value
        .Cells(i, 6).Value = .Cells(j, 6).Value
        .Cells(i, 7).Value = .Cells(j, 7).Value
        .Cells(i, 8).Value = .Cells(j, 8).Value
        .Cells(i, 10).Value = .Cells(j, 10).Value
      End If
    Next
  End With
End With
Application.ScreenUpdating = True
End Sub

Indeed, you could probably achieve the whole of the desired outcome very quickly with a macro and no formulae, but you should still find it much easier to process the data with formulae after the cleanup done by the above macro.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Sum Formula in the range with Numeric and NonNumeric data cells	Spanec	Excel	2	01-12-2012 09:15 AM
Changed data base name. Outlook doesn't recognize.	Skip	Outlook	1	10-20-2011 10:55 AM
PowerPoint won't recognize embed codes from YouTube or Vimeo	JulianC	PowerPoint	1	10-04-2011 03:03 PM
Word crashed and now doesn't recognize file as a Word doc?	sara	Word	0	09-15-2010 02:12 PM
Cross-reference together with Mathtype formula is not working correct	Santa_Clause	Word	2	02-02-2010 04:37 AM