Excel function to count + display

**Silencer001** · 04-26-2012, 08:12 AM

Hi everyone,

I am looking for a function to let me administer my network licenses a little bit better.
Please let me explain what I mean:

On my excel workbook I have 2 sheets:

PC
Licences

On the sheet PC, I fill in some general information about the PC and also the license keys for different programs that belong to an existing PC
On the sheet Licenses, I fill in all the information that is linked to a license.

When I open the license sheet, I want to know exactly who is using the licenses and how many there are in use. So the licenses sheet is only for viewing purposes and the real information in entered in the pc sheet.

I have enclosed a xls-sheet on how I thought it would be the best way possible. The red characters must be auto updated when I change something in the licenses sheet.

I hope you can help me with this.

Kind regards

**JBeaucaire** · 04-26-2012, 08:37 AM

Without adding VBA, I'd suggest having names appear one to a cell in F:K.

In E2, then copied down:

=COUNTIF('PC''s'!$14:$14, Licenses!C2)

In F2, this array formula is entered:

=IF(COLUMN(A$1)>$E2, "", INDEX('PC''s'!$E$5:$Z$5&" ("&'PC''s'!$E$4:$Z$4&")", SMALL(IF('PC''s'!$E$14:$Z$14=$C2, COLUMN('PC''s'!$E$5:$Z$5)-4,""), COLUMN(A$1))))

....and is confirmed by pressing CTRL-SHIFT-ENTER. This will activate the array and you will see curly braces { } appear around your formula.

Now copy F2 to the right through K2 or so.
Now copy F2:K2 and paste in F3:K8.

**NBVC** · 04-26-2012, 08:37 AM

Add this code to your VB editor (ALT+F11, Insert|Module)

Please Login or Register  to view this content.

Then in your sheet use formula:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.

You will need to adjust ranges to suit actual data, then reconfirm with CSE key combo.

**Silencer001** · 05-10-2012, 07:48 AM

Wow guys, thanks for the realy fast reply! I feel bad that I am unable to reply so late!!

I just downloaded the licenses.xlsx from JBeaucaire and it worked perfectly.

I also tried the method of NBVC, but I am unable to get it working. I saved the module, but when I want to use the formula, Excel says that the formula is wrong.
It highlights "C2,SUBSTITUTE" in the middle of the formula. Any suggestions to get it working? I want to try both of your methods, because you did the effort to reply to my threat!

Thanks in again for the help and quick reply! Total aces!!

Kind regards

**NBVC** · 05-10-2012, 08:00 AM

Here you go. It works fine for me. I think it is because your EXCEL version uses semi-colons as argument separators. When you upload a workbook, the separators change automatically to your version.

**Silencer001** · 05-10-2012, 08:03 AM

Wow, thanks NBVC for the quick and correct reply!
It's working now for me also! Maybe it's because I'm using the dutch version the formulas didn't work?

**NBVC** · 05-10-2012, 08:18 AM

Yes, as I mentioned above, your version uses semicolons instead of commas to separate the arguments in the formulas.

**Silencer001** · 05-10-2012, 08:44 AM

Ah ok, now I see! I will close this topic because everything is working now!

Thanks again for the quick help, guys!!

Kind regards,

Excel function to count + display

LinkBack

Thread Tools

Rate This Thread

Display

Excel function to count + display

Re: Excel function to count + display

Re: Excel function to count + display

Re: Excel function to count + display

Re: Excel function to count + display

Re: Excel function to count + display

Re: Excel function to count + display

Re: Excel function to count + display

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions