Tracker
HOME    TRACKER    Excel

# Making An Exception Formula

## I am trying to get 2 different formulas to run depending on the value of the 2 cells. Example: if cell "A" is equal to cell "B" then one formula, if it is different than a second formula. I hope I did this right, if not please let me know and I will ask the question in a different manner.

Related Forum Messages:
I have a workbook setup to give me the nutritional break down of all of the foods I consume. I can get the subtotal of each day. What I need is to also get the subtotal of each meal. I can't seem to get this meal subtotal without adding it to the daily total twice (once in the listing and once in the subtotal). I was thinking of trying to add an exception to the sumif formula in columns I, K & M but I don't know if that is possible. I have included a cut down version of my workbook to illistrate the problem. I will gladly supply a full vertion to anyone who would like a copy for thier own use.

Making A Formula Static
I have 2 sheets in a work book. In the first sheet in A27 I have a value. In the secend sheet I have a formula "=SHEET1!\$A\$27".

Problem is when I insert a new row in sheet 1 the formula in sheet 2 changes to "=SHEET1\$A\$28" and I want the formula in sheet 2 to stay the same that is, always =SHEET1!\$A\$27.

Making A Cell With A Formula Appear Empty
I have a chart with blank values in some cases. I have set up the chart to show those values as gaps, which relies on the blank cell being empty. However, my blank cells have a formula along the lines of: if (condition,value,"")

So the chart does not consider them blank, presumably either because the formula is in there, or because "" is not the same as blank. How can I make this cell appear blank for the purposes of the chart?

Find With Exception
I have code that selects chart source data by searching the header row for dataTyp (in this case the dataTyp will be set to "IMP"). The target header cells look like "IMP 100Hz", "IMP 400Hz", "CAP 100Hz", "CAP 400Hz" etc. recently I inserted an averaging column at the end of each group of data columns. The headings for the new columns look like: "IMP AVG" "CAP AVG" etc. I need to exclude the ...AVG column from the source data selection. Code snip:

Making A Formula To Automatically Fill A Label
As I am a fresh user of excel 2007 I do not know how hard it is to solve the problem I am facing but hopefully there is a solution that the readers here are willing to share with me.

I have a list of **-s that I have recorded as a hobby with my friends (karaoke, free concerts...) and I have them coded in a specific way, so that I can find them on a shelve easily. The list is built up in a form that is seen on the example file that I added.
So far I have used my own handwriting and a sticker to label my ** boxes. It´s not a very good looking combo to be honest. Since I already have all the data in my excel tabel it would be good to use excel to take all the things from there and just print it out.
How to do that? The front cover is easy, I used VLOOKUP function. All I have to do is enter a number into the column C1 and the front label gets filled.

The problem for me is the back cover that should take the right track nr, title and length from several rows (VLOOKUP uses only one row at a time)? How to make it work, that excel would know that on the first album there are 3 tracks and when chaging a number in C1 there would be 2 tracks on a second one.

Making A Grade Book--- Missing Assignments Formula
I have a decent working knowledge of excel, but I'm stuck... I teach and I have created an excel spread sheet to help me keep track of grades, attendance, etc.
I'm trying to figure out how to get excel to figure out the assignments a student has missing (cells with no data input), then take the assignment name (column title with missing data) and students name (first column of data) and put that information in another worksheet. Basically I would like excel to tell me what students are missing work and what assignments they are missing so I don't have to scroll through hundreds of lines of data whenever I have midterms or a student wants a list of "missing assignments". BTW---I can get it to list the number of assignments a student has missing, but not list the specific assignment titles.

I know theoretically this is possible, but I'm having trouble figuring out how to do it. Any help would be appreciated as it will save a lot of time and help out my students! If you can give me a "fake" formula, I should be able to update it for my purposes...

Summing Column With Exception
I attached the extract. I am having the issue in the Total Workbook. Specifically, the Tax and Debit Column. If a company is on both workbooks (Insider Processor, Outsider Processor). The Tax and Debit should be charged once in the Total Workbook. But i don’t know how to get this happening

Exception - Deleting Only Certain Cells
I am trying to write a macro that looks at columns C-I in row 6, to check if the value is G, if it is then I want it to delete C5,6,7 & 8 and so on along to coumn I. So all that I am left with at the end of the macro are the values that weren't green without any blank gaps in the columns. I started using the following

If C6 = G Then
Range("C5:C8").Select
Selection.Delete Shift:=xlToLeft
End If

And using that same code for D6, E6 etc etc, but obviously, when it deletes the cells it shifts them left so the columns change values, so if C6 is equal to G then it deletes it but then what was D6 becomes C6.

