Populate Row Of Cells On One Sheet Depending On Value In One Cell On Other Sheet
Apr 27, 2014
with the attached spreadsheet?
I want to populate "selections" sheet with data from the previous sheet (it will be the date) depending on the value of a cell in column G. You will see I have already got the data from 27th April into my selections sheet but wondered if it can be done by a formula to save me copy and paste time?
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 administer an excel spread sheet which over the years I have added bits of code to which now generates our weekly roster sheets and our daily duty sheets. Sheet 1 is the weekly roster and sheets 2 to 8 are the daily sheets. If an employee is on leave or sick for the coming week the value S or L is entered in to Column B on each daily sheet and when the roster is processed for printing it strikes through the names of the employees for each day that they are off. What I would like to do is then Highlight the name on sheet 1 the weekly roster sheet so at a glance we can see on the main week sheet any issues we may have for the next week. What it needs to do is if the cell in column B contains S or L on sheets 2 to 8 the name which is in column C on these sheets needs to be matched to the name in column D on sheet 1 and this cell highlighted.
I have a Workbook that I use to log invoices and credit notes and it is password protected so that the cells that contain formulas are locked and therefore not selectable.
What I wanted to know is. Is it possible to lock other cells depending on what values are in certain cells?
Let me explain. I have Columns G & H which contain the heading 'Credit Note Number' & 'Credit Note Amount'. Now in columns I & J I have the heading 'Invoice Number' & 'Invoice Amount' respectively.
What I have done is to make a condional formatting so that If I put a value in cells in column G it will change the colour of the adjacent cells in columns I & J, likewise If I fill in cells in column I the conditional formatting will change the colour of adjacent cells in coulumn G & H. This helps me to see whether a row contains a credit note or an invoice.
What I would ideally like is for a Macro that would automatically locks cells G & H in a particular row that I am currently working on if I enter data into the cell in column I. Likewise if I am entering data in column G in the row I am currently working on for a credit note, then lock columns I & J in the row I am currently in.
Any Ideas how this can be done? Also can locking and unlocking of cells be done in VBA while the sheet is protected or would the macro need to temporarily unprotect sheet, lock the cells then protect sheet again?
PS: I have uploaded a Jpeg of what my conditional formatting does to try and illustrate what it is I need.
i have a certain column that tells me if a client has withdrawn from the company, and the column just says "y" or "n" i want excel to automatically copy the whole row to another sheet if that cell is a "y" for yes the client withdrew...
I have successfully used the course booking form as a base for an input form in an accounting workbook that has several pages. (Thanks to Roy and others for the info; the course booking form on [url]is the best explained piece I have found on the net)
What I would like to do is use a range of cells to populate the combobox so that a user can make a choice from the account descriptions (from a sheet named Intro, b63:99) and have the form return the account number (Intro, a63:a99) to the relevant cell elsewhere in the workbook. The user would then have meaningful descriptions rather than numbers to choose from.
I run this macro that populates 7 cells based on data in another sheet. It seems that every sheet that have formulas that point to this sheet are being stuck in in the screen updating somehow?
Basically when I press F9 to calculate or change any cell that makes the sheet calculate it has like a screen burn in of all the sheets that are being calculated. I have been able to use this as a workaround:
Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Application.ScreenUpdating = True End Sub
But I shouldn't have to do this and it slows it down as it runs through this specific code about 11 times for each sheet that is re-calculating. Something is getting stuck in memory or something that seems to be causing this issue. This is the code I am using and I am not selecting any cells or sheets, but it appears that when I walk through the code that an image of the sheet comes up on the screen? The code in red seems to be what is causing the issues, but I do not know why? I can pass the 2 workbooks along to someone so they can see the behavior if they would like? Just let me know.
Sub populateEmployeeData() Dim srcWorkbook As Workbook Dim foundEmployee As Range Dim srcWorkbookName As String srcWorkbookName = "XIP_Employee_Data_" & Left(ActiveSheet.Range("B9").Value, 31) & "_" & Format(ActiveSheet.Range("B10").Value, "MMMDDYYYY") & ".xlsx" If IsWorkBookOpen(srcWorkbookName) Then..........................
Sheet 1 contains a column titled "name" and 6 columns to the right of name titled "sat", "sun", "mon"........ the col titled name is not the first col in the work sheet.
In each row I enter the guest name under the name col and I enter a room number (example: 1A, 1B, 2A, etc...) in the column to the right that coresponds with the day the guest will be staying.
Name Sat Sun Mon Tue Wed Thu Fri
Mr. Smith 1A 1A 1A 1A Mr. Jones 3B 3B 3B Ms. Tiller 4A 4A 4A 4A 4A 4A 4A
Sheet 2 is in the same workbook and looks something like this:
Room # Sat Sun Mon Tue Wed Thu Fri 1A 1B 2A 2B 3A 3B
I want it to look at sheet 1 and populate the date columns with the guest name from sheet 1
I tried this formula =IF('Sheet1'!G:G="1A",'Sheet1!$F:$F,"Available") It seems to work on the first row but I have problems with any rows below that. Basically it acts like there is no data in the rows below.... I think the fact that the names on sheet 1 are not alphabetical is creating part of my problem? I tried a lookup formula but it seems like it has to be the first column in the worksheet and it has to be alphabetical to work like that.
I currently written the following formula, but always have issues when adding additional depending on selection.
D8 is the selection ( Validation ) on PERF EXP FORM Tab, Once you enter the selection... you then go to the Accounts per hour Worksheet =IF('Perf Exp Form'!$D$8="Test1 14-18 station",IF(('10220DBHE'!B9=A6),IF('10220DBHE'!C9 > 11000,11000,'10220DBHE'!C9),"N/A"),"N/A")
Perf Exp Form Perf Exp Form *ABCDEFGHIJKLMNOPQ4 *1. GENERAL INFORMATION****************5Customer***Date / Name(s)*6ApplicationSheet Size1UP/2UPOrientationSOG/EOGFold TypeFold Plate# CH***78 1/2 x 142 UPPortraitSOGLetter/ZHeavy1***8Machine / model**Enduro 4-6 station9ConfigurationCHANNEL 1CHANNEL 2CHANNEL 310590-4720-4910 Dual DeckNONENONE11Date / Name(s)*** Excel tables to the web >> Excel Jeanie HTML 4 ......
In this attached example, this is a portion of a actual work procedure. The yellow highlighted cells are the same work element within the master procedure but one is for one type of machine while the other is for same model but added options. This is a pattern throughout the entire worksheet. Basically 1 model with several different types of options. I don't want to have unnecessary elements in a work procedure.
So what I am trying to accomplish, I want to create a userform or dashboard which will allow selection of model #, model type #, then based off those selections have excel populate a worksheet with all correct procedures based off model type choice.
In this example there is 6 model variations, all share same basic procedure, but all have their own work procedure based on options, so my above approach will allow me to make changes to the master procedure only instead of changing 6 procedures.
Using macro's on Excel 2010. What I'm trying to do is create a macro that will copy a range of cells from one sheet to another depending on a option being selected from a drop down box. I've tried to use formulas but without success. sheet 1 contains a list of approx 20 people with rows containing sales figures per week.
Is it possible to have a macro that will copy the rows to sheet2 depending on the dropdown? The drop down has already been setup with people's names
E.g. if sheet 1, cell a1 (with data validation setup) dropdown contains "mr smith", copy sheet2 row A1:A9 to sheet 1 cell a2 or if sheet 1, cell a1 dropdown contains "mr cooper", copy sheet2 row B1:B9 to sheet1 cell a2 etc... for each name in the dropdown
The idea is so that i select a dropdown and it copies the sales figures that match the dropdown name, if i then select another dropdown, the corresponding figures are copied to the same place.
i have a sheet called 'sample database'. it consists of 56 columns, each with a specific title in row 1. i.e. name, surname, mobile_number, and so on. from row 2 onwards the data has been populated for roughly 200 rows.
i have another 'Capture Sheet' which has the same titles as 'Sample database' except it's in a different format. its a printable form that is given to new employees when they start. once they have completed it it gets captured into the 'Sample Database' sheet.
my wifes boss now wants 1 new sheet for each row in the 'sample database' sheet based on the format/layout of the 'capture form'. how can i get the 'capture form' to auto populate the data from the 'sample database' sheet and create a new sheet for each row? i know this is possible i just dont know how to do it.
I am trying to auto populate one spreadsheet from a master sheet
What I am trying to do: I have a master sheet where information on 25 countries is entered. Sheet has multiple columns for data entry I have 25 additional identical spreadsheets that correspond to one of the 25 countries so can drill down to country specifics
From the master - is it possible to select a country from a drop down menu so when I start entering data on the master it auto populates the corresponding sheet by the country selected.
I have a master sheet that contains all data relating to a particular Agent like Agent Code(Primary Key), Name, Bank details etc so on and so forth. Every month I have to make a new sheet which contains only a few of the columns from the master sheet. I have to manually select and copy each column for the specific agents to whom I have to make the payments. Now, I wanted to know..if there is any way in which I can enter only the Agent Code in the new sheet and it will look the code up in the master and copy the values of the relevant fields from the master to the new sheet.
I have a spreadsheet (Sheet A) with various data and checkboxes that I want to populate based on data in another spreadsheet (Sheet B). Sheet B has various fields with yes/no answers in the cells. I need to take this data and check varius check boxes in Sheet A based on the answers. I think this is possible if I write a macro to sweep through Sheet A to populate everything, but I was wondering if there is an easier way?
I would think there would be a way to set the initial state of a checkbox based on the value of a cell, but I can't seem to find a way to do it.
I have a userform in my workbook with a button to access it on twenty different sheets in the workbook. Currently the userform will only populate the sheet titled "Blank1". How can I change it so it will populate the active sheet without naming it? So no matter what sheet I'm on when I hit the submit button the userform will populate only the sheet I selected the button on? See macro below.
Code: Private Sub OKButton_Click() Dim NextRow As Long Sheets("Blank1").Activate
I am working to make an automatic result card in which excel automatically gets data of every student which is present in sheet within the workbook by simply writing roll number of the student in a cell. and i have done it easily by Vlookup and index functions but the only problem i am facing is, that i want to show the picture of each student by roll no. pictures are in one folder on my hard drive, now i want that as the roll number of the student is written the result excel should display the picture (from the folder, each picture named by roll)of the student. for example i write 1234 in roll no cell and want to get and show the picture no 1234 in another cell. (from the folder)
I'm attempting to pull the same cell ($a$1) from multiple sheets into one summary sheet, populating a column in a table.
I though I might accomplish this by putting an array formula in the desired column of my summary sheet that was similar to the following, to pull cell A1 from every sheet between the Frst Sheet and the Last Sheet.
This gives me #REF errors.
The values in $a$1 on each sheet are Text (addresses)
Anyway I can do this without Macros? The number of sheets between the First and Last will vary at any given time.
I want to do a loop where you can copy say A3 worksheet 1 then add another sheet naming the work sheet "A3" then copying A3 worksheet 1 to A1 "A3". After that looping to A4 to a new work sheet naming the work sheet "A4"copying the value to A1 "A4", etc...
Is there a simply way of doing this loop? I can probably fit my other coding into the structure.
I'm need to add a droplist with periods 1,2,3,4 etc... that references a cell then populates that same reference in a cell raange. Example: cell E1 contains the droplist with periods 1-4 as choices to select from, when you select period 1 it populates the dates jan 15 in cells E4:E7, if you select period 2 it populates dates jan 31, etc. how to add the droplist, how to expand the list, show the formula and how to expand it as I will have more data than this short example.
I'm trying to write very simple code that copies totals from one worksheet and pastes to a different worksheet. Normally, it is a one cell to one cell copy/paste. For instance, I want to copy the total found in cell "C4" in Worksheet "BalanceSheet" and paste it into cell "C14" in Worksheet "SpreadFinancials." I'm able to do this fine, and the code below is what I've been using.
Occasionally though I'm needing to sum up 2 cells in Worksheet "BalanceSheet" and only copy/paste the summed total into one cell in Worksheet "SpreadFinancials". For instance, I want to sum the totals found in cell C8 and C9 in Worksheet "BalanceSheet" and then paste that summed total into cell "C8" in Worksheet "SpreadFinancials". I'm looking for simple code that does this. Here's an example of what I've been doing so far for one cell to one cell copy/pastes:
Sub PopulateFinancials() Sheets("BalanceSheet").Range("C4").Copy Sheets("SpreadFinancials").Range("C14") Sheets("BalanceSheet").Range("D4").Copy Sheets("SpreadFinancials").Range("E14") Sheets("BalanceSheet").Range("E4").Copy Sheets("SpreadFinancials").Range("G14") End Sub