Making 1 Cell Equal Another Cell Including Its Comment?
May 18, 2014
I have a spreadsheet with some cells which return values using INDEX and MATCH to bring back the entire details of a person based on there membership number. However I require the comments to be attached with the returned values. The comments have there image in, rather than just text.
I have found some answers with vba, which do the process, however you had to manually enter the cell, from which the comment was to be copied from, into a pop up box. This involved finding the box myself to then select the box to take the comment from. In doing this I had found and read the comment, which is the is the process I'm trying to bypass.
I have this project for work where on one sheet information is put in by typing AF, CF, WF. I need to have Excel read the AF, CF, WF and make that read as the full word. Is there anyway to do that? I can get my boss to have people just put A, C, W
I have a workbook which i wanted to make backup, i have found several codes and from that i have modified according to my requirement, now everything working fine without any issues but i don't know how to protect the backed up file with write password.
How to correct my formula because it does not work?
Here the formula: =COUNTIFS(Data!C2:C24005,A17,Data!M2:M11149,"<=0")
I need to insert the formula in column C (Findings tab) which counts the rows in Column M (tab named "Data") that equal A17 (Column A in "Findings" tab) and which are less or equal zero. In addition if I drag the formula down I want to only the values be entered in highlighted in blue cells in column C (SKUs With Zero Sales in "Findings" tab) and empty cells in not highlighted cells. I attached images of these two tabs: Data and Findings.
I'm wondering if it is at all possible to have a cell comment automatically update depending on what data is input in the cell (via user input, VLOOKUP, etc.)?
For example, if cell A1 contains the text "CHARLES" with a comment saying "Employee of the Month", and is then subsequently updated with the new text "JOHN" (again, via direct input, a VLOOKUP, data validation, etc.) is there a way to have the comment automatically update to say something else, such as "Team Lead" for example?
I've considered using VBA to accomplish my goal, but am unsure how to compose an effective code to do so. I've also considered perhaps creating a named table filled with all the different comment possibilities I would like to have used in this cell and then inputting a formula in either cell A1, or the comment contained therein, that would then call the corresponding text from that table based on the data in cell A1. Honestly, I'm not sure that what I'm trying to do is even possible;
How, via VBA, would I add a comment from a userform (text box name: txtReason) into a cell that may already have a comment in it? I would like to keep the comment that is in the cell and then have the program add a "/" and the next comment from txtReason..
I am working on a database and trying to remove duplicates. When there are duplicates, I want to take the contents of one cell and copy them as a comment on the cell above (or below).
I tried to work on a small macro to do so, but the contents of the cell cannot be copied (it comes out as a blank).
The long way is to 'double click' in the cell, Ctrl+A, cut, click the cell above, Ctrl+F2, paste. This way is very long as I have thousands of duplicates.
What is the best way to reference a cell in another workbook and return the cell contents and the comment on that cell. I would like the comment to come across as a comment in the new workbook becuase the comment is actually a picture.
Hope this makes sense. I did find a macro through googling but I couldn't get it to work? I don't really want to copy and paste because eventually I have hundreds of sheets & thousands of cells to refer to.?
So I've got an Excel sheet with two cells - A1 and A2. In cell A1, I have a comment that says "Hello". Is there any formula I use to copy the comment from A1 and apply that comment to A2?
I have a recipe and a set of instructions on a worksheet in the cell range A1:H50 and would like to copy the whole range to a single cell as a Comment.
At the moment I select the range, copy and paste it to MS Word.
In Word, I select all and copy, then back in Excel, select cell, insert Comment then paste the text into the Comment box. Then I have to resize the Comments box. Also doing it this way loses some of the original formatting I could get round that by saving the cell range as a picture and inserting the picture in the Comments box.
Surely there has got to be a better way. Using 2007 Excel & Word
I am trying to write a macro that will make the bottom cell in a column equal to the top cell. The top cell will not always be in the same row and there won't always be the same number of cells.
I have a sheet in Excel 2004 for Mac that is a patient list with over 2000 patients. I need a rule that will compare values (patient IDs and responsible party ID) in two adjacent cells, if the values are different, to delete the entire row. The goal is to get rid of patients (rows) that are not the responsible party (responsible party ID).
I have two cells, both of which have drop down lists for options 'a' & 'b'. When one equals 'a' the other should be restricted into only saying 'b' and vice versa. I would also like the user to be reminded that he can only select 'a' once from the two menus. Ideally I would like to do this in a formula rather than VBA?
Simply need a cell to count another cell is equal to certain value.
For example if B3 has a value of 6 then I need J3 to equal 1 or if B3 is equal to 3 than I need G3 to equal one. I tried the the COUNTIF but I am sure i am using it incorrectly.
I would like to create a VBA code where it will delete the entire column if the cell value is equal to value in D2
For example:
Sub Delete_Columns() Dim rng As Range, cell As Range, del As Range Set rng = Intersect(Range("G2:S43"), ActiveSheet.UsedRange)
For Each cell In rng If (cell.Value) = D2 _ Then If del Is Nothing Then Set del = cell Else: Set del = Union(del, cell) End If End If Next cell On Error Resume Next del.EntireColumn.Delete End Sub
======
But I think this line is wrong but I am not sure how to fix it - If (cell.Value) = D2 _
I've been pounding away at a project for two days trying to determine a way to identify a solution, to no avail.
Here is some experimental code. Under the assumption that in Column "A", every fifth line contains some text (a, b, c, etc) I want to place a function in column B that would identify the closest occupied cell above and to the left of the cell containing the function. The sub achieves this, but the function has a problem because of the Activecell line. Is there a way to identify the location of the cell containing the function to act as a reference point for the range?
Sub find_last_heading() Application.ScreenUpdating = False x = ActiveCell.Address ActiveCell.Offset(0, -1).Select last_heading = ActiveCell.Text Do Until last_heading "" ActiveCell.Offset(-1, 0).Select last_heading = ActiveCell.Text Loop MsgBox last_heading Range(x).Select End Sub Function cathead(Optional reference)
I need to identify rows where the contents of a given cell are equivalent to the cell below. I have managed to get this working based on iterating through the cells and selecting them both for cutting.
However it doesn't work effectively: It seems to be leaving blank lines in the original sheet and further fails after the first execution. I would be grateful for any suggestions on a good method to perform the following operations: (1)to compare the cell with the cell below I have used
If cell = cell.Offset(1, 0) Then
(2)to act on the rows for the two connected rows
Sub Test4()
' Loop through rows
Dim TheExtract As String Dim cell As Range
'Application. ScreenUpdating = False For Each cell In Worksheets("Sheet 1"). _ Range("A1", Worksheets("Sheet 1").Range("A65536").End(xlUp)) TheExtract = cell If cell = cell.Offset(1, 0) Then cell.Rows("1:2").EntireRow.Select Selection.Cut Sheets("Test").Select ActiveCell.Offset(3, 0).Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown End If Next cell 'Application.ScreenUpdating = True
I have a sheet that calculates what items are on any given transaction - this works well. I now want another table that counts how many times a certain brand comes up.
For example one item on a transaction might be "TENSOR REG TRUCK BLK 5.0 x2"
I need the table to look at this and count if tensor is in it (sheet name: 'Sheet 1') but I want it to look at the list of brands and and reference that brand rather than type it out individually.
There is a big range of cells with normal numbers (ex. 100, 150 .. etc), but I need to convert them in the following formulas that give the same numbers as a result: for example if the cell value is 100, I need to convert it in =if(iserror(100);0;100) and so on with all other values. Is it possible this to be done automatically for all cells?
I have searched the forum several way to resolve my issue and the only solutions were as follows:
PHP Public Function UserName()UserName = Environ("username")UserName = Computer("UserName")End FunctionOption ExplicitFunction NetworkUserName() As StringDim responseNetworkUserName = Environ("Username")End Function
Problem is that I get a #REF! and #NAME? instead of the user names I am using Excel 2000 (I can not upgrade, Government PC) plus we need it to support Excel 2000 - present versions.
code that will go to a cell in Sheet1 column A that has the same or closest value to cell B11 in another workbook. The number values in the column are to three decimal places and the run in order down the column from lowest value to hightest.
I have a monthly forecast document that is updated by an operator monthly. At the end of the month, the operator presses a "Copy Accrual" macro that copies accruals for the month and stores them on a seperate tab. After sending the document back to the Finance team (necessary step due to software complications), Finance will send the operator back an updated file at beginning of next month. The operator than presses a "Paste Reversal" macro that is supposed to input the negative of the accrual stored in the seperate tab. The problem is that it is posting as the same value (not negative of value). I tried recording a Macro to fix this, but it did not work. An example of the code that came up with is:
I am copying values from a range of cells in one worksheet to another worksheet in the same workbook. I am able to grab the cell values, but I am unable to copy over the cell comments at the same time.
The code I am using is below.
If Cells(5, 6) = "CompetitorA" Then Worksheets("Competitor1").Activate Range("D7:D144").Copy Worksheets("CompetitorComparison").Activate Range("F7:F14").PasteSpecial End If
I want a dialog box to have a label box reference a cell within a work sheet. This referenced cell contains a formula linked to other cells so it will change value.
How wud I find out the total number of rows in a column, that are non blank? Value "False" and N/A are considered as blank cell , but a cell with --> one or more space " " is allowed.
