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.
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 RelatedI 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 RelatedI 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 RelatedI need a formula to look at a cell and if it is =< 102 say "Flat Bed", or =120 "Double Drop"
View 1 Replies View RelatedI 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.
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 dont know how to get this happening
View 6 Replies View RelatedI 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.
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?
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?
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 RelatedIf 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".
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
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 RelatedAs 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.
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.
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...
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.
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.
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.)
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......
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.
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.
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.
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?
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 RelatedI 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.
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.
............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
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 ?