One formula is already present in the cell to show a value or date. I also want to use a formula to populate the cell with a comment from the contents of another cell.
I know the individual formulas, but just need to know how I can use both of them to show the results in the same cell.
I have a spreadsheet, in columns A & B are some numbers. I'm subtracting B from A - =sum(b1-a1) in column C. Can a formula be written, if the sum is equal or greater than 8, it will display 1? If the sum is 7 or less, it will display the sum. Or, does this need to be in a different column?
View 2 Replies View RelatedI've been trying to solve with a 2 column drop down. What I am hoping to do is use two columns to aid the user in selecting the correct entry. So the first column will be a number and the second column would contain what the number means
If:
Col1 | Col2
001 | Horse
002 | Cow
003 | Dog
Both columns show when the list is selected, but when the user selects 003 | Dog, the cell would be populated with only the 003.
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
I know how to format a cell so that if I type 5 it will say: 5 SF And then in another cell if I type 15, it will say: 15 Units
Is there any way possible to format a cell so that if I type (its actually calculated via formulas) these two numbers in one cell that it will format them both?
EXAMPLE: One cell that has 5, 15 will read: 5 SF and 15 Units
I want VBA to open two separate sessions of IE on two separate displays.
I have not been able get the display part to work and not finding anything so i am not sure if i need separate sessions but would like to do so to be safe (doing it manually on the computers using i have to have separate sessions of IE to do full screen on the two monitors).
current code: (this opens two IE windows on full screen on the default monitor)
Code:
Sub Test()
Dim objIE As Object
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True
[Code] .........
[IMG]file:///C:/Users/Paul/AppData/Local/Temp/moz-screenshot.png[/IMG]I need a fairly simple formula (I think) I have this table in another worksheet. I would like to be able to input select from a list "integrity" in A1 and from another list select "20" in A2 and then in A3 this mythical formula (if A1 = Integrity and A2 = 20, then 120%) I suppose i could write a ridiculously large if,then statement but I was thinking more of using a duel vlookup function.
View 9 Replies View RelatedTAKING A BIT FURTHER THE POST Find Min Value In Multiple Columns And Match The Header
Here's my problem: I am trying to find the MIN value in a column with dual headings and match the corresponding product which makes my formula with three criteria.
see the attachment to see the structure of the tables and further explanation.
How can I turn this into a dual axis chart whereby it shows the number of current stores open (like it does right now) as a bar chart. But also as a line chart shows the store potential (%) - so for example, PSUN has 960 current stores and has a 96% store penetration rate.
View 9 Replies View RelatedI'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
View 2 Replies View RelatedI can accomplish what I want with the "Spin Button", but I'd really like to use the command button instead...
Can the command button be used for 2 functions?
Right now, I have a Spin Button, that performs this function:
Private Sub SpinButton1_spinup()
Range("E11").Formula = "=c11"
[Code]...
Is there a way to have the Command Button perform the same function? Not very familiar with VBA...I'd even prefer to go with the "Check Box"...but frankly, I simply don't know enough about the command functions to make it work.
I have a 20000-row sheet generated by a daily report and I only want to work with about a quarter of this data; any rows that contain "PH4" in the M column. What's the best way to automatically single out this data to work with?
My initial solution was to create a seperate sheet to pull off all the columns with PH4 in the M cell, eg cell A8 would be:
=IF([WEB_StoSta_Rep.xls]Sheet1!$M8="PH4",[WEB_StoSta_Rep.xls]Sheet1!A8,"")
Then I could run lookups off that sheet. However, this feels like a sloppy solution to me, and Excel throws up an error when saving, unable to save external link values with available resources. Is there a better way of going about this?
I am trying to create a macro that checks and displays the screen resolution for both the Main monitor and the secondary monitor.
Currently I have this code that displays the resolution on the main screen only:
Declare PtrSafe Function GetSystemMetrics32 Lib "User32" _
Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
Sub ScreenRes()
Dim lResWidth As Long
Dim lResHeight As Long
Dim sRes As String
lResWidth = GetSystemMetrics32(0)
lResHeight = GetSystemMetrics32(1)
sRes = lResWidth & "x" & lResHeight
MsgBox sRes
End Sub
However I want to then display another message with the secondary display's resolution (or on the same message box).
I browsed the first few pages of searching for "row delete macro" and couldn't find anything that suited my needs very much, so here goes:
What I need to do is automatically delete rows where the entries in column B (date) and column E (string) are the same. It is a very large group of data, so filtering is not exacly an option here.
Here is the twist on it...
I have a column F that contains either "Accepted-Active" or "Accepted-Closed". If I delete a bunch of rows that contain the same column B and E entries, I want to save the row that contains "Accepted-Closed", otherwise I don't particularly care which of the rows is saved.
I'm trying to create a cell with multiple dropdown lists in E4 depending on a value from another drop down list in B4. Both E4 dropdown lists are named cell ranges RV_MECHANISM and VALVE_OPERATING_MECHANISM_TYPE. I know how to do this in Data Validation using an IF formula, however, that's not quite "bullet proof" or "idiot proof" enough.
I'm attempting to create a macro to load only the appropriate dropdown list in E4 as called for by B4.
I recorded the Data Validation dropdown lists using the macro recorder, then searched through all appropriate posts I could find at MrExcel and attempted to write the appropriate macro code as follows;
Sub RVorMECHTYPE()
With Selection.Validation
.Delete
.Add Type:=xlValidateList
If Range("$B$4").Value = "RV" Then
ValList.RV_MECHANISM
Else
ValList.VALVE_OPERATING_MECHANISM_TYPE
End With
Range("E4").Select
End If
End Sub
Compile error: End With without With
That's as close as I can get ... can one of you kind souls direct my macro coding paths and offer up the correct code for accomplishing this task.
What is the best way to filter a list of data with dual headers, using both those headers as the filter criteria? I’d like to extract 1 column of data to copy to another location using something like Advanced Filter to select 2 criteria ( Upper header and Lower header), and copy the one column with the matching header values.
The worksheet has the following peculiarities:
- 2-row headers
- Top row headers (main categories) are merged, spanning several columns (I can un- merge if necessary)
- Bottom row headers are sub-categories and have repeating names
Although the top headers are merged and the bottom headers have repeating names, each of the 2 headers combined creates unique labels for each column.
I believe that someone showed me once that you could view the same workbook on two monitors with the vien arrange all mode, you could make changes on both screens and the changes would be saved to the same file similar to arranging two views. I cannot rememebr how to do this or even if it is possible.
View 5 Replies View RelatedI know what i need to do to get it to open the file I want. I dont know how I set the position of a file that i am opening. In this case a .pdf file.
I have searched and came up with a lot of info for userforms, but I am not working with a userform in this case.
I have a splash page I created and I work on dual screen monitors. Typically I have Excel open on the left monitor and my VBA window (alt-F11) open on the right. If I close my Excel workbook and reopen it, the splash page will always open on the screen that had the VBA window on last. In my case Excel opens on the left monitor and the splash page shows up on the right window. I would like to have the splash page open on the side that Excel opens on (in my case the left side). Wondering if there is some VBA code I can add in my splash page code that would allow me to override this default setting?
View 2 Replies View RelatedI'm looking for a macro that can sort emails by 2 very distinct ways, with 1 special find function as well. Not too much eh? ........
View 9 Replies View RelatedI've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
View 9 Replies View RelatedI'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies View RelatedI have a macro running this code to strip out unwanted formulas and formatting.
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2
'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If
Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.
So i was asked to do a spreadsheet for "counting" electrical items from a project. For instance, quantity of cables, cable trays, electrical equipment, etc.
One of the sheets is called "Cables" and is structured in a database form, i mean, the first row contains the name of the columns (fields) and from the second row will be the records (much like a table in Excel).
One of this columns has to be calculated, for example, Qty (B3) * Measured (C3) = Subtotal (D3). If i put the formula in the whole column filling D:D the size of the excel file grows up to 10 mbs. So to keep the size small, I want Excel to introduce this formula only if it can find a value in Qty (B3).
Is liking adding records in Access, the table should grow with each row completed. We cannot use access, it has to be something the team can handle (they know a bit of Word and Excel, nothing else).
I am trying to get two formulas to work in one cell?
=E2/(1-40%)
and
=If(CEILING(E2,0.05) = CEILING(E2, 0.1), CEILING(E2, 0.05)-0.01, CEILING(E2, 0.05))
I am trying to clean up a databse and I was wondering if anyone knew of an answer to this:- In Cell C,17 I have this function, ROUNDDOWN(A17/$C$8,0), and in the cell next to it I have ROUNDDOWN(C17/3,0)*3. As you can see, I need the result of C17 to make the other formula work, but I want to have the function as one formula. I am having trouble in writing the formula for the function to work. It should go something like ROUNDDOWN(A17/C8,0)/3(*3) but so far I have not had any lick in achieving a result.
View 4 Replies View RelatedI need for one cell to be able to perform 2 functions. Specifically, I need B2 to equal B2 minus C2 but also update and equal B2 plus E2. I need for there to just be one value in B2, but reflect the difference. I'm building a spreadsheet to show stock for my department and B2 is my on hand stock. C2 is the number used and E2 is the number I deposited back, or received from my reordering. Is there any way to make that one cell reflect those 2 functions?
View 1 Replies View RelatedIs it possible to hide formulas from the formula bar while still having the formulas active?
View 8 Replies View RelatedTrying to merge two formulas in one cell. I have two cells with different formulas in them. I try to merge both formulas in a third cell to show different results. Depends on the case. All solutions found in searches made ​​by me does not fit my case. I can not use pivot table and not concatenated. Just a simple merge both formulas in a third cell if possible.
Case1 :
VB:
=If(ISERROR(MATCH(A3;FOLHA2!C2:C5;0));"";INDEX(FOLHA2!E2:E5;MATCH(A3;FOLHA2!C2:C5;0)))
join to
VB:
=SE(�.ERRO(CORRESP(A3;Folha2!C2:C5;0));"";�NDICE(Folha2!I2:I5;CORRESP(A3;Folha2!C2:C5;0)))
Case2 :
VB:
=If(ISERROR(MATCH(A3;FOLHA2!C2:C5;0));"";INDEX(FOLHA2!G2:G5;MATCH(A3;FOLHA2!C2:C5;0)))
join to
VB:
=If(ISERROR(MATCH(A3;FOLHA2!C2:C5;0));"";INDEX(FOLHA2!K2:K5;MATCH(A3;FOLHA2!C2:C5;0)))