Making An Exception Formula

May 17, 2006

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.

ADVERTISEMENT

Making Exception For Locked Cells In VBA Codes

Jul 6, 2014

I have a big Excel file that is supposed to be used by many users and therefore I need some cells locked. But problem is I use these cells in my vba codes which the users must be able to run by using the buttoms in the file. But now I get error in every macro since the cells the code are trying the reach is locked. Can I make an exception for vba codes in some way so that I can run my macros?

View 4 Replies View Related

Add Exception To Sumif Formula

Oct 19, 2006

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.

View 5 Replies View Related

Find With Exception

Oct 2, 2009

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:

View 2 Replies View Related

Formula On Making A Statement

Jan 20, 2014

I need a formula to look at a cell and if it is =< 102 say "Flat Bed", or =120 "Double Drop"

View 1 Replies View Related

Making A Formula Static

Jul 13, 2008

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.

View 9 Replies View Related

Summing Column With Exception

Apr 11, 2009

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

View 6 Replies View Related

Exception - Deleting Only Certain Cells

Feb 27, 2007

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.

View 2 Replies View Related

Making HLookup Formula Dynamic?

Jun 20, 2014

I'm currently using an HLookup formula to create an output tab for a huge set of input data, but whenever I add new rows to the input tab, I have to adjust the formulas for each individual output cell.

For example, my output formula is currently "=HLOOKUP(B$4,'Inputs'!$B$2:$BJ$384,116,FALSE)," but if I add a new row to the input data, I have to adjust the table array and the row number for the formula. So, if I added a row before the 116th row, but wanted the output to be the same for this cell, I would have to update the formula to "=HLOOKUP(B$4,'Inputs'!$B$2:$BJ$385,117,FALSE).

Is there any way to adjust this formula so that it will automatically update to produce the same output without manually updating the formula each time I edit the number of input rows? Would an index/match formula work, and if so, what would it look like?

View 1 Replies View Related

Making A Cell With A Formula Appear Empty

Mar 16, 2009

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?

View 5 Replies View Related

Making Formula Calculate At The Top Of Hour

Apr 19, 2014

I have a spreadsheet that I use to track my stocks. I have installed a third party add-in that goes out to the internet and grabs various stock information. But, when you are pulling info on about 30 stocks and getting info like name, price, dividend, div pay date and such, it slows the spread sheet down tremendously. Is there a way to keep the value in the cell and change it only during a certain time period such as 5 minutes before the top of the hour to five minutes after the top of the hour.

View 2 Replies View Related

Add Exception To Macro Displaying Pictures?

Feb 15, 2014

If Cell value in "C36" or "D36" is Yes, then a picture will be displayed (either Picture 1 or Picture 2). If Cell value is No, then the pictures won't be displayed.

The code I use:

[Code] .....

By default, all pictures on the Excel sheet are invisible.

My problem is, there are 3 pictures I'd like to display at all time because they are part of the document's decoration.

Therefore, I'd like to add an exception for the Picture A, Picture B, Picture C so they are not affected by the command "Me.Pictures.Visible = False".

View 3 Replies View Related

Exception To Forcing Text To Upper

Oct 20, 2008

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

View 9 Replies View Related

Making Dropdown Menu That Will Change Formula?

Dec 10, 2013

At the moment if a pupil has done foundation listening then I write their numerical result in, for example C3 or if they have done higher listening I write it in F3 (and it works out the UMS and then the grade), but I wondered if there would be any way to just put a dropdown menu in a column for each name, or change it so that I can type in H or F that will change which data is used? Because the UMS and grades are different depending on whether the pupil has done higher or foundation I am unsure how to format it

View 8 Replies View Related

Making A Formula To Automatically Fill A Label

Jun 8, 2009

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.

View 7 Replies View Related

Formula- Making A 'sports Day' Spreadsheet For A Project

Aug 28, 2007

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.

View 12 Replies View Related

Making A Grade Book--- Missing Assignments Formula

Nov 14, 2008

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...

View 10 Replies View Related

Excel 2013 :: Automation Error Exception Occurred?

Mar 25, 2014

why a macro that works without a problem in Excel 2010 now crashes Excel 2013. The error produced says 'Automation Error Exception Occurred'. If I place a stop in the code and hold the F8 key down, the code runs perfectly (Excel 2013) but if I run it, it will crash. The code is copying a worksheet into a new workbook, adding 2 more worksheets, saving then emailing the workbook before closing. It goes through the routine about 7 times before crashing (sending only 7 email out). It's something to do with the email because I can bypass that code and it runs that part without a problem.

Our office just upgraded all the computers to Office 2013 and I've got to get this macro running again. I had to email 100 reports one-by-one today and I didn't like it.

View 6 Replies View Related

Index And Match To Check Data And Provide Exception Results

Mar 25, 2014

Currently i am using index and match in excel but however i am unable to get the result tt I want.. what i need is

Data to check with item master whether all 3 (article, color code and size) matches and if it doesn't match, which of the field is wrong. (eg: wrong color code, wrong size or article don't exist).

[Code] .......

Above is my item master

Data to check

[Code] ........

Example case: 1

Checking first row of data check

Article number: 2-50660147

Color code: 6

Size: M

Result: "Wrong size"

