# Worksheets

## Recommended Posts

I'm clearly out of my league here!

I've looked around and done a little experimentation yet I am no where closer to finding a solution. I'm working on a project where I want to inventory symbols. In this worksheet I have created criteria that counts all the symbols and sums up the symbols with the same name giving me a count that is accurate. Here is the tricky part. I want to take the inventory of different symbols by name and add them together as well as multiply one particular symbol by two to give me a final total number of units.

What am I missing or is this a functionality that doesn't exist?

The easiest way to do this is probably to add an extra column with the formula for the multiplication. You could then hide the original qty column if you don't want to see it twice.

Set the function in the Column C database header to something like:

=If((A2='2 Over 2 A'),B2*2,B2)

In English, what this is saying is If the data in the database sub row column A is equal to the value '2 Over 2 A) then multiple the quantity in column B by 2 otherwise just use the value in column B. The stuff before the first comma is the criteria.

If you have multiple objects that require the multiplication you can use a logical OR function (which is indicated by a vertical bar character) to add additional values.

((A2='2 Over 2 A') | (A2='2 Over 2 B'))

If you had some items that need to be multiplied by different values, you would need to use nested IF statements.

I believe that a formula must be less than 255 characters, so the above options are probably not very good for lots of different values.

Let us know if you need more help.

Pat

I think they took away the formula character limit.  Or at least made it big enough that it's effectively unlimited.  Just tried it with about 900 characters and it works.

mk

That is good, but trying to manage a formula with 10 Ors and 10 nested IFs is still going to be really nasty.

Probably better for something like that would be a simple RunScript with a CASE to do the lookup of the multiplier.

Since we don't know all of the actual requirements, I was just trying to provide some options.

Agreed!  It would be unreadable once it got that long.

I wonder if you could simplify it with a worksheet script in the header cell?  Certainly all the IF THEN ELSE statements would be easier to read.

mk

You can definitely run a worksheet script from a Database Header. The script it executed once for each subrow of the database. Actually there is a bug that should be fixed in 2017 SP3 where the script actually executes one extra time.

The =WSScript_GetObject procedure returns a handle to the object pointed to by the subrow.

I have not played with it enough yet to know what happens when you SUM rows. I would assume it would run for each object and return the sum, but who knows.

Thanks for getting back to me. I really need to get my understanding of worksheets up to speed. I've tried what you suggested and still can't get the result I want. I'm going to use an alternate method for the time being as I have a deadline that I'm trying to meet.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×

• KBASE
• #### MARIONETTE

×
• Create New...