Multiple 'if-thens' (Chain Rule) With Two Sheets
Feb 4, 2010
I have attached a sample file with two sheets.
Sheet 1 has two columns, 'A' represents a parcel number, and 'B' represents an indicator for the plat name in which the parcel number is located.
Sheet 2 also has two columns. 'A' represents the same indicator as Sheet 1 Column 'B' and Column 'B' is the formal name for the plat.
What I hope to achieve is Sheet 1 Column 'A' to be the parcel number (as it currently is)and Column 'B' to be the formal name for the plat (instead of the number).
As you will notice, the plat numbers on Sheet 1 Column 'B' can repeat many times. A plat number of '0' would mean it is not associated with a plat and therefore should return a blank.
The 'real' file has over 275,000 parcel numbers. All total, there are approximately 8800 differnent plat names.
Due to my 'lacking' skill level, I need it to be a macro as I have yet to learn other methods.
View 12 Replies
ADVERTISEMENT
Jul 10, 2014
I want to change the colour of rows depending on the letter that is in a column. I want it to do it over all the sheets of the document. In the G column there will be letters like 'W' and 'L' that signify something and I want to make it clear by changing the row colour fill. I've tried a few formulas I found by googling but they didn't work. I should point out that the letters are connected, so if I put a 'W' on the second sheet it appears on the first sheet as well.
View 2 Replies
View Related
Sep 9, 2009
Is there a way color a cell red if the the text is "Z" or a number between 0.5 and 10
Or if the cell contains a "Z" or a "C" ....
View 13 Replies
View Related
Sep 18, 2009
how to adapt the solution so it would work in multiple cells. Since I was told to start a new thread, here it is. I want to format a cell based on its contents, If it has one of three entries, it should be greyed. I then want to use the conditional format across the whole page, but the solution given in the other thread refers to a single cell, how can I do this without that reference?
View 2 Replies
View Related
Dec 12, 2013
So I created a formula to genterate from one program to the other. I am use to being able to click at drag the formula and have it create a new one such as 1,2,3,4,5. This formula is a little more complex and does not do that.
Here is the formula.
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6
When I highlight and drag them I want it to create 121113,121213, and so on. It just keeps creating
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6
View 4 Replies
View Related
Dec 3, 2013
I have two workbooks because I can't figure out a chain formula that I need to be able to use in reverse.
First workbook:
Row A1 = x
Row B1 = A1*1.25
Row C1 = B1/0.55
Row D1 = C1/0.42
Second workbook (the same formula, just reversed):
Row A1 = B1/1.25
Row B1 = C1*.55
Row C1 = D1*.42
Row D1 = x
I would essentially like to put information in either A1 or D1 and have the other 3 boxes fill out automatically; is this possible or am I stuck using two workbooks...? Would a circular formula be able to work in my case?
View 4 Replies
View Related
Aug 31, 2009
I have a table of some numbers, and along the left side is a column representing what each row of data is. So in this case, it is a list of letters. What I'm doing is in each column of the data, I find the maximum number, and then I want to have it list below that what letter that maximum number is from. Here is an example:
View 2 Replies
View Related
Jan 14, 2008
I'd like the users to be able to change some detail in a couple of places and have it updated throughout the spreadsheet. Basically, the user can change the line name in any of the input sheets and the code changes the sheet name, and searches for the reference to the old name in the overview sheet and changes it accordingly.
The problem I have at the moment is that I would also like the user to be able to change the line name from the overview sheet too... I am having trouble thinking how to have similar code in the "Overview" sheets Worksheet_Change event without getting into a big constant loop... e.g. if the line name is changed via code on the individual input sheets won't that then trigger the first code, which will trigger the second etc. etc. I have the following code in the ThisWorkbook section:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sName As String
Dim sOldName As String
Application. ScreenUpdating = False
sOldName = ActiveSheet.Name
If Target.Address <> "$B$1" Then Exit Sub
sName = ActiveSheet.Range("B1")
On Error Goto ErrorHandler
ActiveSheet.Name = sName
On Error Goto 0
Sheet8.Select 'this is the overview sheet
Cells. Find(What:=sOldName, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate........................
View 2 Replies
View Related
Feb 26, 2012
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
View 2 Replies
View Related
Aug 9, 2007
I have been running a simulation for about 18 hours now and just received:
Run-time error '1004':
Method 'Add' of object ' Sheets' failed
I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...
View 9 Replies
View Related
Feb 18, 2014
I have a work sheet named "Main_List"...In column D starting with "D2" I would like to list worksheets that I would like to have printed via VBA.
The workbook has several hundred worksheets and I would like to list in column D only worksheets that I would like to print with VBA code.
View 3 Replies
View Related
Dec 26, 2009
I have a workbook with 26 sheets, labelled A to Z. Column A in all the sheets have names from rows A6:A35.
I need a macro or a code to extract all the names from each of the 26 sheets and paste it to a new sheet 'Names' under column A, such that names starting with 'B' paste under all the names 'A' and so forth till 'Z'.
View 9 Replies
View Related
Oct 29, 2013
In my attached sheet I've to find out final result in Column "F" (green shaded). My boss many times a year increases salary by certain percentage (say 30% or 40%) which is given in "E" column. After that I need to adjust it according to Pay Scale given in right side. For example one Executive's salary reaches 19880 after % increase. Now in pay scale we see in executive (row no. 13) it crosses 19800 so his final salary should be next upper value i.e 20600 (if it reached to 20700 after % increase, then his final salary could be 21400). That is final salary should be multiple of designation's increment plus minimum gross salary (Here 800X10=8000+11800=19800, since it exceed by 800, so salary should be 800X11=8800+11800=20600)
So we can either pull data from right side table or we can give formula in "F" column to get the required result.
salary adjustment.xlsx‎
View 7 Replies
View Related
Jun 18, 2009
Does anyone know an efficient formula for the “five-even” rounding rule? Someone asked me about this and gave me these examples:
6.6 must round to this ==> 7
6.5 must round to this ==> 6
5.5 must round to this ==> 6
8.5 must round to this ==> 8
9.5 must round to this ==> 10
66 must round to this ==> 70
65 must round to this ==> 60
55 must round to this ==> 60
85 must round to this ==> 80
95 must round to this ==> 100
View 9 Replies
View Related
Feb 19, 2012
I've got data being scraped from a site, putting 1 new workbook in a folder each day
each workbook has 40 sheets in it.
i need to run 5 modules in sequence on a sheet then loop to the next sheet and run the same 5 modules.
ive writen all the modules, and can loop them through the sheets in sequence but i cant work out how to loop them through the each workbook in the folder..
is there an easy way to do this or can it not be done because it would need access to the folder that holds all the wordbooks which lives outside of excel on the desktop ?
View 5 Replies
View Related
Jan 31, 2014
I am working on a maintenance form that I have attached.
I am wondering if there is a formula or condition that I can input to find and mark all the spaces that fall on a weekend.
Either to have the form auto-populate with a "W" as I have done in the form, or shade the boxes that fall on a weekend. Shading would probably be better or even shading with the "W".
View 9 Replies
View Related
Jan 12, 2009
Is there any way to create a cross-validation rule between 2 cells?
For example,
Cell A5 will have one of the following values (AA, BB, or AB) ....
View 11 Replies
View Related
Jul 8, 2009
Depending on the text entered into one cell, I'd like another cell to state n/a. For example, if cell a2 shows the text of cash, then cell b2 would show n/a.
View 3 Replies
View Related
Dec 22, 2009
I am using excell 2007 and am having a few problems with conditional formatting.
I am trying to do the following.
i have 6 cells in 6 columns ( c2, d2, e2, f2, g2, h2 ) each with figures 17.14+
Now i am trying to add a formula so when ever i insert a number in the cell below e.g c3 i want the cell c3 to turn green or red depending on the value in the above cell (c2).
so in all:
c2 has the value 17.14
if i insert the value 17.14 or less in cell c3 i want the c3 cell to turn green.
if i insert the value 17.15 or more i want the cell to turn red.
now i know how to do this in the conditional formatting feature but i want to do this will all the cells refering the the cell above.
i also do not want to create 6 diffrent conditional rules.
Is there a formula i can use that can be pasted into each cell and the cell number edited?
View 9 Replies
View Related
Aug 29, 2013
I have a table where Sales are in Column B and a % calculation is in Column E. I want to highlight the cells in green that are over 40% and that also meet the criteria where the sale in that row is above $100.
In conditional formatting I tried to use =and(b4>100,e4>40%) but it did not do anything. I am also okay with using a nested if statement.
View 3 Replies
View Related
Jan 4, 2012
What I need is for in the order start date column, I need a formula to pick out the first year where the value is inserted into the column and then insert the year into the order start date column. I would do this manually, but with thousands of rows a formula would prove to be most beneficial.
2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 Order start date
515 151 2013 200 2015 1515 2018 5454 2012
View 8 Replies
View Related
Sep 6, 2013
I have a project where if the user enters 'R' into cell H17 then cells B19:K19 are to be filled with black color. These cells are divided into three fields: B19:E19, F19:H19, and I19:K19.
When I select these three fields and use the conditional formatting tool the only cells that are black are B19:E19. Even though the rule explicitly shows that cells B19:K19 are to be affected. In order to have all three fields black I have to make three rules (one for each field).
The same behavior happens when I use the conditional formatting tool and select the cells from within the tool's dialog.
How can I get all three of the fields formatted with one rule?
View 2 Replies
View Related
May 20, 2014
Is it possible to create a rule that every time I type my name, for my e-mail address to pop up in excel instead?
For example, if I type "purple1686", I want "12purpleexample@domain.com" to show up as a hyperlink to my default e-mail program.
I am also curious if I was to write "my website" is it possible to make it a hyperlink to "www.purple1686example.com"?
Those two first may be easy to answer, so here is the challenging part for me:
Can I make that happen as a rule on every spreadsheet I open?
I don't know if I should make a template, or use a formula? The ideal scenario would be if there was a way to do it through conditional formatting.
I have used conditional formatting to change the color of a word every single time I open a new template and paste the words into it, or even by typing them out.
View 1 Replies
View Related
Jun 24, 2014
is there any rule for random number to be picked up? Any Rule, Any Formulae, Any Calculation Anything, Any authentic routine which may guess what would be the next random number? for example if first random number is 825587 then what would be the next?
View 4 Replies
View Related
Jun 10, 2014
I'm trying to find out the rule for de-duplicating data. I am removing duplicates based on an identification number in a data set of about 6000 records, including the duplicates (some records appear about 4 times). Due to the nature of the data I'm working with, there are only a handful of records that are "true" duplicates, i.e. some of the records appear 4 times but there is a difference in terms of location, etc and some are true duplicates in that there is no difference.
I need to know how Excel removes duplicates - does it only keep the first line that it finds for that identification number? Also, is there a way that I could create a rule for it to keep the record with the highest rate for example?
View 3 Replies
View Related
Mar 14, 2008
Want to make an if rule where a student gets overall 75% in a column, then in another column next to that that would automatically give him a B or a B+ you know what I mean?
Hear are the ratios
A+ - 90-100
A - 80-89
B+ - 75-79
B - 70-74
C+ - 65-69
C - 60-64
D+ - 55-59
D - 50-54
E+ - 45-49
E - 40-44
and another one
Excellent - 80-100
Very Good - 70-79
Good - 60-69
Moderate - 50-59
Borderline - 40-49
View 11 Replies
View Related
Feb 15, 2013
Is there a way to programatically count the amount of conditional formatting rules in a workbook as i have been given a workbook which performs really slowly and the amount of rules seem to be a lot just wanted to count them
View 5 Replies
View Related
Jan 9, 2014
I want to set a conditional formatting rule that makes a cell red or green depending on if the value in the cell is larger or smaller than the value in another cell +2% or -3%. If neither of these conditions are met no formatting should be applied..
a) In the cell (R104) where the condition is set I have the following formula:
=if(BP104=0;"";BQ104/BP104) (the values in BP104 and BQ104 is retrieved from a database)
b) The conditions I've set in R104 are the following
1) =isblank($R$104) (condition: "nothing")
2) =$R$104<$R$106-0,02 (condition: "red")
3) =$R$104>$R$106+0,03 (condition: "green")
c) In cell R106 the value there is a value of 90%
The conditions seem to work fine when the value in R104 is higher or lower than the conditions set in 2 and 3 above but when the value is blank in R104 the cell goes green, which it shouldn't.
View 6 Replies
View Related
Sep 20, 2009
I want to find the correct formula to find answers using excel for the folliowing:
1. >3Sds
2. 3SDs<>2SDs
3. 2SDs<>1SD
4. <1SDs
This question uses 3 sigma rule (see attached worksheet) and the formula needs to be re-arranged to meet new criteria. So, we need to consider both sides of nornmal distributiion (ND) curve. eg. when we say >3SD it means it should count all valuee falling in the rage "Mean(Average)+3SD AND Mean-3SD" i.e two extreme ends of the ND cureve. I have inserted a normal distribution diagram and colour coded the ranges so that it will be easy to identify. Please refer comments inserted in K7to N7, cells that contain formula that need to be reset.
View 2 Replies
View Related
Apr 21, 2014
How do i make a formatting rule permanent to certain cell's.
The steps i followed:
I selected the columns and rows to be formatted. in conditional formatting I selected new rule. then selected the second function, "format only cells that contain" in format only cells with: specific Text , then " ending with" Over Issues.
in format i selected the font and fill.
I saved and close the document, after reopening the rule is no longer working.
View 5 Replies
View Related
