I have a system that generates 4 databases with different types of statistical data. Observed defects, Calculated Defect, Capability and Number of points. I have set up a system for telling me what ranges each section is and I am manually changing the ranges to get my expected results.
Is there a formula that I can use that could adjust my ranges, within my formulas?
I am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx
Trying to look for formula for searching through Range and search through Data to achieve expected res
Data Output ExpectedRange
Pune 123 is city Pune 123 Mumbai 999
Maharashtra 345 is state Maharashtra 345Pune 123
India 678 is Country India 678 Nagpur 666
Maharashtra 666 is state OthersPune 555
Below is the query i used for manual search however need formula to see through Range and achieve output expected
=IF(ISNUMBER(SEARCH("Pune 123",A5)),C5,
IF(ISNUMBER(SEARCH("Maharashtra 345",A6)),C6,
IF(ISNUMBER(SEARCH("India 678",A7)),C7,"Others")))
My SUMPRODUCT formula is returning 0 instead of expected results
=IF(ISERROR(SUMPRODUCT((WOR_Data!$A$2:$A$429=$A7)*(WOR_Data!$E$2:$E$429=$L7)*(WOR_Data!$K$2:$K$42))),0,SUMPRODUCT((WOR_Data!$A$2:$A$429=$A7)*(WOR_Data!$E$2:$E$429=$L7)*(WOR_Data!$K$2:$K$42)))
I have verified that all criteria match criterion for spelling, case, length(trailing spaces). All fine. First comparison is unit, second comparison is account description, 3rd argument of course column to be summed.
If I take out the error handler, I receive #VALUE! error
I'm using a template that has formulas using a $ sign to attempt to stop the ranges they refer to changing. The problem is, when I use the template (which involes Access importing some data and adding columns to the sheet the formula refers to in the process) the rows referred to in the formula change in line with the number of rows of data that have been imported. Only the row numbers change not the column headings. So for example:
=( SUMPRODUCT(((Workings!$H$3:$H$1000=Explanation!B9)+(Workings!$E$3:$E$1000="Buy")*(Workings!$J$3:$J$1000))))-998
becomes
=(SUMPRODUCT(((Workings!$H$511:$H$1508=Explanation!B10)+(Workings!$E$511:$E$1508="Buy")*(Workings!$J$511:$J$1508))))-998
My sheet called 'Report' finds a type using a vlookup. I then have a sheet called labor, where I want to find put the total amount for labor using this formula: '=SUMIF(Report!$K$2:$K$65000;LABOUR;Report!$G$2:$G$65000)', but it only returns a '-'. I want the formula to return total amount from column G, if column K is type 'LABOUR'. Is my formula incorrect?
View 2 Replies View RelatedI have a range that has formulas that are based on other fcells outside the range.
What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.
There could be a marco to run when this process is needed.
example attached
I have this VBA (from a recorded macro) how can I amend it so that these two formulas change depending on the number of values....right now the range is to 129 only because we only have 129 cells of info but some sheets will have more some will have less. I want excel to base it based on the column I which used to be H.
VB:
Sub SLCREPORTDATA()
Cells.Select
Range("F1").Activate
Cells.EntireColumn.AutoFit
Columns("H:H").Select
[Code]....
I know there is a way to make a range adjust as you add or remove row. I just can seam to remember how to do it .I am using a countif function and later I will be adding more rows I want the countif to look at everything
View 2 Replies View Relatedi want a code to adjust the column A:Y from width
View 9 Replies View RelatedI've managed to piece together a VBA code that works perfectly for my needs. The problem is that the sheet it is on gets adjusted a lot, and I only need the code to run when certain cells are changed (rather than each time any cell is changed).
Here is the code I have now:
[Code] .....
Put simply, I need this code to run only when a cell in Range "TValueDeal" is changed. Is making this adjustment as simple as adding a couple lines of code, or is there a better way to write the entire thing?
the dynamic name ranges I had created did not "adjust" in their position references when I added new name ranges later.
My workbook (see attachment) Master sheet has several name ranges that are based on their position to a key name range I set up..."Unit". Example below:
"Unit"
The below formula is in Col F6, how do i get this formula to adjust to data range without manually filling down, my data changes everyday therefore i need the formula to auto fill according to data range.
=IF(ISBLANK(E6),"",Summary!$I$2-E6)
I am trying to get some help by adjusting my datarange dynamically based on what days I select. I would hope the chart could adjust to display for example 10/3/2008 to 10/10/2008 values and only those values.
I have been trying to figure out how the datarange varies in this post Dynamic Chart dataranges, I do not understand how to apply them to the charts source data. Ultimately Krish's chart would be ideal to understand and I belive is the keystone to where I am trying to go.
I have attached a sample of my workbook and the source data and chart I am trying to manipulate.
I have various charts others view and require the data range to change in 12 month intervals, ie Jan07 -Jan08, Feb07-Feb08. I currently move the ranges manually every month but find the it would be more interactive for others if a drop down combo box on the chart were used enabling the user to choose which intervals to view.
View 5 Replies View RelatedI am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies View RelatedIn need of a formula to trim data:
Examples:
* 5 Extravagant Mak (nz) (100) Need the number plus a dot then space then the name, then minus everything after the name, so this would now read 5. Extravagant Mak thats 5dot space Extravagant Mak
*
11 Frisbee (100) This one would become 11. Frisbee thats 11dot space Frisbee
I am using this formula in Cell J12: =AVERAGE($G$12:$G$85)
It will always be $G$12 but $G$85 needs to be done so that it goes until the last empty row on the worksheet.
How or if it is even possible to have a table automatically adjust its range or the number or rows it has based on another table
I have two tables:
Table1 is raw data that is added two daily
Table2 has formulas with column header references to Table1 that processes the information and converts it to a readable format. (basically barcodes to words)
Is there a way to get Table2 to automatically resize to the same number of rows that Table1 has whenever data is added??
I've posted a document in which I'm trying to calculate the weekly headcount for employees using their timecard entries.There are many entries per week for each employee, but I want to count them only once per week rather than each time they make a timecard entry. I need to tweak it to deal with one condition that it currently doesn't handle.The person who provided has said he doesn't check in with the forum very often, so I'm putting this out there to a wider audience.
Whenever the value of column W = "UTO" (unpaid time off) I want the formula to ignore that entry (count it as 0 rather than 1) and continue on to the next entry. The way I have the formula now, it counts any "UTO" entries as 0, but then it does not count the subsequent non-UTO entry as 1. I'm not expert enough to fully understand the formula that was provided
--(COUNTIFS(B$2:B945,B945,U$2:U945,U945)=1).
I have added embedded the formula in an IF statement to ignore the "UTO" entries.
=IF([@[Util?]]="UTO",0,--(COUNTIFS(B$2:B945,B945,U$2:U945,U945)=1)) ("Util?" is the header for column W)
I have a formula that displays the 1st monday of a month, I need the formula to take into account which day is selected and then display the first of the selected days date for the month.
=DATEVALUE("1"&G7&G5)+IF(WEEKDAY(DATEVALUE("1"&G7&G5),2)=1,0,8-WEEKDAY(DATEVALUE("1"&G7&G5),2))
Is there any way to adjust it to do this the drop down box for the day will be in F9.
I was using the formula below which was working fine for copying across 20 columns and down however many rows, but now the requirements have changed to 90 columns. I have tested this out with the 90 columns but the Indirect function is bogging down the spreadsheet with the constant recalculating.
=IFERROR(INDIRECT("'"&COLUMNS($A$1:A$1)&"'!P"&ROWS($A$1:$A8)),"")
I rearranged the formula to the one below, but how can I have the reference to the sheet name changed as in the formula above without using the Indirect?
=IFERROR(INDEX('1'!$A$8:$AT$115,MATCH($A3,'1'!$A$8:$A$115,0),MATCH("PAT",'1'!$A$7:$AT$7,0)),"")
The sheet reference will change from '1' through '90'.
I have a spreadsheet at work . I am tracking the quality target for the department and I need to create a formula that adjust accordingly to the target set for month end which is 90%.
I have build in the foreacasted numbers for the whole month but I need the formula to indicate the minimum error points needed to achieve the target of 90% for month end when I replace the forecasted numbers on a daily basis with actual numbers.
I have attached the spreadsheet which will be clearer.
Attached is a spreadsheet I am working on that has a series of graphs with defined dynamic ranges. I am having 2 separate issues on 2 different tabs.
1) Ranges named AirRecBra4 (and Bra10); AirComBra4 (and Bra10); AirRemBra4 (and Bra10) are not pulling in data from the correct data points. Instead of using the last 26 data point, they are currently beginning at the top of the column (currently cells 11 to 36 is their respective columns). The formulas are copy and pasted from working offset ranges in the same tab but are reading differently.
2) On the last 6 tabs beginning with Stn Backlog, I want to have defined formulas beginning in cell B119 and continuing downward to at least F200. However, when I add the formulas, the offset reads the formulas as being data and adjusts for that. I do not know how to adjust the dynamic range to ignore formulas in blank cells.
Global Demand-Capacity Management_working (version 3).xlsm
I want to concatenate two Cells into a single cell BUT have the first field left justified and the second cell right adjusted.
A1 = "John Williams", A2= "Single"
A3 = "John Williams Single"
Range("B25").Name = "EndMull"
Its fine but if i insert a new row or column then it mucks the whole thing up. Is there away of naming them but if any cells, row or columns are inserted the range will automatically adjust to suit1
I'm trying to make a by month spreadsheet that has all twelve month ranges starting in for a3. in a3 it would have the start date and in a4 it would have the end date. I'm trying to locate all of the dates between those two dates and pull in the profit ammounts from another sheet, the results would be in row 5. I would also like to pull in the loss amounts and have them in row 6. All corresponding with the date range in rows 3 and 4.
View 9 Replies View RelatedI have the code below that is two separate activities and I want to change the second activity from a cell trigger (Set KeyCells = Range("K42:AD42")) to a button trigger. I need to first to remain unchanged.
I'd be ok if this was just one macro that I could assign to a button but because its two and I need to write the second's to clicking a button I'm over my head.
Its occured to me while writing this that because it'll be a range of buttons I'll probably need to make each one an individual code? Is this the case? If so I may have to just keep this as it is.
formula for -- If Text = 1 and Text =-1 adjust running total
example:
1312 (total)
SS1311 (new total)
LA1310 (new total)
LB1309 (new total)
LS1310 (new total)
LS =+1
SS, LA, LB =-1
To get cell to display results of formula I must edit (F2) and hit enter. Is there an easy way to do this in bulk rather than editing and hitting enter on hundreds of cells?
Example of formula:
=IF(COUNTBLANK(K2)=1,"",CONCATENATE("*** ",K1,": ",K2," "))