If/Then Statement: "If Cell A1 Of Sheet 1 = "a", Then I Want To Put The Contents Of Cell B2
Oct 27, 2009
See the attached example spreadsheet which will make this much easier to understand. I want to create a formula in the second worksheet that says "If cell A1 of sheet 1 = "a", Then i want to put the contents of cell B2 ("flour") into cell A1 of sheet 2, I want to put the contents of cell C2 ("egg") into cell B1 of sheet 2 and I want to put the contents of cell D2 ("sugar") into cell C1 of sheet 2. If not put 0.
I need to create a formula which will change the contents of the cell to one of 3 options based on the value of another cell
For example if I was using B2 as the cell to reference I need to say IF B2 = 0 then say "Closed", IF B2 = 1 then say "Open" and if its any other value then say "Error".
I'm having some trouble having all 3 if statements nested correctly.
I don't want to do this as a macro just as a formula.
I have a conditional IF statement, where I want the content of the cell to be blank if the result of the IF statement is false, ie I want the content of the cell to be as if there were no formula in the cell (this if so that the formula COUNTA(Sheet1!$B:$B) only counts the cells where the result is TRUE).
For example, IF(A1=1,1,"") where A1 1, the content of the cell will have something in it (a space), and won't be blank.
i am trying to automate altering a DAT file. Basically, each line of data in the DAT file contains an entity name, an account name, and a value. for some of the accounts, i need to change the entity name. for all other accounts, i can leave the entity name alone. if i copy the DAT file into Excel, and use text to columns, how can i create a macro to compare the accounts to a range of cells containing a list of the accounts i need to alter the entity name for, and then change the entity name if there is a match?
the account does not need to match perfectly, the accounts i input only needs to contain the reference account. the list of accounts and the entity name i need to change to are in cells on another sheet. i thought about doing a loop to run through each line of data, but i am not sure how to do that.
I am working with a spreadsheet that has our user data; User(First) Name, User(Last) name, Computer Name, MAC address, Phone Number, Seating Position and Network Port Number.
I have let this get horribly out of sync, moved computers around, and moved them while the MAC address column was hidden, As a result, I have a bunch of users listed as being at the wrong computer. I have a second spreadsheet I generated that gives me the logged in user for about 2/3 of my computers, with the computer name(Just 2 columns, 'A' and 'B'. I would like to have Excel match the computer name and then overwrite the user name. For example, if the first computer in my correct user list is DELL-99945ty2, and the user name is "Jimbo Jones", I would like it to search the first Excel doc(The one with all of the user data), and replace the user name with "Jimbo Jones". Is this simple and straightforward?
In my main spreadsheet, the user name is in column B and the computer name is in column G, So I want to use the values from Column B in my second document to find its match in Column G of the first spreadsheet, and then replace the data in Column B with Column A in the first spreadsheet.
I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'. e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
In the first worksheet, called "Overall Totals", I have only two cells utilised (A1 and B1).
The first cell contains the name of one of the other worksheets. This may change, in order to reference other local worksheets.
The second cell should use the value contained in the first cell to go and get a particular piece of data from the other worksheets.
So, for example, if the first cell (A1) in "Overall Totals" contained the data "SheetName 1", and the data we wanted to get from that sheet would be cell F12 from that sheet, then I would expect the second cell (B1) in "Overall Totals" to say something like.
='SheetName 1'!F12
if I was hardcoding it. But I don't want to hardcode it, as the value in A1 could change, and I'd have to re-change lots of hardcoding.
I have =VLOOKUP(A4,Jan!A:D,2,FALSE) in my workbook, but I would like to be able to have the sheet name (Jan) as a cell reference?
I would like to be able to type the first three letters of the month in C1, so that the formulas automatically update to look up the data for the sheet specified in C1. Can this be done?
I have a line of code that returns a run-time error 1004 whenever it is passed through. All I am trying to do is copy and paste. I am missing some glaring error? (It is only a selection of the code to highlight the part I am having issues with. "maxdate" and "d" have been set)
VB: Dim ws, ws1 As WorkSheets Set ws = ThisWorkbook.Sheets("Data") Set ws1 = ThisWorkbook.Sheets("Target")
I have a loop function that goes through a list of employees and i want to move their name to a different sheet(monday, tuesday...) so that I know who is in on what day. is there a simple way to have it add the next name to the bottom of the list?
I have contacts list that I made and want to take it to the next level, but don't know how. See attached.
The goal: 1. Highlight an entire row of someone's contact info (i.e. row 9) 2. Click the "ORDER FORM" text box to activate a macro that populates specific cells on the 'order form' sheet with the customer's contact information from the highlighted row. The information I want copied over is in red on the order form sheet. THAT'S IT!! (Oh, and 'paste value' the current date from H5 so that the date is static on the order sheet
If possible, I would like to have an error check in case no row is selected prior to clicking on the text box that pops up the note "Please highlight a contact row before clicking the 'Order Form' button."
I have a workbook called "Staff Planner". I need to check Columns C to I in Sheet "Plan" to firstly find text in a red font, and then copy this value to Sheet "Useage" in Column K. (So, search C1:I1, find red font, copy contents of cell, paste value to K1). I then need to repeat this with blue font/value to H1, green font/value to E1, and finally pink font/value to B1
I am trying to reference a cell in a different sheet and am having a hard time getting the formula to return correctly. The IF statement is currently written like this,
I have a master sheet where users can change the name of 20 different sheets in the workbook by changing a cell value on the master sheet. Here is the code:
Code: Private Sub Worksheet_Change(ByVal Target As Range) Dim rCell As Range If Target.Cells.Count > 1 Then Exit Sub
[Code]....
The next step which I am having issues with is hiding the sheets. I want all sheets in the workbook to be hidden except for the master. I only want the other sheets to be visble if the user inputs a name on the master sheet.
So if the cell contents on the master sheet, say "B9", is blank, the sheet in the workbook that corresponds to that cell will remain hidden. If the user inputs anything, say "Sheet1", in cell "B9" on the master sheet, I want that sheet to become unhidden and to be named "Sheet1"
I have the following code to execute a connection to a DB and return the result of the SQL statement. I have a variable in the statement that I want to be able to change from user input in a single cell. Unfortunately I am unable to get this to work.
This fails and gives me a debug error, however if I declare the condition I want the code executes fine. This would mean having to update the code every time the condition changed or putting the whole query in a cell on the sheet which I do not want to do.
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
I have 2 inventory reports: what my store has and what my supplier has. I need to copy Tab1:K# to Tab2:T# provided that Tab1:A#'s contents match Tab2:A#'s contents. The A column represents the SKU of the item, but there is a difference in the amount of SKU's in each (my store sells ~6,000 items, supplier has ~10,000 items), so it's not as simple as sort by column A and copy pasting column K to column T.
For instance: On Tab 1, A2's value is [1], K2's value is [9.38]. On Tab 2, A70's value is [1], K70's value is blank, but I need it to be [9.38], to match Tab 1's respective SKU.
I almost thought I had it figured out with VLOOKUP, but I cant seem to get it right... It doesn't reference the correct number.
Screenshots for reference
First tab, from the wholesaler: [URL] Second tab, store's stock: [URL]
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
I get a report each day with a list of issues. the "group" that works the issue and the "priority". Based on these two factors, i need to do a double lookup (vlookup?) to another tab or file to match the priority and group and see what value should be brought back for each lines results. For example, if group1 had a prority3 issue, the lookup would find the value from the other sheet or file and bring back the value and put it at the end of the row where the formula is.
Attached are examples of the sheets. sheet1.jpg sheet2.PNG
i need a code that moves down a column and for every empty cell in the column the cell to the left is cleared and then it moves on to the next cell down. the column is not always the same and will start from a selected cell, and the column will contain no more than 5 rows
I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells.
For example:
Cell 'A1' starts out blank. Cell 'B1' has a drop down to select specific items. Cell 'C1' is the date of the order and is a locked cell. Cell 'D1' is the date of completion and is a locked cell. The worksheet is protected to prevent direct changes to cells C1 and D1.
If a user enters any information in A1, then C1 displays the current date. If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.