I have a spreadsheet with standard rows and columns - nothing elaborate. There is a section with a column for each month of the year, with 10 line items showing monthly totals for various business functions. What I would like to do, if possible, is ... instead of showing all 12 columns (making for a very long spreadsheet), I would like to show only 4 of the 12 month columns (in a window) and scroll through the months with a scroll bar.
I'm currently creating a workbook whereby I have (regrettably) highlighted entire columns and formatted these columns to show borders. The reason I did this was because drawing them in manually (I thought) would take much longer with the way the worksheet is laid out.
Obviously highlighting the entire column has meant that borders were drawn all the way down to the bottom row of the workbook, and so the vertical scroll bar cannot effectively be 'dragged' up and down as even a tiny movement will cause it to scroll right past the worksheet.
I have tried running a macro to clear contents and formatting for all the unused rows, but the scroll bar remains to be a pain.
I want the scroll bar to only scroll down to a few cells below the available work space like it should do.
I have a raw data in one file and then another file contains pivot table linked to the raw data. In the dashboard i need to show last 6 weeks trend which is linked to the pivot table. Currently i am manually unselecting previous week and selecting the latest week.
Ex: This week i select weeks 5, 6, 7, 8, 9, 10 from the pivot for the dashboard. When new data is updated next week, i want to select 6, 7, 8, 9, 10, 11. I cannot delete as i need the historical data.
I have a data set that tracks ingredients in chemical products. For example sake the chemical product is a mixed color, and the calculated ingredient is a primary color.
I need to calculate the quantity of two primary colors based on their percentage of the mixture.
Column A contains the mixed color names. Columns B and C contain the percentage of the primary in each mix.
I began tracking this data in January 2009. Each month I add 3 columns to the worksheet. The 1st column contains the monthly mixed color quantity used. The 2nd and 3rd columns contain the calculation of the two primary colors.
I need the formula to calculate the 12 month rolling total for each primary color. (For July 2014 I need to calculate July 2013 through June 2014)
I could swear I used to be able to cut and paste columns in Excel 2010, but for the past week I haven't been able to. When I click on a column and do a right click, "CUT" is greyed out. I can cut any section, but not a whole column.
On my "Order" sheet, I enter an account no. that looks up the company name, address, etc on my "Accounts" sheet. Below the address info on the "Accounts" sheet is a list of the salespersons with their email address.
After entering the account no., I want to enter the salespersons name and automatically look up their email address. I can make the lookup function work if I tell the formula which column the salesperson is located in, but am lost how to write the formula to locate the salesperson based upon the column no. that contains their company info.
F1 - contains the account no. entered on the order sheet.
Accounts!A1:Z100 - range containing columns of account info.
ORDER!G9 - This is the row number value I obtained by using a match function to lookup the Salespersons name entered in cell F8, then I add 1 to render the value of the row that contains the email address located directly below the salespersons name.
Herein lies my issue, the salesperson name will not always be in Column B
I can write another Match to obtain the column no, but then I am using another cell to hold a calculated value for my lookup statement.
ACCOUNTSABCD1CODACCT1ACCT2ACCT323 COMPANY 1COMPANY 2COMPANY 34ADDRESS ADDRESS ADDRESS 5 CITY STATE ZIPCITY STATE ZIPCITY STATE ZIP6TELEPHONETELEPHONETELEPHONE78910TOM JONESJANE DOEMARY SMITH11tjones@firstname.lastname@example.org@company3.com12SALESPERSON 2SALESPERSON email@example.com@company2.com14SALESPERSON firstname.lastname@example.org Excel 2010
In words, Find Mary Smiths email address, she works at the company with the account number in cell F1 on the order sheet.
I have seen in a number of spreadsheets that you can create a drop down list using data validation that hides certain columns depending on what you select. this leads me on to my question...
I have created a Gantt chart for an entire year and what to put a drop down box in C1 that contains four selections (Q1, Q2, Q3, Q4) to represent the four quarters of the year. I want to work it so that when the user selects Q1 it hides the columns where the other Quarters of the year are kept so you can only see that quarter.
If that is possible I would then like a second drop down box that allows the user to select a specific week.
I have to construct a financial model for Senior Executives to show year to date spent amounts. I have my worksheet as follows:-
Cell A2,A3,A4.. to A100 has - Account Numbers (Ex. A/c. 4100..) Cell B1, C1, D1.....has Jan2011,Feb2011,Mar2011.......and so on till Dec2011. Cell B2 onwards, down and to right, all spent amounts by month
What I need is a formula to get year to date number, which will change to Executives requirement.
I have a table in columns C:J, starting in row 7. I have a macro that selects and copies the whole table (after I filter the table) and pastes to another sheet. Recently I've realized that I can only copy/paste C:I or it will mess up my formulas during the process.
This is the code I have right now, which works beautifully for the whole table:
What I need is to know how to copy everything in the table except column J. I can't just copy a certain range cells because the number of rows is variable. There has to be a way to modify the ActiveSheet.ListObjects("Table36") right?
I have dedicated rows and column units.I do not want these to be changed from there current setting.Can just rows and columns be locked and password protected? If yes,where and how do i process this feature?
I have two excel 2010 files that I want to run this on. Each one has about 10+ worksheets in them. I am looking to have a vba script that will look at columns D and E starting at row 4 and check if they are expiring in the next month or have already expired (before today's date). Then it would return a text file that will say which worksheet it is on, the row and column, and what date is in that cell.
i am trying to come up with VBA code, sheet is attached. i have some columns that have headers but rows are empty . so the VBA should delete all the se columns entirely and leave those that has headers and have data in rows.
Excel 2010ABCDEFGHIJKLMNOPQRSTUVW1AccountUnitFund CodeDepartment ActivityAnalysisTypecodedeskitemBegin DateQuantityUnit of MeasureAmount CurrencyJob CodeEntry EventParent Budget Entry TypeOptionsLine CodeFunding SourceFacilities and AdministrationCost Sharing2
I have a Excel 2010 spreadsheet that contains 3 columns of (£) values. I am wanting to highlight a cell if one of these values do not match the other 2 columns. e.g below I would need the middle column to be highlighted as it does not match the other 2 cells.
25,449.47 25,451.65 25449.47
The thing to point out is that it can be any of the cells that do not match, not necessarily the middle column and occasionally all cells will not match and all need to be highlighted.
I am sure I need to use the Conditional Formatting, but cannot figure the formula.
I need to check, if in A1:BU1 are any duplicate words. All the formulas I found deal with finding duplicates downward (like A1:A1000). Have not seen any formula which works across (from left to right)
Is there an easy way in Excel 2010 either to tag/ and -or remove the duplicate which I could apply and then just copy downward? The formula must work from left to right, because many words repeat downward.
I have two columns of data; one is in inches, one is in metres. If someone is inputting data from an original source that has the data in metres I want excel to automatically calculate and input the data into the inches column. Additionally, if they input the data from the source in inches, I want excel to convert it to metres and input into the other column automatically. The conversion factor from inches to metres is * by 0.0254. I am no expert but after looking for this on the internet I am sure this requires some sort of VBA event code (although I don't really know the first thing about VBA). The excel columns will be in the same worksheet. I don't have a circular reference like I would do using normal Excel formula? I am using Excel 2010.
I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'
I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'
I am trying to sort each "Pct" column in descending order. Of course, I can do this manually, but I have over 100 to do, so I'd like to know how I can automate this (of course, the two columns to the left of "Pct" must move along with it).