Random Microsoft Excel Question

[Dan Hocker]

I don't know if anyone here has any advanced knowledge of Microsoft Excel, but if anyone does I could use some help. I have a fairly advanced knowledge of it, but I'm not sure if what I want to do can even be done.

So for a little bit of an explanation here, I have been trying to improve my sales / royalties tracking spreadsheet for our eBook sales. Currently each author has a separate tab in the spreadsheet which looks up the information relative to that author and displays it. I would instead like to have a single sheet in which I type the authors name into a field then it displays the relevant information below for the author. I know how to do everything to make this happen except how to show / hide formatting if the author has more than one book.

So for example Joe has 3 books but David only has 1 book. I don't want all the blank values for Joe's 3 books shown when displaying David's information.

I'm not really sure if this can be done. Let me know if anyone has any insight. I know this can be done with Microsoft Access, but I have very little knowledge of to anything but very basic things in Access.

[Martin]

In my experience anything you can do in Access can also be completed using Excel. The primary difference being that Access utilizes tables of data for joining. Excel either uses data in existing spreadsheets or remote database connects. If I am understanding you correctly you have a worksheet for each author with that authors 'product eligible for commission'. You also have a single worksheet with each 'Author eligible for commission'. So if you have five authors eligible for commission you will have one 'Author eligible for commission' worksheets and five 'product eligible for commission' worksheets for a total if six worksheets.

You would enter your information on the 'product eligible for commission' worksheets and then refresh the 'author eligible for commission' worksheet for updated totals.

This is also assuming that the data you need must be entered onto the Excel spreadsheet. If for example the sales are stored on the websites sales tables and you have remote access you may just need some code in the 'author eligible for commission' spreadsheet and then you re-run the query at your convenience.

Here is the scoop. I had surgery a few days ago and am currently off work. What I said above may not make any sense (the joy of pain meds). If you have a couple example spreadsheets you could send me. They do not even need to be real data. With a brief description of what type of information you have to enter and what you need out of the spreadsheet once it is done I will take a stab at creating something to meet your needs. It is certainly possible that I am unable to help but it is equally possible that I can create a query that simplifies things for you. I have the time so I would be happy to try.

[Dan Hocker]

I've actually mostly figured it out at this point. I think it's probably easier to do in Access / has less of a load on the system, but it does appear to be working in Excell. My biggest problem now will be handling the information for books that have multiple authors. Even as I'm typing this I have an idea for figuring that out as well. What I've found for showing / hiding the formatting was conditional formatting based on a formula.

[Martin]

Once you get Excel working correctly you can probably perform small adjustments to get it performing as well as Access. If you decide you want me to take a look at it let me know.