I have a macro written that pulls data off another workbook and places it on a sheet on the my main workbook. The macro is set to auto filter based on whats in Column 1. The code for this is below:
Now what I was wondering is there a way for it reference say cell I12 on sheet2 in the main workbook and pull that data instead when the macro is run via a button. The number listed is a date code so say we change it too "201503". I would like to pull all those dates instead of the ones for "201504".
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 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.
I have an update summary macro which takes certain cells from certain sheets and displays this data in a summary workbook.
One of the columns, called status runtime pulls cell J5 of each of the sheets which is a formula =TODAY()-J4 for example.
The problem I am having is that when I open the summary sheet from one day to the next, this particular column is not updating. To update the column I have to go into each individual sheet and save so that the TODAY formula updates the value of the cell, and then thus the summary sheet value.
Perhaps I should avoid putting the formula in each sheet and make a formula behind the summary sheet which takes the value in the status runtime column and subtracts TODAY() from it, then it would update as and when the summary sheet is opened.
I`m trying to work out the best way to propogate a cell's format accross an entire range. What I ultimately want is for a user to only have to change the text colour of one cell, and for the entire row to immediately change to the same colour with it. Even if this means always having to update for example the cell in column A to change the text colour accross that row.
In sheet 1, there are two columns, Category and Subgroup. The cell validation for Category is pulled from the category list on sheet 2. Basically, what I want to happen is depending on what is selected as the category, only the related sub-groups would be populated in the sub-group validation dropdown list. In other words, if Fruit is selected as the category, I only want Apple, Orange, Pear, Banana, etc to be in the validation drop down list for the sub group. If vegetable, then only Corn, Green Bean, Cucumber, and Peas should be shown.
I've included two options for the relationship list as I'm not tied to either style and not sure which would be easier to work with.
I am attempting to update a column of numeric values based on finding and validating record IDs in two different worksheets. So for example if in worksheet1 in column 'C' the ID is 1234ETC and in worksheet2 1234ETC is found then I want to update worksheet2 in the corresponding cell for that record ID in column 'J' with the information from worksheet 1. So far I have been using a routine like the following to go through and compare the ID's but now I need to update the other cells in column 'J' reveiw the following and see if there is a way to improve upon this.
Sub UpdateCommitmentAmtsFromImport() Application.ScreenUpdating = False Dim bottomC1 As Integer
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" ??
So I have been trying to plug away at this but am getting no where fast.
I have a set of job numbers on sheet 2 that is pulled from sheet 1, when the job numbers are pulled over it also pulls the due date. The problem I am having is that Sometimes the due date gets pushed out and writing the macro that will pull in the new date
I have not used an event macro before and am trying to one update a cell when changing a cell. I am basically copying a number to another cell that is an input for a calculation and then returning the calculated value back. How do I reference r69 in the code to start the event macro?
I have a macro that is set to clear a range of cells. If I make a change to the worksheet (such as adding a column) that moves those cells in the worksheet, the worksheet formulas update, but the macro cell references do not. How can I make my macro update in accordance with changes in the worksheet?
Let's say that in column A I have numbers,"Yes" and "No". I want in column B to have only the numbers from column A, in the same order without any empty ranges, and everytime I add in column A a new number, column B to update automatically with that number. Let's have an example:
A B Yes 12 12 13 No 10 13 No 10 Yes
And if I want to add in column A: A B Yes 12 12 13 No 10 13 25 No 15 10 Yes 25 15
So the column be will update automatically. I already tried =IFERROR(INDEX($A$1:$A$10,SMALL(IF(ISNUMBER($A$1:$A$10),ROW($A$1:$A$10)),ROWS(B$1:B1))-ROW($A$1)+1),") but using this many times get's my file very heavy and the excel is working slow.
I have a column C with different text in cells (item's title). Column D - relevant description for each of the items. 100+ rows.
Now, unfortunately, often a spreadsheet with items is updated with many new items. So I get a new spreadsheet with old and new items mixed. I need, somehow, to import descriptions of the old items (Column D of the old spreadsheet) to the new spreadsheet from old spreadsheet. So I want excel to look for old items in column A of the new spreadsheet and, once found, insert a description in the column B from old spreadsheet.
I have a basic formula =C17+'Asset Depreciation 2008 Onwards'!C24, and I want to copy it down just using the drag function. Problem is that the second reference range of cells are in rows and hence when I copy it down it doesn’t automatically update the cell references because it want to update them by column number instead of row number. IE I want it to display =C17+'Asset Depreciation 2008 Onwards'! D24, instead of C25. Do you know if there is any way of telling Excel that I want it to increase the column number by 1 every time, instead of the row number for this part of the formula?
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem ... .Fields.Item(1).value = activecell 'activecell value = "Joseph" If Not isempty(activecell.offset(0,1)) Then .Fields.Item(2).value = activecell.offset(0,1).value Else .Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works End If ... End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty ' or .Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
I have a customer spread sheet that uses a “Ranking” to establish the next “Contact Date” based on the “Ranking” and the “Last Contact Date”.
An example is – a “Customer” with an A “Ranking” would be contacted 21 days after the “Last Contact Date”, with a B “Ranking”, 28 days after the “Last Contact Date” etc.
Is there any way that the “Next Contact Date” can be automatically up dated when the “Ranking” is changed and there are multiple rankings (A, B,C, D etc)
So I want the SUM of the total from a certain range of dates to go into the Week Columns on the right. I'm gonna manually select the date ranges for each Week but how can I set up a formula to Sum the Totals at the right of the table based on the Date Column.
I have one "master" spreadsheet I utilize per month. Each month my "master" spreadsheet is updated twice a week (so, for a four week month I update my "master" spreadsheet a total of eight times).
To update my "master" spreadsheet I compare it to an "update" spreadsheet that contains the same column headings as my "master" spreadsheet.
What I need updated: I need to compare account numbers (column A) between the two spreadsheets, once I find a match I need to update each of the costs for the 5 products (columns B thru F) from the "update" spreadsheet to the "master" spreadsheet. I currently have a simple SUM(IF) statement that completes this for the first update.
I am lost at how I can have my "master" continually update for the next seven updates without losing each of the previous updates.Any help on this would be greatly appreciated.
I am attaching two sample spreadsheets - "master" and "update".
I have a userform I attached here with 3 combo boxes where a user can pick districts and schools, etc. When they click ok, the various tabs in this macro enabled workbook will "refresh" with the metrics for that entity)combination of picks from form) across the board. Each tab will have multiple pivot charts/tables so the filters will need to be addressed. I've been looking for code to kind of match this scenario. I've seen some that kind of in the ballpark, but due to the size and complexity, I suspect this will be extensive, right?
Attached simplified sheet gives background - but need to update the cells in the aggregate monthly table C44:C55 based on data in single cell above, and according to the correct month. Need do this without the previous months data changing.
Month is determined by cell E8 and data to be updated to the agg month range is to come from cell C15. The month date will change automatically as will the data in C15.
In Excel sheet1 Column G has Names(John,Augutus,Jennie ect..) In Coulmn H i need to daily update the Present or Absent status, once i update the status i need Coulmn I to automatically fetch the System name and Column J should update with Time and date..
I'm attempting to force excel to auto update the day of the week, and the date in a spreadsheet. The date isn't as important, since it can be hard coded. The only problem there is some months have 31 days, some 30, and another with 28. I've uploaded an image of the spreadsheet, and you can see in field A1 the date/year is input. I'm wanting to find a way to force the days/dates in fields 2E and 3E to update based on the month.
I have a spreadsheet with ~35k rows of data. This isn't too easy to explain I have the following data (example).
User Name Employee ID Data Level Jan 08 Feb 08 March 08 etc. Jo Bloggs 12345 A1 1 1 0 Jo Bloggs 12345 A2 1 1 0 Mark Jones 89101 A1 1 1 Mark Jones 89101 A2 0 1
For each user with data level A1 I wish to add an X if the same user has a corresponding data level of A2 so the result should be,
User Name Employee ID Data Level Jan 08 Feb 08 March 08 etc. Jo Bloggs 12345 A1 X X 0 Jo Bloggs 12345 A2 1 1 0 Mark Jones 89101 A1 1 X Mark Jones 89101 A2 0 1
I would really love a formula or macro to be in existance that would save me from trawling through all this data and going blind.
I need to update the master workbook with data from the invoices workbook using the following criteria.
In the master workbook column C has unique job numbers. In the invoice workbook column F has unique job numbers. Both worksheets are the first worksheet in both workbooks.
If the job number in the invoice workbook is already in the master workbook do nothing.
If the job number is in the master workbook but not the invoice workbook then delete the entire row from the master workbook.
If the job number is not in the master workbook add it to the master workbook.
To complicate matters the master workbook is shared. It is my understanding that shared workbooks can't contain the macros needed ? It would be preferable that the master sheet had them but if not ...
I am trying to use a row of cells as update cells, where the user inputs an amount into greyed out cells, which in turn updates Sheet1!$I$2:$I$11, from which it will update other worksheets that are currently in progress. but I do not know how to go about it. Is it possible?
For each amount the user enters (for each Code Number) a date will be displayed below the amount.
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.
