I have the following formula that has ceased to work in excel. I don't think there are any errors in it so I'm unsure as to why the link no longer works. Neither of the files I'm using has moved location on the server.
I am getting a #REF error when using an INDIRECT function within a MATCH function to check against a dynamic named range. Basically, I am trying to get the row reference so that I can go back and extract other data from the row (in a table contained in another sheet) into the current worksheet.
I attach an example file for reference. The issue arises when a Dynamic Named Range is used. In the example file, if a value from a static range is chosen, the match with indirect function works, but it fails with the dynamic range.
Is it risky for someone to edit an excel document while it is still in the flashdisk or external hard disk? I was told that this habit can lead to loss of data or the data can be affected if someone does this. I was told that I have to copy/cut paste the document onto my desktop, edit/ work on it then move it back to the storage device. Is all that work necessary, or can I simply work on the document while it is still in the flashdisk or external hard disk..
I have a challenge with conditional formatting. I have products A-K with sales current year and last year and want to have year on year growth. I want to have one column where the cell formatting changes based on data validation.
Let's say product A sold 60 items CY and 50 LY meaning YoY variance is +10 items or +20%. Hence, if the relevant cell says "%" then the YoY figure should say "+20%" (formatted as a percentage) and if "VALUES" then YoY should be 10 (formatted as numbers/absolute values).
Remember also that YoY growth can be +100% (or more), 0% and -100%.
I am trying to copy worksheet between 1 workbook to another. But even the worksheet was successfully copied but the code inside wasn't. Do I need to copy the VBA module manually (create new proc/sub) ? Or there is a setting during Worksheet copy process?
Working on a user form... I have a combo box, with a list of the items available.... then I have a text box that inputs the quantity. Is there a way that I can put a list underneath in such a way that every time I select a item, put the quantity in the text box, it will list down in that list?
In other words... If I have 5 products for the same order I would pick one, qty and click add, then another one, qty, and add... so on. That way I would be able to see all of the items that I have picked.
Is it possible to use the HYPERLINK function inside of a validation list?
Presently I have a dynamically named range on another worksheet that includes a hyperlinked term. When I attempt to create a validation list using this named range the term appears in the list without the hyperlink.
I am making a buiness card request form, which I have attached. On the Master Sheet (Sheet 1), the user enters in his/her personal data, such as his name address, phone number, etc. After he/she finished entering all the relevant data, Business Card Layout (Sheet 2) automatically populates and shows the user what their business card will look like.
Everything works fine, however I just realized that the contact information might vary from person to person. Right now, a person can enter 5 different contact information, but most of them will probably only choose four.
I wanted to know how to shift the cells down if the user does not fill out one of the contact information.
I have previously used the following code to successfully pull out IE webpage source code for string manipulation.
Its a crude example to demonstrate the principle:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Public IE As Object Sub Sample() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True
However when I substitute in a Google websites address into the IE.Navigate command, the code runs to the "Source_Code = IE.document ...." line then flags up a Microsoft Visual Basic error. "Run-time error '438': Object doesn't support this property or method"
The webpage that I am trying to access is a confidential company site, so you won't be able to access it yourself, but starts with [URL] ......
The one thing that I have noticed about this website is the Privacy Report icon in the lower right status window (Picture of an eye with a restricted symbol in front). I don't know whether this is the cause of my problem, or purely an incidental observation.
Is there something peculiar with Google sites that means that the source code cannot be extracted in general, or is this an issue specific to my site ? Does the Privacy Report icon have any relevance, and if so how do I switch that off ?
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
I have a spreadsheet that i am tracking some Training dates on. i have the requirements written in the box categorgy on the attachement if anyone wants to see it. Ok so so far in the B colum (contains date of a computer based training (CBT))i have this conditional formatting: Formats for Red background
If a1=today and b1=null then colour of cell should be green, or If a1=today and b1between today and +7days then colour of cell should be yellow If a1=today and b1 between today and +14 days then colour of cell should be green
I am trying to do this in conditional formatting, but it doesnt work. As when we try copy to all cells the ref is still for B1 so it wont work on a spreadhseet with many data rows.
I have set up a basic sheet for a colleague, whereby the Team Leader can enter client names into a control sheet, and then the team can add the client names into the allocations sheet when they take on the care for that client, and Conditional Formatting colour codes the cells to show which program a client comes under.
My problem is, the conditional formatting works perfectly on my computer, and another colleague's computer, but for two other colleagues, only half of the colour coding works... leaving half of the clients cells white, indicating they are not allocated to a program.
I have checked that we are all using the same network pathway, macro security settings etc.
I have a spreadsheet that has values in the end column that read Addition, Deletion or No Change. I am trying to get the row cells in Column A to shade red, green or yellow depending on the value of the cells in Column I. I have tried the folllowing:
I chose the fill to be green for this but when I click OK nothing happens but I do not get any error messages.
I have the macro below that I want to use to conditionally delete all columns where the first row of each column does NOT have the following values but it seems to be deleting most of these columns as well all the others.
Fellow Excellor Sub Deletecolums_Conditional() Application.ScreenUpdating = False
I am trying to do a conditional formatting for the number of working days. What I am trying to do is:
As in the row D7 I give the actual workdays required to finish the task the Gantt Chart gets a specific formatting on the right appears for the number of working days.
I want to avoid the formatting if there are any weekend (Saturday or sunday)
Suppose I give number of working days as 20 then there would be 6 weekend days so it shall add to the overall actual working days but again sinnce we are adding 6 additional days one more weekend day would come in between so the formatting shall take care of this.
I have a spreadsheet which uses conditional formatting to highlight cells with certain values. It's works perfectly, except for a single cell. The formatting formula is and should be exactly the same as other cells in the sheet. For whatever reason, this cell will not change with the formatting rules. The cell is not protected, it doesn't have any oddball values or anything that I can see.
I have a conditional formula that highlights dates red when it meets a certain criteria. The file named MS Working, the conditional formatting formula (formula below) works as it should with no issues. The other file MS NOT Working, the conditional formatting formula (formula below) has stopped working as it should. What I did? I inserted a new column to the left. Where the MS Working file has only 1 column to the far left, the MS NOT Working file now has 2 columns to the far left.
It seems as if the formula adjusted itself when I inserted the new column; however, its not working.
MS Working Conditional Formula: =AND(TODAY()>B3,NOT(ISODD(COLUMN())),B3<>"",OR(C3="",C3=0),B3<>0)
MS NOT Working Conditional Formula: =AND(TODAY()>C3,NOT(ISODD(COLUMN())),C3<>"",OR(D3="",D3=0),C3<>0)
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("E:N")) Is Nothing Then If Range("E34").Value = 0 Then Application.EnableEvents = False Range("G8").Value = Range("G8").Value + 1 If Range("J34").Value = 0 Then Application.EnableEvents = False Range("G9").Value = Range("G9").Value + 1 Application.EnableEvents = True End If End If End If End Sub
I found code online that I can put on my sheet to get my formatting properties to stay the same for the items in my dropdown list located on another page. However the code does not work for conditional formatted cells...which is what I need. This is the code that I have that will carry over regular formatted cells. Just not Conditional formatted cells.
Every time I try to add the contents from the request form to the Master CCO tab, the information does not dump. The only way the form will close is if i hit the cancel button. I don't know what I've done wrong with the add request code.