Okay, so it seems basic but I do not know how to do it. I have 2 or 3 sheets with the same fields. There are account numbers, 36 on each worksheet. We add new account numbers randomly and want to keep track of how many we have listed.

At the top of worksheet one, there is a TOTAL ACCOUNTS.... this is the sum of all the accounts we have logged. If there are more than 36 we move to worksheet two. if that fill up, the 73rd account goes to worksheet 3.

I'm trying to figure out what I can type to make the TOTAL ACCOUNTS COUNT how many times there is an account number entered. On each worksheet there appears Account in 3 columns and 12 rows... in the space to the right of each of these will either be blanks or an account number.

I was thinking vlookup or if functions, but I dont know how to specify the 36 fields on each sheet to look at.... let alone the other worksheets if more accounts are added.

Any help or clarification, please let me know. Thanks guys.

7/13/2009 12:11:35 PM

i started off OK with your post, then was utterly confused by the 3rd paragraph.

Can you post screenshots, or maybe an example Excel file (with dummy information entered)?

7/13/2009 12:31:54 PM

=COUNTIF(Sheet1!A:A,123)+COUNTIF(Sheet2!A:A,123)+COUNTIF(Sheet3!A:A,123)

Where A:A is the column containing acct numbers and the acct number in question is 123

7/13/2009 12:40:10 PM

^ only problem is there are other things in those columns beside the account number that I do not need to count.

^^
Yeah, I understand completely.

Lets say there are 36 fields on each worksheet that I need to count, IF something is in that field.... maybe 1-3 sheets are going to be used depending on the number of accounts. The problem I am having is that the fields are not in a column together.

It will always be for example:

C9..... J9........ K9
C17.... J17......K17
continue this 10 more times till you get 36 fields per sheet.

Then, there is a seconds sheet with the same fields used.

I need to count the times something is entered in one of these spots on each worksheet?

I hope that is more clear, thank you all.

[Edited on July 13, 2009 at 1:58 PM. Reason : 2]

[Edited on July 13, 2009 at 1:59 PM. Reason : 2]

7/13/2009 1:58:04 PM

So don't use a range, just do a COUNTIF of each cell. I think you can do it for one sheet and then just paste into the next sheet and it will be valid for that sheet (maybe).

7/13/2009 2:15:54 PM

so, what is wrong with:

=countif(c9,">0")

seems like if there was an account number (vs. being left blank) then excel would count this, but it just leaves my formula there. Is that not a valid criteria?

Once I get the 36 countif's in there, I need to go in and add 72 more for sheets 2 and 3? Surely there is an easier way?

7/13/2009 2:47:25 PM

Did you get this figured out? I don't have excel installed on this machine to test what was up with the single cell countif command, but generally, you have 36 different cells spread out that you need to query, and if the cells repeat on the next page, then you should be able to copy the formula from sheet 1 onto sheet 2 and it will work no problem.

The only other way I can think of to do COUNTIF as a range would be to label all those "account" cells as ACCOUNT and then a single =COUNTIF(ACCOUNT,">0") should work, but I don't know if you can have a range setup with individual cells like that.

The only real way to learn new aspects of excel is to google and try things to see what works.

7/13/2009 7:17:01 PM

Unrelated question but I didn't want to start a new topic:

I have an excel file that gets updated with data nightly from our database. Its large (~0.5 Million rows, a few columns)

Its a pain in the ass to open, and multiple people need to look at summaries of it (averages, distributions, etc) . The summaries are things that I cannot do in SQL easily (or at all). So I need a solution for people to have access to these summaries quickly and painlessly

This is what I'm thinking. Have this file, called File1, get loaded with the data nightly and also contain the summaries that people need. Have a second file, called File2 that copies (not links) the summary data into itself nightly, after file1 has been updated. Then in the morning, people can view file2 easily without having to load any of the data in file1.

I don't know how to automate the second part. I can load the data into file1 just fine, but how would I open file2 automatically and copy the summaries from file1 into it? And would this even work? thanks!

7/15/2009 5:18:18 PM

can you not just create a reference to the summaries in a seperate file (file 2)

7/15/2009 6:14:46 PM

Nevermind, im an idiot. That's exactly what I need to do ^

[Edited on July 15, 2009 at 6:23 PM. Reason : .]

7/15/2009 6:20:05 PM

or record a local macro -- saved in your settings, not the excel file that would be replaced.
Just have the macro load the file, do all the modifications to file 1, then save the modified to file 2.
Just have to run the macro every morning.

Or do it in Access.

[Edited on July 15, 2009 at 7:05 PM. Reason : -]

7/15/2009 7:04:46 PM

K, ive got it all set up, Im using windows schedualer to load file1 each morning, which triggers a macro to refresh the query and copy the summary data to another sheet, which is referenced by file2. then it gets saved.

It all works great, except i want it to close when done. That's easy, but then it will close every time it's opened. I'd like it only to close if i fail to do something, like click a messagebox or something like that. is this possible?

7/21/2009 4:27:56 PM

yes

this is pretty close to what you need

inputResponse = msgbox("Do you want to close",vbYesNo)
if inputResponse = vbYes then
**close code**
else
**don't close code**
end if

7/21/2009 7:18:51 PM

^ the problem is I dont want to be there to click 'close'

I came up with a shitty workaround, I set it so that it basically does that ^ if it's opened after 7 am, and I have windows scheduler open it at 5 am so it goes through and does its thing and closes on its own.

7/21/2009 9:46:07 PM