Indent Cell Depending On Value In Another Cell
Jan 8, 2014
I have a work sheet that is used for pricing purposes as follows
Column "A" contains a number between 0 to 4, where
0 defines a clear row
1 Defines Product Category
2 Sub Category
3 Actual product
Column "B" contains line item e.g
A 1 would produces 1
A 2 would produce 1.1
A 3 will produce 1.1.1
A 4 will produce 1.1.1.1
and so on
Column "E" contains the description of the product.
What i wish to do is if column "B" contains a "2" indent text in Column "E" by 1px, and if column "B" contains a "3" indent text in Column "E" by 2px and so on. If this could be added into a conditional formatting formula that would be good.
From what i have searched i cant find a means of doing this or a suitable function to assist in the formula.
View 2 Replies
ADVERTISEMENT
Sep 22, 2009
I was wondering if it was possible to do a SUMIF function depending on the indent level, e.g. sum all of the values which have an indent of 2 in the row headings.
View 2 Replies
View Related
Oct 9, 2007
I am trying to do some automatic formatting of a list of data to create an indented BOM structure in a similar way to DDiddy's post "Indent Data Based On Adjacent Symbol" (Indent Data Based On Adjacent Symbol.)
But want to set the IndentLevel for Column B by values in Column A. I.e. a value of 1 in Cell A1 indents Cell B1 by 1.
Username shg posted some code that he suggests should do this, but I failed to get it to work. Can anyone explain shg's code or suggest an alternative that would work, preferably automatically upon entering a value in Column A?
I'm only just starting to learn Excel macro's, so please forgive me if this post is in any way unclear.
View 9 Replies
View Related
Jun 9, 2009
I'm working with text cells I get this tiny indent on the left hand side of a cell about the size of one hit of the spacebar button.
Excel doesn't recognise this as an indent and I can't get rid of it. It's, pardon my french
View 9 Replies
View Related
Mar 27, 2007
I have 2 columns and 10 rows (A1:B10). In column A are certain text values. How do I write a macro to go through the table and preform an operation in the corresponding cell in column B depending on the value in cell A? for example, if A3 = "Complete", overwrite the formula in B3 with the value (paste value)?
View 2 Replies
View Related
Jan 9, 2013
I am trying to create a sheet in XL 2010.
In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.
Column B is when the weekly inspection is due.
Column C is when bi-weekly inspection is due.
Column D is when monthly inspection is due.
Column E is when 6 monthly inspection is due.
I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A
I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.
For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.
A1
B1
C1
D1
E1
Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due
January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3
View 3 Replies
View Related
Feb 9, 2007
Is it possible to use a formula to shade a cell dependent on a condition? I have tried an if formula (see below) but it is incorrect. =if('November 2006 SVOC'!B6>'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [red],if('November 2006 SVOC'!B6<'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [blue],))
View 3 Replies
View Related
Aug 7, 2014
I'm trying to create a tag with a color border. What I desire is to fill the BLANK cells around the tag, A1:D1 + D1:D19 + A1:D19 + A1:19 in a certain color based on the text value of the cell B11. There are 5 different values, such that if the B11 read Red Sox - the boarder is going to be red, if it reads Houston Astros it will be dark blue, etc..
I have a similar problem with changing the color of the cell based on the month. So regardless of the year, 2014, 2015, 2016, etc... If I use MONTH() function I can just get numbers from 1-12. I want Cell C16-C18 to be certain color depending the date entered in cell C17 such that for each quarter, months 1-3, 4-6, 7-9, 10-12 they are different color.
I have had no luck with conditional formatting (and I also believe that it is good up to 3 cases only). I am decent in logic/programming language but have little knowledge with macro notation and especially how to run them in excel 2013. I do know how to start it alt+F11 and that I need to make sure that code is written under the specific sheet where my tag is located.
View 6 Replies
View Related
May 9, 2013
I'm using Excel 2010 and I would like to format a cell (say, the font of that cell turns RED) if the value of another cell meets a certain criteria.
View 9 Replies
View Related
Mar 20, 2014
I have the names of companies in one column, and the amount they owe in cells in the column beside them. I then have a second list of companies that is a subset of the first. Is there a formula that would place the amount they owe in teh corresponding cell adjacent to the compny in the second list? I've attached a sample workbook, Full Company List in column A, amount owing in B, trimmed down list in D and ideally I'd like the corresponding values in E.
View 3 Replies
View Related
Nov 19, 2008
What I need to do is have a cell that will be say yellow until there is information put into this cell. The information could be in the format of text or numbers. The information would not always be the same so it would need to be yellow when there is no information in the cell and another colour or white when there is information in the cell.
View 6 Replies
View Related
Mar 6, 2009
I have a file consisting of two columns, called, "bom ref" and "material"
I need to show the data as indicated in the file, headed required format required.
In effect where I have a 0, that is the material I need to show for every row with a bom ref. of 1,2 or 3, down as far as the next 0 but not including the 0, when I reach the next 0, the material is a new letter, and that letter needs to repeat down as far as the next 0, but not including the 0 and so on.
View 4 Replies
View Related
Mar 7, 2012
code which highlights a cell depending on the criteria of another cell.
Example.
In B1 I have the formula =NOW() (we all now what this means).
In B2 I have a completion date.
I would like A2 to fill with a specified colour.
Can this be done so that it works down the whole sheet, if the date is in B5 then only A5 will highlight so basically only the A cell on the same row will change depending on the date in B on the corresponding row?
View 4 Replies
View Related
Jul 23, 2013
So, I just started checking Excel Macros. I'm working on a quite large data base where I need to search for a given part number and then move N columns to the right and display the text inside that cell on a message box.
Here's what I got 'til now:
Private Sub M0016216_Command_Button_Click()
Cells.Find(What:="M0016216", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Select
[Code] ........
View 3 Replies
View Related
Sep 26, 2006
how to make a cell a color depending on a number in a different cell? Example...if A2 is 92.6 how would I make D2 blue? But if A2 were 91.9 how would I make D2 red?
View 5 Replies
View Related
Nov 28, 2012
I have a cell in a column A1 where is written: "XYZ 492" and in another Column is written "492".
Now I want to remove the "492" out of the first cell with a furmular.
View 4 Replies
View Related
Mar 23, 2014
I'm trying as below:
If F3 contains specific word i.e. FCL then only cell K3 and L3 are unlocked or available for data input.
Additional information:
F3:F10002 contain data validation list
I need to apply above condition on K3:K10002 and L3:L10002
I did some search on this forum and I came to know above can only achieve through VBA only. Is there is any possible way without VBA? I'm not good with VBA etc
View 7 Replies
View Related
Oct 10, 2008
i'm trying to ask my spreadsheet to fill a cell with either 'YES' or 'NO' depending on the value of one cell. I've succeeded in getting it to enter 'YES' but can't figure out how to tell it to choose between the two options. This is the formula so far
=IF(L5>2,"YES")
View 3 Replies
View Related
Jul 9, 2014
I want to conditionally format cells K8, L8 and M8 so that when cell C3 contains a 3 it causes the previously mentioned three cells to fill black.
I cannot seem to work out how to do this using the options available under conditional formatting. Is there another way?
View 1 Replies
View Related
Mar 4, 2009
I'm trying to get the pink cell in THIS sheet to match up with the correct MONTH'S TOTAL in the pink highlighted column in THIS sheet. i couldnt figure out how to explain it any clearer.
View 6 Replies
View Related
Sep 3, 2009
I was wondering if it is possible to fill a row with a color depending on the value of a certain cell. Say, if the value of cell N5 is 0 then row 5 turns red.
View 3 Replies
View Related
Jan 30, 2014
[Code] .....
But what I want to do now is to strikethrough to eliminate unwanted data when a particular option is selected from the drop down list. In this case ( If User choose "Mens" for Gender, and NOT "Pants", Then Strike though the Result "NIKE". This works for the template in column E only using this code.
[Code] ...............
I tried to modify this like
[Code] ...............
So that every time x changes, the condition changes, but it seems like I cant do, Range("NIKE_" & x).
Attached File : Test1-2ed.xlsm
View 2 Replies
View Related
Mar 24, 2014
I would like to run a macro that searches column [AS] and hides any rows in that column that contain "0".
View 6 Replies
View Related
Jul 3, 2009
I have another macro request.
I would like to grey out a certain qty of cells depending on the 1st number in another cell. So 1st of all it would have to check in column B to see if there is any data and then check for the first number in that cell (there will be many numbers in the cells but we only need the 1st one (Ex.: B3 = 3 / 8 & 8).
Then it should start counting from column D the found number of cells multiplied by 2.
Ex.: if Cell B3 contains 3 / 8 & 8 it will count 3 x 2 = 6 (3 cells x 2 = 6 cells)
Then, if after that count (now at I3), in the next cell over (J3) if the is data in the cell above (J2) then it should apply an Interior.ColorIndex of 15 for the next cells until there is no data in the cells above (if nothing after K2 the grey will be applied to cells J3 & K3 only).
View 14 Replies
View Related
Oct 30, 2013
In three cells selected from a sheet, use the "MIN" to calculate the minimum value of the values ββof these three cells. After calculating the minimum value, apply a conditional formatting cell and it is colored green.
What I need is that, once established the minimum value in another column and cell specific, run a specific formula, taking into account: If the minimum is in a column X, and Y in a row, the formula should run only on the cell that is in the X -2 column, row Y-1 (two columns less and less row, where it is the cell whose value is the calculated minimum).
Example : if the minimum is in the cell " K24 ", the " Formula 1" must be executed in Cell "I23", if this minimum calculated were within the cell "H24", the "formula 2" must be run on the cell "F23" and if the minimum was in cell "E24"ββ, the "formula 3" must run "C23".
View 4 Replies
View Related
Aug 29, 2008
What i want is for a message box to appear automatically when the workbook is opened ONLY if Cell C4 (which is merged to F4)...
If there is information in this box then do not run macro
in the message box I want a box you can type in that will enter that information into cell C4. I know I will have to us a user form but what is the code I need?
View 9 Replies
View Related
Sep 4, 2008
I have a range C20:C38
In this range I have 15 codes that can be put in. If one of these codes is typed in incorrectly then a message box will appear stating "this code is incorrect please re-do"
can anyone help?
a few of the codes are
"S" "F" "O"
View 9 Replies
View Related
Dec 30, 2006
Once again I strugle with how I write the title for my IF Function problem, but anyway.
This is my problem:
I have a spreadsheet to count times
column D and F is:
D Start
F Finish, on column G, I have "F-D"
I start in D2 and type the first time.
F2 = D3 and
G2 = F2-D2,
If I have nothing in D3 I got an error, and if I don't fill all sheet I also got an error. What I want is, in the end of the row I have a cell with the final time "D36" where "F35=D36" if I made only half a way lets say until "D19" then "F19" should be = to "D36" and not = to "D20", stopping like this the calculations, doesn't give me errors.
View 4 Replies
View Related
Mar 8, 2013
The purpose of the excel sheet is hour administration. In the first page you fill in which group it is about, what week it is about and the amount of hours to change.
There are several other sheets named after the group that are filled with some kind of agenda.
An example: I want to make a change to the amount of hours group X will be there in week 3. I fill out the 3 columns (Group, Week, Hours) and hit a button. It should now check those 3 columns for data and modify the hours for the correct group. It is not done yet but I'm running into a problem already, the function I use to find the row with the correct weeknumber in the Group sheet returns wrong numbers and I don't know why.
VB:
Private Sub CommandButton2_Click()
Dim Week As Integer
Dim WeekRow As Long
Dim Groep As String
Dim Uur As Integer
[Code] ....
What I am expecting to happen is this: It checks A1, B1 and C1, selects the correct sheet, executes the function FindRowByValue (the week number is in column B) and fills Week Row with the result, then returns to the original sheet, gives a debug message and repeats this 5 times.
The debug message shows that the Week Row is not returning the row with the week number I filled in but instead returns either the week number itself (so if I wanted to find week 5 it returns a 5, while week 5 is in row 28), or a different number that is incorrect.
View 1 Replies
View Related
Apr 30, 2013
I am creating many graphs. I have already split my data so I can use CurrentRegion.I want each graph to have a different title using the text value of a cell which would be (6 * j - 5, 2) inside the For/To, but I can't get it to work no matter how much I tinker.This is the code as is without any of my attempts inside to clutter it up.
VB:
Dim p As Long p = Sheets("ForWeb").UsedRange.Rows.Count For j = 1 To (p - 1) / 4 ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlLine ActiveChart.SetSourceData Source:=Cells(6 * j - 5, 5).CurrentRegion ActiveChart.Parent.Cut Cells(6 * j - 5, 1).Select ActiveSheet.Paste Next
View 2 Replies
View Related