Exception To Forcing Text To Upper
I am using the below code to force a range of cells to change to Upper Case, what i am after is an exception to the rule for example the words Pick Up i don't want to force to upper case, is there VBA i can add to the below to achieve this?

Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''
'Forces text to UPPER
''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

On Error Resume Next
If Not Intersect(Target, Range("w6:w299")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True
End If
On Error GoTo 0

End Sub

Formula- Making A 'sports Day' Spreadsheet For A Project
I am making a 'sports day' spreadsheet for a project and i have one problem, the spread sheet is meant to have the possiblitlity of mistakes reduced to a minimum.

I have 6 teams in the sports day and under each i put 1st, 2nd,3rd,4th,5th,6th.

[url]

basically i need the rows to only accept 1st 2nd 3rd..... only once. I need something like an error message to come up if a value is entered twice on a row.

Obscure Bug In UserForms ListBox Support: "Exception Occurred"
I'd like to report an obscure bug in Microsoft's MSForms. ListBox support that has had me tearing my hair out for several days. I started getting a totally useless error message everytime I popped up a very simple dialog to select a Vendor from a list (in a ListBox, of course). The (useless) error message was titled "Microsoft Forms" (okay I could have guessed that - not too helpful), and the message (after the typical error icon - exclamation mark in caution sign) was "Exception occurred." (how helpful is that?!?!)

The debugger was not invoked, and the dialog/UserForm was frozen except for the scrollbar on the ListBox, any control clicked on (there were only the ListBox, OK and Cancel buttons) popped up the same error. Even closing the UserForm popped up the error message once before finally closing the UserForm. No breakpoints were triggered in any event handler for any of the controls, including added error event handlers for each control (and the UserForm istelf).

This dialog (or UserForm) had been working perfectly earlier in my development of this project, so I found it very frustrating when I started getting this error. Since I had no debugging clues (other than what I realized when it was finally resolved, with my 20/20 hindsight), I tried many things to deteremine where the problem was. When I copied the form to a new workbook, the problem disappeared, until I started copying in other controls and/or modules or workbook/worksheet procedures. However, I couldn't see anything in these other procedures that was causing the problem and they were all working just fine for themselves......

Visible Sheets To Be Hidden With The Exception Of The Sheet Called "open". Then Is Saves The File
i am using the macro below to get excel to hide all sheets but the one called "open"...

PHP
Sub savemini()

ActiveWorkbook.Unprotect "letmein"

For Each sh In ThisWorkbook.Worksheets
If Not sh.Name = "open" Then sh.Visible = xlVeryHidden
Next sh

ActiveWorkbook.Save
End Sub

So the workbook is protected with the password "letmein", and the VB code above in theory should unlock the workbook, allowing all visible sheets to be hidden with the exception of the sheet called "open". Then is saves the file.

Now heres the odd bit... it works for certain people, but not for others. I have made sure those it wont work for are not doing anything weird and they are not.

The workbook i am using has a code that only opens certain sheets for certain users. I as a master user have access to all sheets. I can go into the users sheets and click the button that activates the macro above myself and it works fine, but for some users it wont work.

Making Lists...

A Names
B Streets
C Cities
D Phone no.

If I write a name in D1 I want to get the corresponding street, city and phone no. in E1, F1 and G1.

I also want to copy the formula downwards let's say ten times so I can make a list.

If I for example just want to make a list of three names (D1-D3) I want the rest of the fields to stay blank.

Making A Button
I have a spread sheet that tracks progress reports that are due every 90 days. I have a due date colum that will automatically get high lighted if the 90 days have passed to inform me or my staff that the report is late. I would like to have a colum with a button in each row that allows the reader of the spreadsheet to press "YES" for that accont's progress report having been turn in. When the "YES" button is pressed the date due date column advances to the next 90 day due date and the highlight would turn off.

I used the conditional format to change the due date column to Yellow if =TODAY() is less then or equal to the due date. But I can figure out a way to select something and turn the highlight off and advance the due date to the next 90 day due date.

Making A Map
I am making a simple map in excel. I have my floor plan on sheet 1 and the location of product on the floor on sheet 2.

Currently I have some formulas on sheet 1 that link to some sales data on sheet 2, but what I would really like to be able to do is use the search function on page 1 and it give me the location of the products in a given area.

The data is broken up into group location on the second sheet.

Making A Barlist
............Beer........ Total
Stefan:....1........... 0,70€

Now i need a button that when i click on the name the amount 1 gets 2,3,4,5,6, ect and that it calculates the price up as a total

Making A Roster
I have been asked to make a formula that sums up the hours in a week and then tells me whether I am below or over the budgeted hours for that week!

I cant get it to work since I cant figure out how to have the same formula in all the cell that sums up early, mid and late shifts, ie 06-14, 14-22, 22-06.

The graveyard shift is a problem there since I get negative hours.

Further more, I need to have the formula ignore cells to be added up if it has letters in it, we write an R for Rostered day off.

Making Squares
I would like to:

1-Make Rows & columns 1/4" X 1/4"

2-Be able to enter dimensions into an input box that would then draw a square(or rectagle) with the inputed dimensions in INCHES.

Making A Report
1- I 'm working with a table wit many Sheets.

I want to make a Report Sheet with selective ranges(cells)
the selective cells should paste as link ( in order to be updated cells)

2- then how to Zip this worksheet to zip file ( to save it)

3- If I have a Macro and i want to perform this macro to all sheet except one sheet how to that ?

I've created an Add-in (.xlam file) in Excel 2007 and installed and activated it (it shows up under "Active Application Add-ins" on the Add-in menu for new workbooks).
I can see the macros and code when I go to the VBA editor, but the macros don't show up in the regular macro list.

I know that I can write code for the new workbook that will reference the Add-in code, but I want other users to be able to install the add-in and run the macros in it right away without any knowledge of VBA. Is there a way to make this possible?

Making A Time Sheet
I made a time sheet. In this time sheet i made a macro where when I press ctrl+t it fills in the date in a column and the time in the column next to it. So to start my day I would press ctrl+t and to end it i would goa column over and repeat the macro. and then i made it so that in the fifth column it adds the 2 times together with this formula "=IF(E27>0,(IF(G27>E27, G27-E27, 1+G27-E27)),"")" formula works fine unless you make a "mistake" and change the ending time manually to a time that is early then the starting time but on the same day. So I wanted to see if there is anyway of the total time column can return an error message if the ending time is earlier then the starting time by looking at the date in both date columns. Im going to attach the workbook to this post. on the tabs on the bottom it is under time sheet. and i think the macros should transfer over too. ctrl+t is the time macro if you want to try it out.

I may have not been so clear on explaining it so ask any questions if that is true.

Making A Date Static
I have a rather simple question that I can't seem to find an answer to anywhere, even with a date stamp search on the board. I've found similar answers, but nothing specific to my problem.

I'm currently working on a department worksheet to track productivity. I have the formula in 2 cells to automatically display the current Sunday through Saturday dates when the worksheet is opened:=TODAY()-WEEKDAY(TODAY()-1) in E1 and=E1+6 in E2However, I need these dates to be static once the week is over for each worksheet and the workbook will contain a sheet for each week in the month (I'm not sure if that matters). I know this needs to be done in VBA, but I can't find how (if possible).

