1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: Checking values on multiple cells and transferring values to 1 cell

Discussion in 'Business Applications' started by spectacularstuff, May 12, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. spectacularstuff

    spectacularstuff Thread Starter

    Joined:
    May 11, 2006
    Messages:
    9
    Hello Everyone,
    (This is kind of long but I didn't know how else to explain everything)

    There are two parts to my question below.

    I have a spreadsheet with roughly 40 columns

    An Example of some of the columns would be:
    Code:
    B1 = Client Name   E1 = Travel    F1 = Backup    G1 = PC Install  H1 = Data Recovery | X1 = Total    | AE1 = Summary 
    B2 = Tony Smith    E2 = $70.00    F2 = $0.00     G2 = $95.00      H2 = $0.00         | X2 = $165.00  | AE2 = (See Below) 
    B3 = Tom Daniel    E3 = $70.00    F3 = $0.00     G3 = $0.00       H3 = $725.00       | X3 = $795.00  | AE3 = (See Below) 
    B4 = Rene Little   E4 = $70.00    F4 = $125.00   G4 = $95.00      H4 = $995.00       | X4 = $1285.00 | AE4 = (See Below) 
    
    Currently this spreadsheet is being analyzed to be used to track field technician jobs and figure everything out for accounting.

    I have been asked to please make a cell with a list of the information above so that accounting can just copy and paste from that cell into quickbooks in order to send these people out a receipt instead of having to retype everything into quickbooks. This list has to be based upon whether or not there is a value greater than 0 within that cell then it will grab both the heading cell in the first row and the value of that cell it was checking and place it as a line item in another cell.

    I know how to do half of what they are asking and if I was dealing with 1 column I can do the other half.

    I know how to list items in 1 cell using the char(10) function within the cell.

    I don't know how to check multiple cells at once to see if there is a value greater than 0 and then if there is to take the cell 1 text with a : and then the value of the cell I am checking into another cell as a line item.

    Some examples would be the following. From the chart above:
    Let's take row 2.
    Code:
    B2 = Tony Smith    E2 = $70.00    F2 = $0.00     G2 = $95.00      H2 = $0.00         | X2 = $165.00  | AE2 = (See Below)
    
    1st the code would look at B2 and see that there was a value and print that to AE2 & char(10) &
    Tony Smith

    Next, the code would look at E2 and see that there was a value greater than 0 and then print E1 with a colon ":" and E2 to AE2 & char(10) &
    Tony Smith
    Travel: $70.00

    Next, the code would look at F2 and see that there is not a value greater than 0 and skip it and go onto G2. It would see that G2 has a value greater than 0 and then print G1 with a colon ":" and G2 to AE2 & char(10) &
    Tony Smith
    Travel: $70.00
    PC Install: $95.00

    It will go through and look at every cell in that row and perform that same function for all of them.(Currently cells E:Z) until AE looks like:

    Tony Smith
    Travel: $70.00
    PC Install: $95.00
    Total: $165.00

    Thanks for any help

    If I missed anything let me know.

    Wayne
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    The layout of the attached file is based on your description. Over & above the actual data, the file contains a couple of dynamic named ranges, some data validation, and a little VBA.

    Select AG1, then click the downarrow and select from the list of names. Doing this will run the VBA, which builds the 'summary cell' (AI1) & copies it.

    If I do this and then switch to Word and paste as Formatted Text (RTF) it looks OK, however I can't say how "Quickbooks" would 'react'.

    I'm off to the beach shortly, so here's a couple of hints in case you need to make adjustments:

    1. Press CTRL+F3 to see the definitions of the dynamic named ranges

    2. Rightclick the sheet tab & select 'View Code' to see the VBA.

    HTH,
    bomb
     

    Attached Files:

  3. spectacularstuff

    spectacularstuff Thread Starter

    Joined:
    May 11, 2006
    Messages:
    9
    Wow, thank you. I did not know I could upload a file, I would have uploaded mine.

    I thought my knowledge of excel was fantastic until I met everyone in the forums... lol

    You are all phenomenal!

    Thank you for your help. Enjoy the beach. I just got up. It is just around 9am EST.

    i will check out your file now and post comments later.

    Wayne
     
  4. spectacularstuff

    spectacularstuff Thread Starter

    Joined:
    May 11, 2006
    Messages:
    9
    Hello,

    Okay, I was playing with that file. In column AI it puts everything in separate cells. I have to leave for the day but when i come back I will upload a version of my excel sheet so that you can see where I am coming from.

    Thank you for that.

    Wayne
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    :confused: ; not for me. It's supposed to build a variable "x" (line by line). There's no actual writing to the worksheet until the (virtually last) line:

    Range("AI1") = Left(x, Len(x) - 1)


    which should just dump "x" to AI1 (bar the last character, which is a carriage return, so there's no blank trailing line).

    Looking forward to seeing your file -- just make sure you don't include any sensitive data. Your best bet would be to make a copy of it with most of the rows deleted and any names replaced with bogus ones, then post that.

    Rgds,
    bomb
     
  6. spectacularstuff

    spectacularstuff Thread Starter

    Joined:
    May 11, 2006
    Messages:
    9
    Okay,

    Attached is my excel file. This has all bogus names and numbers in it but it will give you a good idea of what I am attempting to do.

    We are almost there. You have the sheet doing exactly what I need but it looks like it is going 1 step extra by placing all of that information into AG prior to going into AI. I don't have enough space for it to go to AG first.

    Thanks for any help.

    Wayne
     

    Attached Files:

  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Well it's gotta go somewhere for the purpose of getting it onto the clipboard. :)

    The attachment is your file modified. Selecting (clicking on) a job name (col B of Sheet1) runs the sheet code (which you can see by rightclicking the sheet tab & selecting 'View Code').

    The formulas in Receipt!B2:B23 pull in the data from Sheet1 for selected job name. The code then compiles this data, as required, into Receipt!A25 and copies it.

    From there, it should Paste Special > Formatted Text (RTF) into Word OK, although as before I don't know if the same can be said for "quickbooks".

    HTH,
    bomb
     

    Attached Files:

  8. spectacularstuff

    spectacularstuff Thread Starter

    Joined:
    May 11, 2006
    Messages:
    9
    Thank you for your help bomb.

    I am going to check the script from another computer on Monday. Currently the one I am on for the weekend only has Open Office on it and I cannot get that script to work in it. I click on the user's name and nothing happens.

    I will be back on one with excel come this Tuesday. I will reply with any questions then. Thanks again.

    Wayne
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I don't think OpenOffice yet fully supports VBA..
     
  10. spectacularstuff

    spectacularstuff Thread Starter

    Joined:
    May 11, 2006
    Messages:
    9
    Wow Bomb,

    That works pretty nice. Let me ask you the following questions
    We will be adding more columns in the future as needed

    1) If we add more columns, will the code automatically adjust itself or do I have to go somewhere and manually change it?

    2) Is it possible to have the itemization (receipt sheet: A1:A23) on the receipt page and have the grouped results (receipt sheet: A25) copied over sheet 1 cell AG2 if we are working with cell B2,
    AG3 if we are working with cell B3
    AG4 if we are working with cell B4
    etc etc etc.

    Thanks Wayne
     
  11. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I've updatd the code in the attached, so that the 'grouped results' go to col AG.

    For added columns, the 2 code lines you need to watch are:

    For Each Cell In Target.Offset(0, 3).Resize(1, 22)

    'Target' = selected cell in column B, so the 'For Each' range = B# offset zero rows & 3 columns (E#) and then resized one row & 22 columns (Z#).

    Target.Offset(0, 31) = Left(x, Len(x) - 1)

    This governs the grouped results destination -- B# offset zero rows & 31 columns = AG#.

    For the Receipts sheet, you'd just extend the column A labels and column B formulas down.

    HTH,
    bomb
     

    Attached Files:

  12. spectacularstuff

    spectacularstuff Thread Starter

    Joined:
    May 11, 2006
    Messages:
    9
    I have not had a chance yet, to look at everything on an excel box. I have been away from the office for a couple of days. I should be back there tomorrow to look at this. Thanks again.

    Wayne
     
  13. spectacularstuff

    spectacularstuff Thread Starter

    Joined:
    May 11, 2006
    Messages:
    9
    I just got a chance to look at it. Thank you very much. That works great.

    They wanted to know if I can add

    Company Name at the top of the Summary. I believe I just have to edit the macro to do this.

    Thanks again for all of your help.

    Wayne
     
  14. spectacularstuff

    spectacularstuff Thread Starter

    Joined:
    May 11, 2006
    Messages:
    9
    Okay, I have one more question.
    The date as you see is in column D.

    How do I add the date in the Macro.

    I added the following:
    x = "Spectacular Computer Repair Services performed on " & Chr(10) & Target & Chr(10)

    Right after the "Spectacular Computer Repair Services performed on " & (Date Here) & Chr(10)

    If the row is row 2, I have to pull the date from D2.
    If the row is row 3, I have to pull the date from D3.
    etc etc etc.

    How do I write that in a Macro?

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("B2", Range("B65536").End(xlUp))) Is Nothing Then Exit Sub
    If Selection.Cells.Count > 1 Then Exit Sub
    
    Worksheets("Receipt").Range("A1") = Target
    Worksheets("Receipt").Range("B1") = Target.Row
    
    x = "Spectacular Computer Repair Services performed on " & Chr(10) & Target & Chr(10)
    For Each Cell In Target.Offset(0, 3).Resize(1, 22)
    If Cell <> 0 Then
    x = x & Cell.Offset(1 - Cell.Row, 0) & ": " & Format(Cell, "$0.00") & Chr(10)
    End If
    Next Cell
    Target.Offset(0, 31) = Left(x, Len(x) - 1)
    
    End Sub
    
    Thanks,

    Wayne
     
  15. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Which version are you using now? -- I have Date in col C. :confused:

    To put cy name & Date in the "top row" of col AG, do it in one hit at the end by editing one line -- from:

    Target.Offset(0, 31) = Left(x, Len(x) - 1)

    to:

    Target.Offset(0, 31) = "Spectacular Computer Repair Services performed on " & _
    Cells(Target.Row, 3) & Chr(10) & Left(x, Len(x) - 1)
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/466641

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice