Announcement

Collapse
No announcement yet.

Excel question

Collapse
This topic is closed.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel question

    So, I want to set up a way to copy stuff from one excel sheet to another. I use an excel workbook to consolidate multiple outlets' revenue reports into one journal entry. The problem I am having is that, obvious, for the workbook to work, the cells need to remain constant in the individual outlets for them to flow through to the journal properly, and they need to be sorted by account. The report that is auto-generated by the outlets is sorted by amount rather than account, and doesn't have all accounts listed if there wasn't any activity on it. Right now we are getting around that by hand entering it from a print out of the reports into the workbook.
    My question is if there is any way that I can get excel to automatically sort (which, okay, that one I know how to do and is somewhat easy) and compensate for not all accounts being listed (it doesn't do much good to sort in account order, and then because there was no activity on Discover cards have it post Mastercard activity to Discover because when it was auto-generated it didn't leave a blank space for discover, it didn't leave a space for discover at all)?
    I have no idea if my question made sense, and if I really have to I can try to screenshot what I'm talking about, but I hesitate to do so for obvious reasons (yeah, I know, I can redact any important information, but if I can get someone to go ah-hah without having to post screens, I'd appreciate it).
    If you wish to find meaning, listen to the music not the song

  • #2
    Can you list accounts with no activity as having "zero" for sorting/reporting purposes?

    Comment


    • #3
      There's a way to make blank cells into zeros. If there's a zero there, does it sort correctly, or does it still not "see" that space?
      Replace anger management with stupidity management.

      Comment


      • #4
        Okay, so I guess I'll have to do pictures...





        So, my problem isn't blank spaces, as you can see, if the account didn't have activity, it doesn't even make it onto the report, so the cell number for each account will change from day to day.





        And then for some of the accounts, it sorts them by amount rather than by account name, and the worksheet that I need to put it in, needs it by account name.



        So, ultimately, I want to get the reports that are auto-generated (the ones from above), which will vary in formatting from day to day depending on activity, to go into that form there (the worksheet has several of those forms, one for each outlet, and when I'm done it spits out a journal entry that has compiled them all), which obviously needs to remain constant every day. I'm not sure if I'm explaining it any better, but hopefully having the pictures helps.
        If you wish to find meaning, listen to the music not the song

        Comment


        • #5
          How are the figures being copied over? Is it a linked cell/series or vlookup formula?

          Are there any macros attached to these workbooks? If so, are the sorting options performed in the macro?

          Comment


          • #6
            Sum IF statements.

            Its been a while since I've built such a thing. But I'll have a play.

            We did this by having the cover sheet and the data sheet. Data updated on the data sheet. Update sheet is then refreshed and self calculates and you just compare totals and check that the formulas include all the rows. We had 1500-2000 invoice entries and payments to sort into which customer account name, currency, month etc... so had multiple condition IFSUM statements running.

            Here is a basic way I've done it. Note the $ signs in the cell references? They stop the data range from changing as you copy the formula down so that each row from 11 to 14 still looks at the original range - otherwise it would move and 12 would look at Row 3 to 7.




            Obviously with two tabs in a worksheet being used the cells would state the worksheet name on the ones with the data to be looked at and summed - but that is the basic view so that hopefully you can get your head around it. I used to do our entire monthly customer accounting this way with merely checks to make sure it did all come back to the same value.
            I am so SO glad I was not present for this. There would have been an unpleasant duct tape incident. - Joi

            Comment


            • #7
              Also worth your time reading this help page - basically goes through the same thing I did including how to hide errors etc. https://www.found.co.uk/5-great-uses-if-formula-excel/
              I am so SO glad I was not present for this. There would have been an unpleasant duct tape incident. - Joi

              Comment


              • #8
                Gizmo is right, SumIf is probably the best way to go.

                I'm not the best with SumIf's, though, so there is an alternate you can use as long as the data for each category shows up reliably in the same columns.

                Set up a master copy on the first sheet of the workbook, expecting to post your report on the second sheet. The master sheet uses VLookUps to pull the data into the appropriate cell on the cover sheet, by looking for the account name/code.

                I put the VLookUps into If statements using IsError, so any #VALUE errors due to a particular item not being listed churn out a value of zero, instead.

                I've attached an example.
                Attached Files

                Comment

                Working...
                X