I'm new to this board and not a super Excel pro, so any help would be fantastic! Also, I'm working with the company's very old Excel97.

Making #N/A Dissapear (or Appear Blank)
How do you make a cell appear blank when it has a formula in it refering to a cell that hasnt been used yet?

I would like some cells to be blank instead of having #N/A in them.

I have a table which as a few columns, the left column is called name and the far right one is called email. The name cells have a drop down list which refers to another workbook with peoples names, when you select a name other cells are automatically populated using the vlookup function such as phone number, email etc. In the workbook it is referencing too with all the data, the email address are like a hyperlink which creates an outgoing mail if you click on it, is there a way to have this in the table as nothing happens when you ckick on the text.

Making Certain Cells Unchangeable
I am preparing an excel spreadsheet to be used as an order form. There are certain columns that I do not want customers to be able to change, for example unit price, but I would still like them to see it. Is it possible to have only certain fields that can be changed and others that are protected?

Function Making Worksheet

But we have just started and i havent gotten all the functions to work yet.

Make function that tests if D9 is larger then 100 and I9 is less then 50. If true "Kontroll" if not "OK" should be written.

Also, if "Kontroll" shows up it should have a dark green "OK " should just be black

Its roughly translated from norwegian. If you need some more information I will try to provide it

Making Negative Numbers To Become Zero
I have been working on an excel sheet:

I am working with stock options. Therefore I must subtract the current stock price Cell C2, which has a value of 23 with the Options price which is D9, has a value of 34.79 then muliply it with the shares which is cell F9 which is 4000. So the formula that i have is (C2-D9)*F9 that gives me the sum which resides in cell I9(total outstanding profit).......This turns out to be a negative number. I want to make it so that if the sum of that formula produces a negative number, i want that negative number to just become a "0". Ive tried the IF function but could not produce any results.

Making Cells Disappear
Is there a macro that can make cells with no data in them disappear or not be displayed?

Making Code Efficient
following code be write down in more efficient manner :>>>

Range("B3") = Range("L" & Target.Row)
Range("C3") = Range("O" & Target.Row)
Range("D3") = Range("Q" & Target.Row)
Range("E3") = Range("R" & Target.Row)

Making A Case Function
i am trying to get the following function to work

i want it to look at 2 cells, depending on the number in the second cell i want it to multiply the first sell by a specific number.

for exaple (Below) function in a3 looks at a2 sees it is a 2 and multiplies a1 by 1.09 shown in the code below

A2=factor

A1 A2 A3
100 2 Function

Select Case (Factor)
Case "1"
Case "2"
Case "3"
Case "4"

Making One Worksheet Talk To Another
I know I posted this same topic about a week ago but I went through the first 20 pages on the forum and couldt find it, i tried the search option and nothing came up. I have several worksheets in the same workbook. When I enter a number in one cell in work sheet A, I want the same number to show up in a given cell on work sheet B. How do I do this? I can make words move from one shet to the next but numbers dont seem to be the same.

Making A Checkbox Load A Value Of Yes Or No
I have a checkbox with a userform. I can get it so that if I enable it and add data then it correctly shows as "Yes" or "No" within excel. However if I open the data using offset the checkbox is greyed out with neither Yes or No. Is there a way to make the checkbox allow and show a value of Yes/No?

Making Third New Dictionary Out Of Two Exisitng Ones
I have two wordlists (dictionaries) in two sheets and want to make third dictionary out of them in a third sheet.

The first dictionary is Iranian-German and the second one is German-English. Now the macro is supposed to make a third dictionary Iranian-German-English. The new wordlist should contain all words from first list, even if some of them are not translated through macro.

attached excel file to understand the problem better.

Excel file

Making A Cell Blank
I have a spreadsheet with about 1000 rows. This is data that I've imported from another source and there are blank rows scattered throughout. I am trying to add columns together, but when I cut and past the sum formula, I end up with a 0 in a cell that should be blank. I've tried various iserror, isblank, isnull, empty functions, but am still getting a 0.

Here my example of one function I've tried:
a b c
1. 50 50 50 =if(isblank(a1:c1)," ",sum(a1:c1)) 150
2. 20 20 20 =if(isblank(a2:c2)," ",sum(a2:c2)) 60
3. =if(isblank(a3:c3)," ",sum(a3:c3)) 0

I would like that zero answer in row 3 to be blank otherwise I have to manually go through all the rows and delete these 0's out or cut and paste the sum function just on the rows with numbers.

Making A Summation Table
I've run into an issue for which I can find no elegant solution. I consider myself a competent Excel user, so it's pretty embarassing...

What I want to do is to sum the values along the 'axes' of the table. In order to fully explain that, I'll give a small example:
Let's say, this is my source data

Making A Synonym List
i have to make a synonym list out of existing wordlist for my professor. As far that’s the most boring job I would like to ask you people, if it could be automatically done by a vba code. I don’t think that that’s technically possible but I hope though that I am wrong and that some one here can help me. I will try my best to describe the problem as good as I can. Please let me know if its not precise.

Here we go!

I have a wordlist containing four columns A,B,C and D. In column A we have got Source Words(sw), in column B word specifications1(ws1), in column C word specifications 2(ws2) and in column D we have many meanings and target words separated by comma (tw1,tw2,tw3,…) for (sw). The interesting columns for our problem are only A containing words (sw) and column D containing meanings and synonyms(tw). The wordlist is supposed be get larger through finding, enhancing and adding synonyms in the same list. The difficult thing would be to make a macro with two modes. In first mode which asks each time before it adds or enhance a cell and in second mode the macro should do all the work alone without asking the user. The existing wordlist looks like: ...

Making List From Various Cells
I have a workbook of contracts that I have designed in excel. Each worksheet tab/title is the contract number, i.e. 3487. On each individual contract I have the contact name, address etc. Each cell/field is in the same position on every contract. In other words, every B1 is the Group name, every B2 is the first name, every C2 is the last name, etc. What I'm trying to do is to make a new page with all of the contact info on it so I can make labels etc. I hope this is clear.

I m looking for is a way of making it so when the user has finished entering in data and they save it, ready to send to anther user to view, that the whole sheet is readonly with no way of editing it ( like protecting the whole sheet).

