I have attached an excel sheet that takes 2 inputs in cells A2 and A3, sums them in cell A6, and then writes the outputs in cells A9 onward, recording the new sum as changes are made to cells A2 and A3.
Below the last output in the list (Cells A9 to ...) I want to have a Sum of the above outputs. In other words, I want a sum of the previous outputs, and I want it below the final output, moving and recalculating each time another change is made to A2 or A3. How can I do this?
i have csv data which has splits cells due to commas between numbers, and then moves data further to the right, is there a way to fix this, so for example this is what some cells look like
I know how to make a dynamic chart that shows only the last X number of rows. However, I'm looking to do something slightly different.
I have columns A and B both with 3000 rows of values. I would like to make a bar graph. Some of the rows are NA(), some have the data that I would like to graph. The data I would like to graph is always in a group, say from rows 100 - 200, but this could change in location from rows 95 - 195 instead, and could also change in number of rows to be rows 95 - 150 (ie. 55 rows rather than 100 rows). I would like my bar graph to be able to search Columns A and B and pick out the section with data (ie. not NA()) and graph it. I had introduced the NA() hoping that I could just put the entire range in the chart and it would pick out the numbers, but I gather this only works when using a scatter graph which just won't work for my needs.
In sheet 3 they are combined via reference in order for me to do calculation in the input - They are combined in sheet3 as the columns are not identical.
Because the data-tables will be updated via sharepoint, i have to link to cells beneath the data-tables as i expect these will be fill out when the tables update.
My problem is that when the tables update, i dont see the updates in my "combination" sheet. it appears the the data tables add a new line, which moves my cell reference 1 line down.
Ex: Had a reference in Sheet1'E3 - after the data table updates the reference is now Sheet1'E4.
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
I have a workbook that has a dynamic number of tabs. Every day the report is updated, it will create a tab for yesterday's date. I need to somehow create a summary page that will add the most current tab name (which is yesterday's date) to column B and several cells from row 7 to the appropriate row on the summary page each time I add a new tab. I have attached the spreadsheet in question. I have added manual references to the fields I need to use in the summary on the tab MTD (2) If I am able to get this to work it will replace MTD.
I have a workbook that starts the beginning of the month by entering daily hours in cell D3 (Day 1 in cell D3, day 2 in cell E3, day 3 in cell F3 etc). Column B has several codes, but the one code the macro looks at when going down the current day is a letter "W" for "Worked". Therefore, Rows 4, 5, 9, 12, 56 (examples only - it changes daily) etc. could have a "W" and when the macro is ran, evertime it sees a "W" it includes the hours found in row 3 of the applicable day i.e. starting on row 4 the formula is =if($B4=D$3,D$3,""). This copies to the bottom row using the shortcut (Ctrl + Down Arrow) to find the bottom. What I have done is entered Zeros all the way down and changed Zero Values so they don't show.
Where I get in trouble is if a zero is removed, the shortcut stops at that break thinking that's the bottom. The bottom moves as we remove equipment out of the line up or add new equipment. What I am trying to do is have Excel figure out where the bottom row is for each daily calculation when the macro runs down the daily column.
I've a master sheet called records. It consolidates all the data from various worksheets. Overtime, the lists gets very long. So I'm proposing this. I want a macro which - Shift all the data yearly into a new directory at
G:MMTPMVarious StockArchive. When it saves at this directory it should bear the year as the folder name. The dates are retrieved from column H. - it should be operated at the every end of the year.
I have a Workbook (only 1 sheet) that is filled out each month and consists of sales and revenue data. I need to copy the total sales and total revenue and paste them to another sheet. The only problem is that it is never consistent as to which cell the totals will be located in. They will always be in the same column, but almost never in the same row.
For example, one month there will be 8 individual sales and the total will be in Cell F13, but the next month will be F20. THe same needs to be done with Total Revenue (which is always one cell right of the Sales Total) I need to copy and paste the cell with Total in it (in this case F13) and do the same for each month after. This is not difficult to do in Excel, but i need some code to include this process in a macro.
I m adding colors in excel sheet where ever ther is value added in the cell and delete the same value of the cell as this sheet i have to change every month end. i have tried this but not working when i want to delete the same Pivot Tables
In design mode on my UserForm, I have an object on top of all other objects (it's hidden until a button is clicked). That's how I designed it and it was working fine when I ran the form. Now, when I run the form and click the button, the object appears at the bottom below all other objects. I've tried closing Excel & re-opening it, setting the ZOrder in design mode and in the CommandButton code--nothing works. If I did do something to cause this--I have no idea what it was. I've been moving and re-sizing some objects, but not adjusting the ZOrder.
I want to perform a COUNTIF in a column where data is added regularly. The COUNTIF range is to be the last 100 rows of data: so if yesterday that range was a2:a102 then today it should be a3:a103, tomorrow it will be a4:a104 and so on.
How can I get COUNTIF to change the range as new data is added? I can generate a string with the changing range ("a2:a102", "a3:a103", and so on) but how do I put that into COUNTIF?
I have a graph that is showing the date on "Y" axis and a value on "X" axis, when I add a new date and value to my data the graph does not update, it just shows the data when I first created the graph.
How do I get the graph to display the new data I entered ?
I created a line graph by clicking on the "A" in column "A" (Thats where my date is) then clicking on the "B" in column "B" (Thats where my values are) and clicking the create graph button then clicked finished.
I have attached a copy of the chart and data. My chart starts on the bottom. I had to take out a lot of data but beneath the data shown is much more data..
I want my chart to automatically update when new data is added. Here are the issues. I have several lines of headers and below that my data. Then below my data I have other data that I don't want displayed in the chart (its not shown here because of size limitation of attachmnet).
The new row will be added each time right below the header, right above the most recent data given. So a row will be added above row 8!
I have a pivot table I am try to update. The table references another tab where my data sheet exists. If I add data to the datasheet how can I get my pivot table to recognize the additional information.
I am running the below code to basically apply a defined list to a cell containing the string "WORKDAMNIT". This list is used as validation. I am running into a HUGE problem where once I apply the validation on about 17,000 cells and try to save the spreadsheet I get an error saying that "Excel could not save all the data and formatting you recently added to...". Thus, the IF loop basically does a save of records everytime I hit about 1000 records. I can even see the file being saved. However, whenever I close the spreadsheet it asks me to SAVE!! If I try to save it I get the same error and if I dont NOTHING gets saved.
Sub Valid()
Dim listCount As Long 'counter Dim cellCount As Long 'cell Counter for save function Dim foundCell As Range 'found cell in sheet Find Dim foundList As Range 'found cell in sheet list Dim fwb As Workbook 'workbook value Dim fsheet1 As String 'find sheet Dim fsheet2 As String 'list sheet Dim strMatchCol As Integer 'address value of column number Dim col As String 'actual value of column string Dim strFind As String 'The string we are searching for (eg. "blah blah")
I am using a form textbox on a worksheet. I have the textbox positioned where I need it to be but when I print the worksheet the textbox moves out of position.
Scenario; I have 3rd party software that pulls reports in .csv files. I want to summarise this data into another spread sheet. Ideally I would like to save these files into a folder on the network and my summary workbook simply looks for this folder and is able to find data within all Excel files saved in there (new files will be added on a monthly basis).
Once it has found the folder there will be 2 pieces of data to evaluate; Cell $B$1; this will be the name of a course (e.g. health & safety, money laundering etc.) Column $D; this will contain the score achieved by multiple learners.
The summary spreadsheet then averages all of the scores for the different courses(in seperate cells)
The idea is that the .csv files can be pulled and saved into said folder and eliminates the need to manually input this data into the summary spreadsheet.
This formula calculates the average mailing return rate of all the mailings added together. J15 is currently white text to hide it from the end user.
Everytime I add a new row of data I need the formula to update itself automatically. So for example, the next time I add a row of data the above formula has to change to
"=J15/11"
Or if I were to add three rows of data the formula would have been
I have several ComboBoxes & ListBoxes on a Userform that populate by referring to Dynamic Named Range in the RowSource property. Also on the Userform is an area to edit/add/delete items in these named ranges. If I Add or Delete an item, the Named Range does not automatically resize--or at least the Menus don't "resize" accordingly--unless I unload the UserForm and load it back up. Is there a way to refresh these Comboboxes without reloading the UserForm?
I am trying to do a running total in one column with data added from the columnn before it. But I dont want the last amount in the running total to continue down the page I would like it to be either blank or zero amounts. Also if there is a zero amount in the first column the running total should show the amount previous.
I have a single figure and as the figure increases and moves through thresholds the pay changes. e.g.
Number of sales payable = 15.66 Level 1: 1-10 pays 100 for each that falls in this threshold Level 2:11-15 pays 150 for each that falls in this threshold Level 3: 16-20 pays 200 for each that falls in this threshold Level 4: 21+ pays 250 for all above this
I want to work the amount payable with a formula.
It should be $1849
Is there a formula that can calcualte this without me manually figuring out how many in each threshold.
I have 2 Listbox in Sheet3 & lists in Sheet1 & Sheet2 in columns A:A. When I delete columns in this Sheets or insert new columns List moves forward i.e. But I need The ListBox show allways List in the Columns A:A.
consolidating data from 3 worksheets which contain same data fields but each representing 1 brand. I like to have a summary/mastersheet to contain data from all 3 worksheets and then when the data in each worksheet is added or updated, the summary worksheet should reflect the changes (adding new data to the last row).
I want to pull data from my excel file (using VBA) into Ms Word. I created a template in Word and wrote a macro to do this, it worked, however, anytime a new row is inserted or deleted in my excel spreadsheet my macro produces wrong results in my Ms Word template because the cell position has shifted, thereby producing the wrong result. How do i make it that my result remain the same when new row is added to my spreadsheet.
Example: Excel row1: vicky 528 row2: sam 532 row3: john 092 row4: Own 211 word template: 092
This is what happen next:
Excel (New row added) Macro: ThisDocument.ScreenedPatients.Caption = wb.Sheets("CSAs").Cells(16, 1)
row1: pat 542 row2: vicky 528 row3: sam 532 row4: john 092 row5: Own 211 resulting Word template(running macro): word template: 532
But I want John to automatically go on the Word template without going into macro to change it all the time when a new row is added.
I have created an Excel spreadsheet in 2007. When a friend opens the workbook in Excel 2013, It does not let him add data to the cells. ( just one column which is the "date" column) The worksheet is not locked or protected.
When I right-click on the column and goto format cells, protection, the box is ticked, but the note says this doesn't take effect unless the sheet is protected witch it isnt.
