Update Table On Another Sheet With Information On ACTIVE SHEET Based On Column Search?
May 14, 2014
What I have is a sheet that is copied periodically from some source sheet, and on this sheet is a table. This sheet is called "Onsite Checklist Template" and it's table is titled "Checklist". I also have another sheet called "Loggers and Initial Notes" which has a tabled titled "Record", and then finally a title sheet call "Proj Details".
To clear this intro up - The sheets, in their order, is: "Proj Details", "Loggers and Initial Notes", "Onsite Checklist Template". The tables: "Record" on "Loggers..." and "Checklist" on "Onsite..."
When the user wants to make a new site visit, he/she fills in the requested date and then selects a button on "Proj...". When this button is selected, it copies the table data on "Record" and puts it on "Checklist", then inserts a new worksheet tab, always in the 3rd position (the title is based on the site visit date in which the user entered), which is a copy of "Onsite...". Now we have another sheet with a table called "Checklist1", and upon another new site visit, there will be another worksheet with "Checklist2", and so on.
On the "Onsite..." worksheet, there is a button on it which also gets copied with the worksheet so that every new worksheet has this copied "Checklist ???" and this button. I'm looking for a macro that, when the button is selected, will bounce the active sheet's table "Checklist ???" off of "Record" and make changes as needed.
"Checklist ???" data range is B11:M20 (the header is on row 10); "Record" data range is B29:Q78 (the header is on row 28); Column headers are titled the same, just that "Record" has 4 extra columns, 3 in the middle and 1 on the end. "Checklist ???" columns 1-12 to "Record" columns 1-7, 10-12, 14-15. The search criteria is the 4th column in both tables ("Trk #").
I need the macro to do the following:If it finds a match, then update "Record" as needed with data from "Checklist ???", changing whatever cell is different in the row that contains the matching "Trk #", so long as the cell on "Checklist ???" is populated (i.e, if a cell on the target row of "Record" has a value, but it's blank on "Checklist ???", then "Record" wins; if it's blank on "Record", but populated on "Checklist ???", the Checklist wins. If both populated but different, then Checklist wins.If a "Trk #" exists on "Checklist ???" but is not on "Record" then add the line to "Record" (the 1st empty row, table size remains)I see no need for any "delete" at this time.
My concerns: The last column on record (column #16) is the filtering column for the worksheet copy event and needs to be left alone (it's formulated to produce a "Yes" or "No")I would like the ability to adjust table sizes if needed without modifying the macroThe table rows on "Checklist ???" will not be changed, deleted, or altered in any way by the macro.
Here's another question for you excel junkies to solve, while I try to understand what y'all are doing. I have two worksheets named "MASTER" and "BEDS". What I need to do, in sheet "MASTER" is go down column ("K:K"). "K:K" has unique data in it. then for each row...
"X" .value = offset minus one "Y".value = no offset
now I need to go to sheet "BEDS" and find "Y" in column "A:A" then
from "Y" offset + 3.value = "TRUE" "Y" offset +4.value = "X"
there should only be one instance of "Y" in column "A:A"..............
Create a macro button in 'Spreadsheet 2' that searches 'Spreadsheet 1' for updated information specific to a certain criteria and adds it to a new row in 'Spreadsheet 2.'
There are three sheets
Prospects (where all original data is entered)
Actions -Bob (Bob's new Prospects are added to this sheet)
Actions -Frank (Frank's new Prospects are added to this sheet)
So in this example Row 6 in Prospects (Constituents, Rating, Manager and Solicitor would be added to ACTION - Bob's sheet on Row 5 and Row 8 in Prospects (Constituents, its Rating, Manager and Solicitor would be added to ACTIONS - Frank sheet on Row 5
Here is the example spreadsheet - Prospects and Actions.xlsx
I've got a bit of a quandary here that my novice programming skills can't seem to solve.
Here is the scenario. I work for a photographic library. We have a spreadsheet that contains 5 or so columns, and a couple of thousand rows. Essentially each row contains an imagine code (the number we use to file away the imagine, ie. US_NY_NYC_1 ) in the first cell, and the image information in the following cells (ie metadata, keywords, photographer, date, etc).
all the keyword, photographer, metadata, and location information can only be tied to the one image code whose cell begins the row.
Now, here is my dilemma. This sheet (lets call it the master sheet) contains all the images we have in a given batch. However, when a client orders images, we will send them most images in that batch, but not all, and we may send a few additional. So I have a separate sheet that contains only those image codes that the client requested. What I need to do is essentially take a code from the second sheet, find that same code in the master sheet, and copy over all the cells associated with that code's row. If the number does not exist in the master sheet, simply leave that row with only the code in the first cell, and the rest blank, and move on.
so for example, in my second sheet lets say I have the following A_B_C . I would like for excel to find that same code in the master sheet, and then copy over the metadata, photographer, date, etc, associated with that code, and paste it into the second sheet, so that that row in the second sheet now looks identical to the row with the same image code in the master sheet . If A_B_C does not exist in the master spreadsheet, then I would like excel to simply leave that code in its own row, leave the rest of the row blank, and move on to the next code.
I have a workbook which has links to another excel workbook (source). This workbook has many sheets -all exact copy- with different names. I changed startup prompt (edit->links) to "dont display the alert and dont update automatic links". Then I recorded a macro for updating links (from source) and assigned this macro to a button. What I want to do is when I click this update button I want links to be updated only on the active sheet that Im on. My problem is, when I press this button, it updates all links including other sheets (basically whole workbook).
My current macro is (I dont know coding but I tried changing ActiveWorkbook to ActiveSheet but it didnt work):
Sub update() ActiveWorkbook.UpdateLink Name:= _ "C:Documents and SettingsAlperDesktopExcelsource.xls", Type _ :=xlExcelLinks End Sub
So, I'm setting up an accounting book. There's a master sheet that needs to pull from the other sheets that are made. The problem is, with each new sheet that is made, I have to update the formulas on the master. I would like that if I made a new sheet, the master would automatically pull from it.
Example:
Master pulls renter fee info from May 3rd, May 7th
I create sheet (May 15th)
Master pulls renter info from May 3rd, May 7th, May 15th
Here is an example of my spreadsheet: Excel example.xlsx
I would like to get all the bid/ask quotes for the different currency pairs in sheet 2 into sheet 1 sorted by the right date and time.
I need a formula which recognize the time + the currency from sheet 1 and search for it in sheet 2 then brings the right bid/ask quote back into sheet 1.
I have a sheet1 with following column name Ab,Bb,Cb,Db,Eb,Fb and sheet two contains Ab,Xc,Eb,Sv,Db,Fb,Gm,Cb,Hb these headings are on 7 line of both excel sheet.
Now I want to check each cell under column name Ab in Sheet1 and compare it with all the cells under column name Ab in Sheet2. If both matches then update Cb,Db,Eb,Fb of Sheet1 to the corresponding columns in sheet2 for that column name.
I've been working on a macro that makes copies of a template sheet based on a table in my Opps sheet. If column B isn't empty, make a copy of the template sheet, rename it to Opps column A, and then hyperlink column A's current A.row to the newly copied and renamed sheet.
I'm not sure what is wrong exactly, it keeps making duplicate Template(x) and stops renaming them, and the hyperlinks are not working. -This is my first go at VBA hyperlinks to internal workbook sheets
I've got 3 problems I've been trying to solve for the last 7 hours, I think they are all relatively simple to those who know what they are doing but I've exhausted my excel for dummies and every formula I have taken from this siteand tried to adapt. Obviously I'll post each problem on a different post, so here's problem one. The sample is attached. It will consist of about 35 sheets, each documenting the hire of a Motability scooter or wheelchair.
On each sheet column P will identify any review dates. What I would like is: On the Master page in Column C next to the relevant sheet name is for it to display any 'Review' that is flagged up, so that the user can have an overview. There will only ever be one 'Review' per sheet, I can only get the master page to show it by referencing it to one cell, I can't work out how to search the whole column.
Is there a way to edit my database located on sheet 1 using the advanced filter (output) on sheet 2?
I have a gigantic database and I want to filter it down to the rows I need to edit.. so I used advanced filter to extract the rows I need on to another sheet. But if I edit the rows on sheet 2 how do I makes those changes reflect on sheet 1 (the full database)
I need a Macro which pulls the data from different sheets of excel (which is not formatted properly) to Main Sheet. Also some of the columns will not have the same names, so macro should handle this exception as well.
I searched but didn't find exactly what I needed. I have a workbook with 31 sheets. It is a price guide with each category on a separate worksheet. I figured out how to list the sheets on a separate tab. What my client wants is the ability to:
1. select certain categories for printing, the ToC, Cover and backcover pages have to print in every case
2. the ToC has to change depending on the sheets selected.
I'd rather write some code and give him an an easy command button rather then teaching him how to select non-concurrent sheets and printing only active sheets.
What I'm really stuck on is the updating of the ToC with active sheets only (category and starting page which changes depending on pages selected).
I have a worksheet, with a "cost" column, and a date column. I have another sheet with dates pre-written, and a cost column as well. I'd like to know how to make the cost from worksheet 1 go to worksheet 2 under cost, but also add up any costs that are on the same date.
I am putting together a register of information and it is broken up over a number of sheets i.e. LOG1, LOG2 etc.
I have a master sheet in which I want information to automatically populate based on whether a certain column has the words yes or no.
Ie Column I in LOG1 or LOG2 etc can either be yes or no. Is there a way to populate the Master sheet only with the information of a row in which column I is yes?
I know it would have to be a formula in the master sheet which checks all the sheets and then somehow populates the master sheet downwards if anything is yes but then removes it once that yes becomes a no....
I have filled up my ComboBox1 with the sheets I want to add information to. My problem now is how to place the info in the correct sheet when I click CommandButton1.
I've been working on this for a few hours now. I got this to work on the first code I found but it didn't work afterward. I just deleted the whole thing and started again.
I want to make things easier for myself when filling in my worksheets by having certain cells autofill based on a product number.
So I have my main worksheet and I have my product information sheet. When I type in the product number in cell 13C, I want it to auto fill cell 13B with the product name which is located in collumn A on the product info sheet, but dependent upon that product number. Collumn B in my product info sheet has all of the product numbers. From there I would need 13G on my main worksheet to fill in with Collumn D from the product sheet, but again dependent upon that product number.
There are more that need to fill yet, but I can figure out what to do once I have a starting point with the above I believe.
a. actual data in columns is approx 150 items (that is why i need to hide/unhide columns) b. there are about 120 persons whom above items are issued depending on expiry of life of item i.e. issue date plus number of months mentioned in row 3. for example if coat was issued to John on 5th Jan then it's life expires on 5th May and so on.
Now what i want
1. by clicking hide checkbox only that specific column should hide or if i click Hide All check box all column in range should hide.
2. as i enter issue date expiry of that item should automatically update on corresponding month sheet i.e for above example 5th May should automatically get entered in sheet May.
I have entered sample data manually in months sheet.
I have been able to produce a column that gives me key date information. I might have many empty cells in that column before it hits another date. I need to get this data over to another sheet and place it in a row.
I am trying to get it to look like this:
B2(value), C2(value),D2(blank),E2(Value),F2(Value),G2(blank) etc depending on how many more elements are found in the sheet.
I am able to find topics that allow me to copy a range of data but not one that will allow me to copy a range then skip a cell.
i want to update values fon an excel sheet on a month by month basis. So i have jan to dec folders and excel sheets under each folder , i want to update my monthly forecast sheet based on the numbers in the folder I want to just change the cell in my monthly forecast sheet to Jan or Feb and the values should be pulled from the closed excel files in the monthly folders. I tried concatenation but it gave me #REF .. Is there a way to do it other than using "pull" ??
I want to enter a number in cell, select a value from a drop-down list, and have another sheet updated with that info. For example, on sheet1 cell A1 = $900, A2 = Western Region (drop-down list). Sheet2 would have a list of values that would update based on values entered in sheet1:A1 and sheet1:A2. Is this possible using formulas or do I need to use VBA?
I am trying to create a formula that will change the cell color in a range of cells in the row. I want the color to change based on the information in a particular cell appearing on a different sheet. What sheet the information is on determines the color the cells change to.
Example: I have 4 sheets I am working with. We will call them A; B; C; and D. I would like the color of cells A5-I5 on sheet D to change to red when the information in cell A5 from Sheet D shows up in any cell in column A on sheet A. If the information from cell A5 sheet D appears in any cell in column A from Sheet B then the color will be yellow, etc.
I'm trying to create a copy of the active sheet and then rename the new copied sheet to what's in cell O4, which is a formula (see below) and then paste value cell O4 in B3 of the copied sheet. However, when I run this macro it doesn't seem to like the second line where I am renaming the sheet (run time error '1004').
"O4" =DATE(YEAR($B$3),MONTH($B$3)+1,DAY($B$3))
Sub NewMonth()
ActiveSheet.Copy Before:=Sheets(Sheets.Count) ActiveSheet.Name = Range("O4").Value ActiveSheet.Range("O4").Copy ActiveSheet.Range("B3").PasteSpecial Paste:=xlPasteValues End Sub
I would like a macro that will go to a fixed sheet, copy the format, go back to the previous sheet and paste the format. My problems arise going back to the previously activated sheet rather than just a fixed sheet.
I've got a big file with a large dataset. One of the sheets works as a screening for the data, so everytime I populate an ID number in say cell C3, the rest of the sheet updates as per the formulas I have in place. However, I need to write a code that takes ID numbers one by one from a sheet, populates them into my screening sheet, updates the sheet and saves it.
I have a worksheet designed to look like a form. That worksheet pulls data from an "Application Data" worksheet using INDEX and MATCH formulas. That part works great. The user enters an Application number on row 8 and it populates all of the other cells. In fact, in cell O7 (not pictured), I have it generating the row number the data came from (based on a MATCH function).
Here's my question--if they go in and change a value in one of the other cells, they should be able to press an "Update" button. That will then kick off a macro that goes to the "Application Data" sheet, finds the row that has the Application Number (from row 8) and updates the values based on what the user entered on this form.
How do I write the statement that selects the row based on the Application Number?
I am trying to create a summary sheet from the matrix to do further analysis. I want to pick out the welds done everyday with weld inches as you will see in the summary sheet. How can summary sheet be automatically updated when I enter the inspection date rather than copying and pasting? I can use vlookup to get the weld dia once I get the weld numbers on that date. I have attached the file.