Making IF= Formulation Come Back As N/A
I am trying to create an IF formula so when i try to get the percentage change if lets say the 2008 number was 0 and the 2009 number increased by 9 than i would get an error so i want the if formula to come back as n/a.

Making A Summary Sheet
I need to make a "aummary" or a pod sheet? what I need to do is make a page that will automatically tally up new information I put into new work sheets. right now I have 5 work sheets and the totals I need totaled are in different row #'s on each sheet. How can I do this so it will add the numbers automatically?

Making Columns Get Sequentual?
One of my colums has a list of customer Ids:

Example:

876541
876542
876543
876544
etc...

I just added a bunch of new customers, like 400 of them. Is there a way to make the column automatically add a customer ID, in sequencial order down the column for 434 more customers downward?

Making A Simple Chart
Im trying to create a bar chart that contains required hours per week with a line of hours worked going across the bars, im struggling to make this work and have basically no experience of making any sort of charts or graphs.

I need to create this using vba but to begin with I cant even make the chart manually! Where its all going wrong is the column line for required hours is so thin it looks rubbish. Its a bar chart then I formatted the series of total hours to be a line chart.

Making Data Permanent
I've been using the function =vlookup(...) to fill up the cells in my spreadsheet. However, a problem I've come across is that vlookup does not make the values in the cell permanent and keeps drawing whatever value is found in the corresponding cells it searches. Thus if the file it draws it's info from goes missing or is edited, my data also changes accordingly. Therefore I would like to ask if there was a way in which I can make the data permanent in my spreadsheet.

Making Code Available To Other Workbooks
I have written a sub that will compare the data in one colum to the data that i paste in another colum, when it finds a similarity it deletes the row. Everything works fine, but the file that needs to run the code will be changing every month. how do i export or package to use in other sheets. Also what command will i need to insert to get the new sheet to run the code.

Making A Special Chart
I want to make a chart in Excel similar to a Stacked Horizontal Bar Chart, except I want to be able to have spaces in between the bars of each row. I want to be able to have the separate bars represent spans of time, and I don't want them all to have to necessarily connect to one another. I tried the Floating Bar Chart under the Custom Types tab, but that doesn't let me have more than one bar per line (as far as I can tell).

I want to make some of the columns read-only (user cannot edit any of these cells).

Making Time Roll Over After 24 Hours
I'm creating a calculator to determine the distance & travel time on a xy grid for a game. I'll share what I have so far to display the big picture. Here’s what I’ve accomplished so far:

#1) Cells A1 & B1 are dynamic, they are frequently changed so distance and time may be displayed. Cells A2 & B2 do not change.

#2) I use the following formula to determine the distance between cells, displayed in C2: =SQRT((A2-\$A\$1)^2+(B2-\$B\$1)^2)

#3) Next I multiply C2 by a number so that distance is measured correctly per unit type for this game. (I hide this column). Therefore, cell D3 has the following formula: =C2*20

#4) Now my problem, cell E3 - Correctly displaying time. I have the following formula in E3: =TIME(0,D2,0)

This works great!! That is until the time goes beyond 24 hours, then it rolls over. I've tried right clicking and setting the custom format of E3 to [hh]:mm, it still rolls over. I’ve also tried setting formatting to d:[hh]:mm, if I do this then the output time displayed is incorrect.

Ideally I would love a simple fix so that the time will roll over and show days. (I would like a display similar to d:hh:mm).

I’ve done a fair bit of research online and either I'm missing something simple or this is more complex than I thought. And not to make this any more confusing, but I would love whatever I accomplish here to be useable in compatibility mode ...

Making Web Queries Recursive
Hello folks! Here's to hoping I can eventually be a source of information rather than questions. But enough about me - on to the topic at hand.

I have a single-source web query that I'd like to expand.

Here's what I have in the IQY file:

VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes" ...

Making A Chart From Data On A UserForm
I'm trying to make a chart (which will also be on the UserForm) from data that a user will enter onto a UserForm. The problem is that I am trying to select a data range as one of the charts 'series' but I can't quite find the right code. At the moment it looks like this:

ActiveChart.SeriesCollection(1).Values = Range(BMIM20Ttl, BMIM2025Ttl, BMIM2530Ttl, BMIM3040Ttl, BMIM40Ttl)
ActiveChart.SeriesCollection(1).Name = " Total"
ActiveChart.SeriesCollection(2).Values = Range(BMIM2075, BMIM202575, ...

The problem is with the Range function.

Always Making The Date The Previous Sunday
Is there a way to make the date in a cell check the current date then fall back to the previous sunday unless today actually happens to be sunday? So for example it would look at today and make the date 12/9/2007 rather than 12/14/2007