Formula: CHECK And LINKED TO Which Consist Of The ID's Of Each Computer And The Attached Monitor
Aug 1, 2007
I'm trying to find a way to create an excel sheet for my company. It will be used to import into our CMDB. Its for computers(ID) and their monitors(LINKED TO)
We have made 3 rows: ID, CHECK and LINKED TO which consist of the ID's of each computer and the attached monitor. The CHECK row is where we put the word "OK" if that computer is physically present at that location. After this list was made, we decided NOT to link the monitors to the computers, so afterwards we will delete the row LINKED TO.
What i need to do now, in order to not have to seek every monitor manually, is to have a formula which looks up an id (i.e. WBE01111) in the LINKED TO row, and if that one exists in the ID row, it will place the word "OK" in the CHECK row.
Example: (with WBE01111 being a monitor attached to computer WBE03333)
[ID]_____________[CHECK]______[LINKED TO]
WBE03333________ OK _________WBE01111
WBE09999________ OK ________ WBE08888
WBE01111________(**)_________
**=formula must put OK here
So the WBE01111(monitor) below [LINKED TO] exists in the [ID] row, i want the formula to see that, and put OK below [CHECK] for WBE01111
View 2 Replies
ADVERTISEMENT
Dec 5, 2006
i woul like to make a macr which will check the system date on the computer to see what month it is and the populate cells A14 - A44 with the dates from the 1st till the end of the month.
View 9 Replies
View Related
Mar 17, 2013
I have attached excel with the logic I am using, but I have a problem and needed logic to update to make sure it is not inserting same data (duplicate data).
Please update the logic so that if it is having same data in sheet 3 it should not insert or delete duplicate.
Code:
Public Sub CopyData()
Dim ws As Worksheet, bi As Byte, vData(1 To 9), bi1 As Byte, vData1(1 To 9), bi2 As Byte, vData2(1 To 9), bi3 As Byte, vData3(1 To 9), bi4 As Byte, vData4(1 To 9)
Set ws = Sheets("Sheet2")
For bi = 1 To 9
[Code] .........
View 3 Replies
View Related
Nov 29, 2012
I have two sheets in a workbook.
i want to check a cell in sheet 2 is linked twice in sheet one.
also find whether a cell in not linked.
View 2 Replies
View Related
Jun 24, 2014
I am trying to create a macro that will create a number of check boxes, which are linked different cells. I have had some success in creating multiple check boxes and having them at the destination I want the problem is that instead of linking to different cells they are all linked to the same cell. I have attached a sample workbook SAMPLE.xlsx
View 1 Replies
View Related
Jun 25, 2009
I am trying to set up a cell (E10 on my attached spreadsheet) to act as a key that would collect all of the PO#'s from a seperate column (cells A12:A27 on the attached sheet) and wouldn't repeat any of the repeat PO#'s. I am trying to do this because I currently save each order form by the PO #('s) and at times I find myself typing a long string of PO#'s for the book name. It would be much easier if this key cell would reflect all of the PO's for me and I could just copy and paste the contents into the save as box. This brings up another problem- when I try to copy and paste the contents of the key cell I have now, I just get the formula, not what the formula returned.
View 10 Replies
View Related
Feb 18, 2014
I have thousands of drawings listed in colums and each in the row has revisions P01 to P10 for Proposal and C01 to C10 For Construction. Is there a way of getting the highest revision of all listed in a row without turning them into numbers when P is older than C , but 1 is older than 10? MAX function doesnt count when value consists of both - number and letter.
Manualy selecting the highes revision will cost me weeks... and brain damage.. as I have thousands of drawings and more are coming...
View 14 Replies
View Related
Apr 5, 2008
Is there a code or formula to get the name of my Computer?
View 9 Replies
View Related
May 28, 2014
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)
View 2 Replies
View Related
Jan 5, 2013
I have a Age calculating formula in an excell cell, which calculates the age as on another date.
The forumala is =IF(E4="","",IF($C$1="","",ROUNDDOWN(($C$1-E4)/365,0))) where E4 is the date of birth and C1 is the date in which I want the age calculated to be.
The above formula works very well on my computer, but does not on any other computer. If I access it from my sent items or from a shared drive, it works perfectly on mine, but not on any other computer!!! I get a #value error message on all other computers.
I have tried the foll:
If i enter =ISNUMBER(C1) where the age has to be calculated, I get #Name?
If I enter =ISNUMBER(E4), i get TRUE
The above is on the sheet where it does not work ( on others computer ) and i get TRUE for both on mine. Also both have the same date formats and same time zones...
View 10 Replies
View Related
Nov 12, 2009
I have a odd problem. I have workbook that I made I placed this formula in cell A1
View 3 Replies
View Related
Oct 13, 2009
Need formula to calculate the average %age in the attached spreadsheet. I would like to enter a score between 1 and 4, but with 1 = 10%, 2 = 25%, 3 = 80% and 4 = 100%. The score in the cell must still show as between 1 and 4 but the total must be an average of the relevant %ages. i.e. if scores are recorded as 1, 2, 3, 4, then the total average % will be (10%+25%+80%+100%)/4 = 53.75%. I'm not sure whether this should be in the Validation or in the Total cell.
View 2 Replies
View Related
Apr 18, 2006
I have a problem when using autoshapes. Above a display of columns of data, which are of different widths, I need to display several pairs of boxes, which consist of a descriptor box and a value box. If I use formatting in cells, I am constrained to use the width of the columns below. So I will be using autoshape rectangles. No limit on box widths and I can link each one to a cell to send text to them. So far so good.
However, I would like to contain a group of these box pairs within another autoshape. When I do this, if I fill the outer autoshape with colour, I have the choice of making the colour solid, which means you can't see the inside box pairs at all, or semitransparent, which allows the boxes to loom through a fog of the outer colour. What I would like is for the inner boxes to show properly, and fill only the space between the inner boxes and the rim of the outer autoshape
View 4 Replies
View Related
Mar 19, 2007
We use a spreadsheet to log all transactions at our front desk. I've made a column that automatically calculates the time when a transaction is input into a row. The formula is as follows:
=IF(D5="","",IF(A5="",NOW(),A5))
This morning that formula stopped working on the computer at the front desk, returning a message about a circular reference. I can open that same spreadsheet on my computer as well as others in the office and it calculates fine, but of course on the one computer on which I need it to work, it won't ;P
All computers are using Excel 97. Presumably a setting has changed on the front desk computer that's affecting this calculation.
View 4 Replies
View Related
Feb 5, 2010
The results of the formula in cell K36 in the attached spreadsheet returns a value of null. It should be $1,200. Am I blind or have I done something wrong. I just can't see the problem with the formula.
View 6 Replies
View Related
Jun 17, 2009
I need a formula to drag down the attached that will place zeros infront of any numbers that don't have 9 digits. so if a part number consists of 6 digits then i need 3 zeros in front of it, if a part number consists of 9 digits then i dont need any zeros proceeding it.
View 2 Replies
View Related
May 8, 2014
I have this code that works well for the entire column, but, I only need to monitor every other cell in the columns. the cells I want to monitor are:
B10, 12, 14...B96. the columns to be monitored are B,F,J,N,R. the column part is good. I don't want the pop up box to appear when I change B9, B11, B13 etc...
View 6 Replies
View Related
Jan 9, 2012
How do I set a worksheet to monitor H29 to run a macro whenever H29 changes?
I found [URL] but I'm having trouble getting it to work right
I cant figure out the errors im getting enough to correct them.
View 3 Replies
View Related
Jul 22, 2008
i have a userform which displays a textbox which shows a variance in price terms based on a sheet which has live data via dde.
I would like to turn this userform into some kind of floating monitor if possible. At the moment - once you fire up the userform - the price in the textbox is fixed and does not change with the cell which fills the textbox on the userform. The only way is if you close the userform and re-open - then you will see the current price. I would like to keep this userform floating so that you can still select other cells on the sheet.
Is this possible?
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
With ThisWorkbook.Sheets("price").Range(ActiveCell.Address)
If .Value = "" Then
Range("D31").Select
Me.TextBox1.Value = "No Trade Selected"...................
View 9 Replies
View Related
May 7, 2014
Below is code. However, I need this to happen for multiple cells.
The cells that I need to monitor are B10, B12, B14.... through B96 and F, J, N, R columns for the same rows. The input values will be stored in cells AM10, AM12, AM14 .... through 96 for the B column results. Then for F, J, N, R will be AQ, AU, AY and BC respectively.
Additionally, when B10 has an input, and the user input box displays for the number of hours, there is a cancel button available, but it does not work. How can I exit the loop and clear contents of B10 if the "Cancel" button is clicked in the dialogue box.
[Code] .....
View 5 Replies
View Related
Jun 8, 2009
when I run code attached to a User Form, the user form will leave trails (similar to what you see when you win at the Microsoft Solitaire game) when you drag it around with the mouse.
I haven't been able to figure out why this is. Some of my code runs and allows me to reposition my form without issue. Other times, I get these 'form trails' that obscure what's behind the form.
View 2 Replies
View Related
Oct 15, 2013
I am trying to find the last row of worksheet "A", return the value, then in Worksheet "B", reference Worksheet "A" Column J - last row. It gives me an error for the formula. I don't know if I am close to the solution, or way off. Here is my code:
Dim LastRow As Long
Sheets("NICMap31 Data").Select 'goes to worksheet A
Range("A1").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Offset(1).Row 'finds the last row of worksheet A
Sheets("NIC MAP Data Table").Select 'returns to worksheet B
Range("C7").Select
ActiveCell.formula = "='NICMap31 Data'!(J & LastRow - 1)" 'link to worksheet A with this formula.
View 2 Replies
View Related
Aug 19, 2008
Is is possible for me to copy a linked formula down instead of accross. For example, i have two tabs in a spreedsheet. the first tab have data in the following cells a1; a2; a3 but going through column M. Basically, a1;a2;a3 have data through m1; m2; m3...
On the second tab, i am linking from one cell to the data in a2 from the first tab; however when i copy my formulas it is giving me data from b2, c2, d2, etc instead of pulling cells a2, a3, a4, etc...
Is there a way to copy the formula to pull down instead of across?
View 9 Replies
View Related
Jun 16, 2009
I have a spreadsheet control inside of a userform. I can generally access this spreadsheet and do what I need to do with it. My problem is that I need to monitor it for the cell change event. I normally accomplish this with:
View 4 Replies
View Related
Sep 26, 2013
I know what i need to do to get it to open the file I want. I dont know how I set the position of a file that i am opening. In this case a .pdf file.
I have searched and came up with a lot of info for userforms, but I am not working with a userform in this case.
View 1 Replies
View Related
Oct 14, 2006
We have a set of workbooks with a linked Vlookup formula. When we email the file to staff that do not have access to the linked file, the linked formula seems to change the directory. see below
Original Formula
=VLOOKUP($A$30,'G:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)
Formula after user opens email with the drive changed automatically to C
=VLOOKUP($A$30,'C:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)
Note that the user does not have access to the G drive and they are not updating the links when they open the file.
View 4 Replies
View Related
Jul 26, 2009
To monitor fast moving decimal values in the even numbered cells( e.g. F2 to F 30 ) of a column, ignoring blank cells, and if two of cells values simultaneously fall below a certain value e.g. 2.04 True if not False.
View 9 Replies
View Related
Jul 26, 2007
I've run into a roadblock in excel 2003 in trying to create an accounting spreadsheet that will pull in information from several separate workbooks (invoice files) using formulas which substitute invoice Nos. (which coincide with the tab/worksheet names).
My obstacle has something to do with order of operation/calculation I presume... I've successfully used labels to bring in information from separate tabs on the same file by using the substitute function with generic formulas.... in this case exel processes the formula, likely because it calculates the linked information first in its order of calculation; then trys to substitute information into the result of a calculation already made....
I'm trying to find a way to tell excel to look at respective cells in the same file but on a different tabs/sheets...
For example; I've been trying to pull in the date an invoice was issued for client "X".
I plug in a formula in a remote cell of my accounting spreadsheet "Y" which basically says to bring in the value on file ["X"]; worksheet "tab"; cell $J$11 - where "tab is just a filler that I would like to use the substitute function to exchange with respective invoice Nos. (i.e. 049; 050; 051; etc.) My hope is that by adding these 3-digit sheet identifiers; all other information from respective cells (in file "X") will be pulled into my accounting sheet - file "Y" (i.e. invoice total; applicable pst; gst; expenses; etc.) I have written as many formulas as required for the different types of info sought after, the only variable being the 3-digit worksheet identifier. The problem has been that excel first processes the formula in the remote formula cell(in file "Y"); then applies the substitute function to info already brought in from File "X"; whether it is a date value; or a currency value. I can't figure how to have the substitute function change the formula... not the result of the original function...!
I've tried to format the source formula cell as text (therefore not calculating a result at that instant) however the substitute function inherits the format of the source formula cell.
View 11 Replies
View Related
Feb 10, 2013
Essentially I have three columns.
Column 1 is the name of a property
Column 2 either says "Primary" or "Secondary"
Column 3 has a date of when some building work is due to be completed.
I need a formula that can tell me how many entries there are in the second column of "primary" sites that have a completion date that is less than the current date.
View 3 Replies
View Related
Mar 9, 2008
I currently have numerous workbooks with linked cells to a master workbook, but the linked cells dont appear to carry over or retain the formating from the master workbook (e.g. font style, bold, underlined, font color, cell color). I make changes quite often to the master workbook and would like the changes carried over to the other workbooks. Does anyone have a script where it would automatically carry over the font formatting? I hope this is understandable.
View 4 Replies
View Related