But however, it returns as wrong size as it only reads the first time the article number appear in the item master and does not recognize the second time the article number appears with the correct color and size.

View 9 Replies View Related

Excel 2010 :: Making Portions Of Drag Down Formula Skip Cells

Feb 11, 2014

I have set up a formula to sum data from specific columns in multiple sheets in the same file. The simple formula had worked perfectly until I needed the data from the last sheet. In all of the other sheets the data that I need to pull is all in consecutive rows but in the last sheet the data that I need pulled and added is in every 4th row. The sheet looks like this:

All of the columns I've filled in work fine. The ones blank are the ones I am running into issues with. When I drag down the formulas excel pulls the next consecutive rows, which is perfect for the first four sheets I am summing, but for the fifth sheet (I'll ref the Test1 column) I need it to pull 'Wayne 2014'!C6 and then 'Wayne 2014'!C10 in the next row and so forth.

Date
All Test
Test1
Test2
Test3
Test4
$ Amt.

Jan.16
=SUM(C2+D2+E2+F2)

[Code] ........

The first four sheets look like this, which is why they work fine:

Date
All Test
Test1
Test2
Test3
$ Amt.

Jan. 16
=SUM(C2:E2)

[Code] ........

The fifth sheet looks like this: I only want the formulas from the first sheet to pull the data from the dated rows in this sheet.

Date
All Test
Test1
Test2
Test3
$ Amt.

Jan. 16
=SUM(C2:E2)
=SUM(C3:C5)

[Code] ......

Is there any way in Excel 2010 to get it do what I want? (I am not sure how that one cell became outlined and I do not know how to fix it.)

View 4 Replies View Related

Obscure Bug In UserForms ListBox Support: "Exception Occurred"

Aug 29, 2004

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......

View 2 Replies View Related

Visible Sheets To Be Hidden With The Exception Of The Sheet Called "open". Then Is Saves The File

Sep 9, 2008

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.

View 9 Replies View Related

Making A Map

Feb 3, 2010

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.

View 11 Replies View Related

Making Lists...

Feb 6, 2010

I have four columns containing about 200 addresses.

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.

View 6 Replies View Related

Making Add-in Macros Available

Aug 2, 2007

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?

View 11 Replies View Related

Making A Calculator From Vb.net To Vba

Feb 11, 2008

i made a claculator in vb.net, and now i have to make one in vba, what i did was as follows, first ill post the code from last year, then my vba attempt;

View 14 Replies View Related

Making A Button

Mar 14, 2007

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.

View 9 Replies View Related

Making A Roster

Mar 30, 2007

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.

View 9 Replies View Related

Making A Barlist

Aug 20, 2008

............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

View 9 Replies View Related

Making A Report

Jun 6, 2006

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 ?

View 6 Replies View Related

Making Squares

Jun 15, 2006

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.

View 3 Replies View Related

Making Cell Mandatory

Aug 21, 2012

In Column B of my spreadsheet i have a drop down that only allows users to enter "Yes" or "No"

Is it possible to make Column C a required field if "No" is selected in column B.

So basically if cell b4 is "No" then i want to make it manditory that cell C4 is filled out with a reason why.

Same thing for cell b5 then i would like cell c5 filled out before they continue using the sheet. etc.

View 1 Replies View Related

Making Dropbox From Datatable?

May 6, 2014

I am a Coach who will be using this data table to call pitches via a catcher's wristband. The band is obviously completed below; however, I need to create a separate sheet where the numbers correlate to the pitches that are being called. How it works: everything in the table(s) with a dash is a pitch i.e. 2-1, 1-3, 1-U, etc. The numbers on the columns and row are what is relayed to the catcher to call the corresponding pitch. For example, I would signal is 113 (first number = row second number = column). 113 is 4-1. I need a way/ formula to where I can see which numbers are to be relayed in for each pitch. It would possible look like 2-1: 101, 411, 422, 501, etc.

View 5 Replies View Related

Making Spreadsheet Smaller?

Mar 28, 2014

I built what I can only describe as a fantastic spreadsheet. It wasn't until after that I realized it is 123 MB in size, but even for the complexities within it, it still feels like that is quite large.

I can't share the file due to proprietary reasons, but any general tips as to things I can do to make it smaller (and therefore faster to run).

I tried saving it as a binary spreadsheet and that dropped it to about 80 MB, but I don't really know what that does and if it will impact the sheet performance in any way.

View 2 Replies View Related

Function Making Worksheet

Apr 24, 2008

I'v got a school task about making a excel worksheet.

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

The task itself is :

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

View 10 Replies View Related

Making Sure Cell Has 'J' Prefix

Nov 23, 2008

i have a cell "h23" that must always start with a "j" or a "J"
the trouble is some of my users are only putting in the number
ie 2345 when it should be j2345 or J2345

View 3 Replies View Related

Making One Worksheet Talk To Another

Dec 8, 2008

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.

View 6 Replies View Related

Making A Summation Table

Jan 11, 2009

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

View 2 Replies View Related

Making List From Various Cells

Jan 14, 2009

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.

View 5 Replies View Related

Making #N/A Dissapear (or Appear Blank)

Apr 15, 2009

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.

View 7 Replies View Related

Making Cell A Hyperlink

Jun 2, 2009

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.

View 6 Replies View Related

ADVERTISEMENT