Counter in Excel

Question

My file has 50 sheet inside, which each sheet is a standard form. Lets name as Form1, Form2, Form3.

I want to create a print sheet, where I select which form I want to see, so that I don't need to navigate between sheet to find the wanted one. And I want to have counter in this print sheet that It counts everytime I send to a printer.

What did I do so far? I created this formula, where M1 is a drop-down list with the form names. So If I want to see the Form2, I select Form2 in the dropdown list, and the formula displays all Form2 sheet.

=IF($M$1="Form1";IF('Form1'!A1="";"";'Form1'!A1);"")&IF($M$1="Form2";IF('Form2'!A1="";"";'Form2'!A1);"")&IF($M$1="Form3";IF('Form3'!A1="";"";'Form3'!A1);"")

It's working fine, because all forms are standardized.

After choosing the selected form, I need to insert a ID number from my company and then print it.

I would like to know if it's possible to create a counter in this sheet that counts how many times I printed... Or anything that would count in chronological order when I save or change anything...

Answer 1

All of your requests can be accommodated. But only using VBA I'm afraid.

Keep the dropdown but also add a button that triggers a macro.

In the macro, read the cell with the dropdown and send the appropriate sheet to print. Then update the appropriate counter which can be in a set of cells on the same page as the dropdown and button.

To count the number of saves, you need a macro that before_save function. You need to search for that, I think there may even be an article about it on my blog, can't remember, it's been a few years since I've done it. The principal is the same as the previous macro in that you simply add 1 to the appropriate cell.

Answer 2

A somewhat cleaner way of building the “print sheet” that will show the contents of one of the other 50 sheets is to set cell A1 to

=INDIRECT($M$1 & "!A1")

which builds a text string that looks like the qualified name of the cell that you want to see, and then uses the INDIRECT() function to de-reference that string (i.e., to treat it as a cell address and retrieve the contents of the identified cell).  With this, you won’t need to change your formula(s) when you add Form51.  Warnings:

• This may cause Excel to do a lot of work (i.e., spend a lot of time) “recalculating” these indirect cells whenever anything changes (not just M1).  I’m not sure whether it would be any worse than what you have now.
• Be sure to avoid the temptation to change data or enter new data into the “print sheet” when you want to update Form42.  (Of course, you have this issue now.)  You may be able to protect yourself from such accidents by protecting the print sheet.

There are other ways to construct the print sheet; e.g., using VBA to

• create a user-defined function that might replace the INDIRECT() call, but operate more smoothly, or
• actually copy all the data from Formnn to the print sheet whenever you change M1.

You can count changes by writing a Worksheet_Change VBA routine.

I don’t know any way for the sheet’s logic to be able to count/detect when you print it.  However, you might be able to achieve the desired effect by writing a print routine in VBA.  A

PrintOut , , , True

statement will print the sheet for you; add whatever other actions you want (e.g., incrementing a counter).  (You just have to remember to print using this macro.)  The fourth parameter to PrintOut is Preview, set to True to have Microsoft Excel invoke print preview before printing the sheet (thus giving you the option to cancel) or False (or omitted) to print the sheet immediately, unconditionally.

See How do I add VBA in MS Office? for information on, well, you know.