Using Boolean to compile knowledge...

First post here. "Hello!"

I have minimal excel knowledge, but enough to get by. I have 2 years in C++ programming.

Here's what I'm trying to do:

I'm trying to create a workbook to keep track of flags we send out, and receive back. We have a lot going and coming, so I figured the best way to do this would be to break down each region on different sheets. But I need to create a check box that says something like, "Yes/No" or "Here/Gone" or something like that. THEN, I need to take that information, and put it on the front sheet of the workbook so I know how many we have in/out total. Does this make sense? I know how I would program this in C++, but excel is a bit different. Thank you for your time and thoughts on this.

/Hawk

Have a read through this similar thread...

Instead of >0 conditions to get data... use your Boolean results (=TRUE)

http://www.excelforum.com/excel-prog...rder-list.html

How can we help you then?

What lost you?

Where is your sample workbook showing what you have and what you want?

The VB Editor is the Visual Basic Editor... where code is entered to run macros...

but I was indirectly referring to my solution in that post which is all formula-based on has no VBA macro involvement...

That formula is used in a helper column to gather information.. so that the formula in the summary page knows which items to extract... so that if any of those formulas result in anything other than 0, then it is to be extracted and in that order.

If you post a workbook example, I may possibly be able to get you started.

The paperclip icon in the reply box...

see attached.

I inserted a column in each existing sheet where I put the formula to count the number of entries with "Yes" in column B.

Then I inserted a Summary sheet to extrapolate the data, based on the number sequence in column A.

So as long as you put a "Yes" in column B of the sheets, it should appear on the summary sheet.

I probably should have re-emphasized it here.. but you do need to install a free addin from here for it to work: Morefunc.xll

The THREED() function is part of that addin.

The basic workings of this function is the native Vlookup() function..

The formula looks for the number in column A of the summary sheet in any/all of the other sheets... if it does not find it, it means we have extracted all available "Yes's" and so returns a blank.. otherwise it pulls the data corresponding to the number in column A from whatever sheet it found it in.

The $ sign makes the cell reference absolute (i.e. "freezes" it) so that when you copy the formula down or over, the cell reference does not change... if the $ sign is not there, then the reference is relative and will change relative to where you copied it to.

You just need to download and install it.. it should automatically become part of your excel forever.

After you install the addin, go to Tools|Morefunc|Embed Morefunc in the workbook.. this will allow you to share the workbook without having to get the users to install the tool themselves.

Is the addin installed? Go to Tools|Addins.. it should be in the list and checked.

Did you change, add or remove sheet names? You have to adjust formulas and the names must match exactly...

Also if you changed the Vlookup ranges, column indexes, you have to make sure that the references are valid...

So is the addin installed or not?

If yes, re-attach sheet here.. I can see if you did something wrong.. perhaps.

Yes, it's installed.

I also get a weird error when I embed the file to the workbook (if I embed the help file, too).

EDIT: Sorry, ONLY if I embed the help file.

See if attached works...

I don't know what happened.. put I just went to B1 and reactivated the cell, pressed Enter and it worked... I then copied down and across...

I also re-embedded the Morefunc for you... I didn't get any funny errors,etc...

hopefully it works.

Upon opening the file, I get:

"Macros are disabled because the security level is set to High and a digitally signed Trusted Certificate is not attached to the macros."

What level of macro security should I be using?

EDIT: Changed to low ...

How did you reactivate it? I went in, typed 'yes', checked summary and "#REF!"'s took over. I haven't changed ANYTHING.

Embedding the Morefunc.xll addin creates a macro so that is why you get the message...

I would suggest you always set security to Medium and only approve if you trust the file you are opening...

No, the copy I saved on my computer comes up okay after closing it and re-opening it....

Go to cell B2 and hit F2, then hit Enter.. then copy down and across. Does that work?

Re-save it and then re-open it.

Unless you are changing anything else in the file that I sent back to you, I am not sure what else would be causing that problem...

See the attached alternative.. it doesn't use the Morefunc Addin.. but it is more complicated in that it uses a named range consisting of the names of all you data sheets.. and then it uses an array formula to extract the data... The array formula is confirmed with CTRL+SHIFT+ENTER and then copied down and across.

To better understand the formula, go to a cell with the formula in it and then go to Tools|Formula Auditing|Evaluate Formula.. then click Evaluate over and over to see the steps taken to resolve the formula... This is why I went with the other solution first.. because it is easier to understand and implement if the addin would work..

For the drop down, you need data validation..

Pick the range of cells to apply it to and go to Data|Validation... choose List from the Allow menu and enter "yes,no" (without the quotes) in the Source field.

Click Ok... now when you click on one of those cells, you should get the drop down choice.

Originally Posted by hawkteflon

Lastly, I noted that if I insert columns, it messes things up. Lets say I want to insert a new column (new D column) ... what will I need to be altering to make sure Summary isn't messed up.

Inserted in which sheet(s)?

In B2 of the Summary sheet, change the formula to this one:

=IF(ISNA(VLOOKUP($A2,INDIRECT("'"&INDEX(Areas,MATCH(TRUE,COUNTIF(INDIRECT("'"&Areas&"'!A2:A50"),$A2)>0,0))&"'!A2:Z50"),COLUMNS(B$1),0)),"",VLOOKUP($A2,INDIRECT("'"&INDEX(Areas,MATCH(TRUE,COUNTIF(INDIRECT("'"&Areas&"'!A2:A50"),$A2)>0,0))&"'!A2:Z50"),COLUMN(B$1),0))

hold the CTRL and SHIFT keys down and press ENTER.. You should see the { } brackets reappear... now copy over and down as far as you want...

This will accomodate for up to 50 rows in each sheet and 26 columns in each sheet.

When you insert new columns in the other sheets, you may need to many adjust the headings in the Summary sheet.

The formula I added in my last post is different from the one I sent in the previous file and therefore, you need to paste the new formula in B2 and reconfirm it with the CSE keys to get it to have the { } brackets...

Yes, you need to change G50 to G125 only in the formula in cell B2, then you need to once again confirm it with the special CSE key combination. Then you copy it across the columns and down the rows.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save