Solved: Automating Report in Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

coachdan32

Thread Starter
Joined
Nov 13, 2003
Messages
1,021
I am trying to create a template, that when a datafile is pasted into it, it will automatically calculate some totals at the bottom of the sheet. The problem is that the number of rows in the sheet are not a static number and what is contained in column A of each row needs to determine whether the row's data is used in the calculation.

For example, one of the formulae will add the data in column B if the text in column A of the same row is "Delivery", "Prep" or "Performance Support". If the text is anything else, the row is ignored in this formula. Since these three phrases do not always end up in the same row, I am not sure how to accomplish this. I thought about using named ranges, but again with the row unknown I don't think it can work. I believe VLookup is the answer, but I don't know enough about it to set it up - especially with 3 variables. Can anyone help with this? I have attached a copy of the template.
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,896
sorry I gave an Access answer.

Have a look at this copy of your spread sheet, I have added some nested if statements in column AO that does what I think you want. You can now use the values in this column in your summary at the base of B column.
 

Attachments

Joined
Oct 26, 2005
Messages
354
Howdy. Dynamic named ranges will automatically adjust, like this:

=OFFSET(Sheet1!$A:$A1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
 

coachdan32

Thread Starter
Joined
Nov 13, 2003
Messages
1,021
That works fine. It is going to take quite a while to set it all up initially, because I have to do it for all the rows and columns in the table. Once it is done, it should save a lot of time when running the report though. I have another formula in the spreadsheet I'm tackling now. I will post back to this thread if I have problems, but want to give it a go on my own first (that is how you learn, right?)
 

coachdan32

Thread Starter
Joined
Nov 13, 2003
Messages
1,021
OBP,
Sorry. Exegete posted before I got my reply typed. I used your approach on a trial and it works fine, but I have to setup a lot of columns for each spreadsheet. It will still save time in the long run.

Exegete,
I didn't see your post at all until now. I will into it further next week when I return to work.
 
Joined
Jul 25, 2004
Messages
5,458
Note the OFFSET/COUNTA method for defining Range Names can be skewed if your data is non-contiguous or has blanks in your data range. If you are using it for a Pivot Table, however - especially updating/refreshing via code - this method is necessary as others will not work.
 

coachdan32

Thread Starter
Joined
Nov 13, 2003
Messages
1,021
OK, I decided to go OBP's route for this function.

My next obstacle is more complicated I think. I have the following formula in row 31 (Percent of Time Captured):

=C29/(22*8*B32)

A brief explanation of the formula.

In parenthesis, 22 represents the number of working days in the month. I have found a way to handle this, now instead of a static number it will be a cell reference (J1) where this is calculated. The 8 represents 8 hours in a day. This is a static number, that will not change. And B32 is the number of users; this will be a number that is manually entered. So the formula in the parenthesis will be changed to:

(J1*8*B32)

Now, the problem part of the formula. C29 = is the total row for column C, which is hidden. Each row in column C is adding hours entered for each task listed in column A. C29 is where the totals of all tasks (C2:C28) are totaled. The problem here is that it might not always be found in C29 depending on how many tasks are listed in row A. So, I need a way for it to check column A for the words "Grand Total" and if it is present use that row number instance in the formula for Column C.

=C[to be determined]/(J1*8*B32)

Is this possible?
 
Joined
Jul 25, 2004
Messages
5,458
If that was the only "Grand Total" in column A then you could use this formula ...

=INDEX(C:C,MATCH("Grand Total",A:A,0))/(J1*8*B32)
 

coachdan32

Thread Starter
Joined
Nov 13, 2003
Messages
1,021
firefytr,

That is right on the money, except there is something weird going on with the formatting. Look at the attached file. For some reason it is attaching a minus sign in front of the number. As you can see in B38, the numbers are perfect, but in B35 (where the formula is) it adds a minus sign. If I can conquer this obstacle, it will be exactly what I am looking for. Can you see a reason for this?
 

Attachments

Joined
Jul 25, 2004
Messages
5,458
Okay, found it. Look at your formula in B35 ...

=-INDEX(C:C,MATCH("Grand Total",A:A,0))/(J1*8*B36)

Notice anything between the = sign and the INDEX? ;)
 

coachdan32

Thread Starter
Joined
Nov 13, 2003
Messages
1,021
Nice catch. I guess I was digging way deep into the logic of the formula and not looking for a simple solution. It works great.
 
Joined
Jul 25, 2004
Messages
5,458
Great! Glad it works well for you.

If this solves your thread don't forget to mark it Solved: Thread Tools | Mark Solved | Perform Action.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top