Microsoft Office Forums Can't get formula to recognize correct data

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-17-2012, 10:31 AM
cklassen cklassen is offline Can't get formula to recognize correct data Windows 7 32bit Can't get formula to recognize correct data Office 2010 32bit
Novice
Can't get formula to recognize correct data
 
Join Date: May 2012
Posts: 1
cklassen is on a distinguished road
Default Can't get formula to recognize correct data


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.
Attached Files
File Type: xlsx Cheques.xlsx (32.8 KB, 4 views)
Reply With Quote
  #2  
Old 05-19-2012, 01:25 AM
macropod's Avatar
macropod macropod is offline Can't get formula to recognize correct data Windows 7 64bit Can't get formula to recognize correct data Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,541
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't get formula to recognize correct data Sum Formula in the range with Numeric and NonNumeric data cells Spanec Excel 2 01-12-2012 09:15 AM
Can't get formula to recognize correct data Changed data base name. Outlook doesn't recognize. Skip Outlook 1 10-20-2011 10:55 AM
Can't get formula to recognize correct data 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
Can't get formula to recognize correct data Cross-reference together with Mathtype formula is not working correct Santa_Clause Word 2 02-02-2010 04:37 AM


All times are GMT -7. The time now is 08:06 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft