I have a list of Grades (Job Description). Each of them is allocated a SCH No = SCH1 / SCH2 etc
I need a macro that will go through this list and copy the Grade into the correct SCH Column on Sheet List2.
The columns in List 2 then need to be sorted A-Z.
AddNamestolist.xlsx
I have a list of abbreviations in Sheet 2 and Names of Companies (Full name of Company and short name) in Sheet 1.
What I need to do is check if the abbreviation used in the Short Name Field in Sheet 1 is correct based on the given abbreviation list in Sheet 2.
here is the example:
Sheet 1:
Fulll Name of Company
Short name
AMERIPRISE FINANCIAL, INC.
Ameriprise Fin
Sheet 2: Abbreviation List
Full Version
Abbreviation
Academic
Acad
Bank
Bk
Financial
Finl
in the abbreviation list the correct abbreviation for financial is Finl so the short name should have been "Ameriprise Finl" and NOT "Ameriprise Fin". I'd like the rows highlighted in yellow if it is incorrect.
I have two problems sort of rolled into one. I have a spreadsheet with a list of skills to perform in cells a2:z2. Under those I put in dates where those skills are performed so a3:a100 , b3:b100 and so on. At the moment I have the these dates populating another sheet with the list of skills going a2:a200 and the dates in b2:b200.
Annoyingly I have been asked set it up so it inputs the other way so my problems are: The skills in the second sheet are in a different order than in the first sheet so its not a straight correlation between the two so I would need to search for the title and match it to the second sheet. Then the second problem is as the will be numerous entries I need to find the next available space in the first sheet. So can you add data at the bottom of a list??
I have two worksheets in one document. On sheet 'M_Admission' there are numbers for each week. can those numbers be automatically copied to the correct cell on sheet 2 'M_Actual' See the attached Excel worksheet to see what I mean. (Excel 2002)
View 3 Replies View RelatedTrying to assign this cell's value to the correct sheet tab for the current month.
Trying this:
Code:
=IF(MONTH(A3)=5,'[Wholesales 2014.xlsx]May'!$P$1,0),IF(MONTH(A3)=6,'[Wholesales 2014.xlsx]Jun'!$P$1,0)
A3 is todays date. Wholesales 2014.xlsx]May is a seprate sheet for the month of may and so forth for june.
The problem is the p1 value is empty for the Jun sheet so I get an error: #VALUE! (I assume that is why I am getting the error?)
Using:
Code:
=IF(MONTH(A3)=5,'[Wholesales 2014.xlsx]May'!$P$1,0)
By itself works just fine. I need to set this up for future use, though.
Here's the data table being referenced
Rank
Week Ending
Name
Value
1
1/1
Apple
100
[Code] ........
Now on another sheet, I want to return the top two 'Name's and their values like below:
Name
Value
There is a fluctuating amount of rows in the first table, too. So what formula can I use to return the correct names and values on another sheet?
I'm thinking it will use some form of concatenate for the first and use a sumifs function for the value column..
I have attached a 97-2003 .xls file with data for multiple store locations on sheet 1, and the desired result on sheet 2. I am actually using excel 2007, but I dont think I need any special features that it provides.
I will try to explain the issue here without opening the attachment.
Here is an example of the Data on Sheet1
I need to count a list of corporates claims settled as i have multiple Claims Status as outstanding, In process,Cheque prepared and Etc.
Basically i cannot use countif formula for the condition as it has only a range & criteria but here i have to check the list in the validation tab and map with the details in the side spread sheet given which are settled and count them so such criteria is so unknown to me to use .
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
View 4 Replies View RelatedI have a macro which copies one cell into another on a different sheet, provided it has content.
I want to change it so that it copies to the different sheet but puts the value in the correct row of the other sheet.
For instance sheet 2, A1 =TEST, A2= TEST2, A3= TEST3
Sheet1. B7 = TEST2, A15= This is a test
I want the macro to copy the value in A15 and copy it to B2 on Sheet2, as this is the matching row
This is what I had originally:
VB:
If Range("A15") = "" Then
Exit Sub
Else
Range("A15").Select
Selection.Copy
[Code .....
I wrote my program with several user forms and private subs. All the results was going to my Report sheet. However, now, I want to be able to run those userforms in another sheet. All the results are coming up in this selected sheet. Is there a way that I can program to for all the results to go to the report sheet without going back and fixing every private sub?
View 3 Replies View RelatedWe have a form (an Expense Sheet) that has the date entered in the "Monday" cell using the =Today() formula (the rest of the week uses =Today()+1, =Today()+2, etc).
I am betting you already see the problem...this only works if you open the form on a Monday....before I point out the error to the document owner I thought I better have a solution...
So...what is the best way to self-populate the "Monday" cell with the correct date for the Monday of the week?
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
#####################
149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.
#####################
I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.
I need to see how many items processed for each set per day.
Example:
[url]
The problem is that it counts the correct amount but not with the correct
dates.
The formula that I use is:
=SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
I have a workbook that starts off with the following sheets:
MAIN_PAGE - Where filter criteria is selected via checkbox and PLOT button
RAW_DATA - unfiltered raw data
After running a series of macros tied to the PLOT button, two new sheets are created:
FILTERED_DATA(HIDDEN) - where the filter criteria from the MAIN_PAGE is applied, and the filtered data is stored.
CHART - The plot of the hidden FILTER_DATA sheet.
Every time the PLOT button is pressed, the the macro searches for the FILTERED_DATA and CHART sheets, and if present, clears and overwrittes with the new appropriately filted data. This works and suits the user's needs 95% of the time. However....
QUESTION
If the user decides to keep the old CHART and FILTER_DATA as well as the new FILTER_DATA and CHART, I need something to differentiate the two charts and data sheets. My thought was to have the user rename the CHART. The problem is that because the FILTERED_DATA sheet is hidden (and has to stay that way), how can the name change of the CHART be carried over onto the hidden FILTERED_DATA sheet, therefore keeping the plot and it's data in tact? For example, if I append the name CHART with a 1 to make it CHART1, how do I automatically change FILTERED_DATA to FILTERED_DATA1?
The text disappears when I want to correct it (protected sheet). see the discription of the problem in the attachment with figures.
View 3 Replies View RelatedI have a form with a number of fields that once submitted paste the data in the next blank row in a sheet (as below). A user will input the date of the week commencing in a text box on the form (Calendar1) and a number of days in another box (txtSupp). I need it to submit the value of "txtSupp" in the same row on the sheet under the right date of the week commencing based on date in "Calendar1". Rows L1 to EJ1 of the sheet have the date of the week commencing starting from 02/04/2012. E.g. L1 = 02/04/2012, M1 = 09/04/2012 etc.
Private Sub SubmitForm_Click()
ActiveWorkbook.Sheets("Data").Activate
Range("A1").Select
[Code].....
I would like to know whether its possible to fix a sheet to always be the second sheet in a work book no matter how many other sheets are added. Currently I have set up macros to add subsequent sheets before the last sheet, so that Sheets("Number 2") remain sheets(2). Is there some way I can lock the first two sheets so that when I reference them in my code as sheets(1) and sheets(2) it will reference the right sheets. Right now I have it set up so that Sheets("Number 2") can be renamed by the user, and data inputted on this sheet, but I require that the user not be able to move this sheet and the sheet preceding it.
View 5 Replies View RelatedI am trying to write some code that is linked to a Command button. The code in the command button is in my workbook called "MF Consolidated ACTUAL DAILY REPORT - Dev.xls" but then I have another workbook called "MF BANK EXPOSURE SUMMARY.xls" that I want to do some work with - namely delete blank columns and it is here that I am having the problem.
My problem is this: my code module is contained in my project "MF Consolidated ACTUAL DAILY REPORT - Dev.xls" and the columns that I want deleted are in the "MF BANK EXPOSURE SUMMARY.xls". Now even though I believe I have activated the "MF BANK EXPOSURE SUMMARY.xls" workbook the action, that of deleting the blank columns is performed on the "MF Consolidated ACTUAL DAILY REPORT - Dev.xls" workbook. Why?? Here is the code that I am using:
Sub Commandbutton()
'ASK FOR DATE AND SET IT IN THE REPORT
Workbooks("MF Consolidated ACTUAL DAILY REPORT - Dev.xls").Worksheets _
("Seg and Non Seg Bank Summary"). Range("I1") = Application.InputBox("PLEASE ENTER REPORT DATE IN THE DD/MM/YYYY FORMAT")
'ACTIVATE MF BANK EXPOSURE SUMMARY
Workbooks("MF BANK EXPOSURE SUMMARY.xls"). Sheets("Seg and Non Seg").Activate
Dim iCol As Integer
Dim Isheet As Integer
With ActiveSheet.UsedRange................
I need a macro that will look for a list of sheet names - like Albany, Houston, Denver - and return the value of a particular cell - like B25 from each sheet and display it in a column of cells on a summary sheet.
View 9 Replies View RelatedI have a master list of names on sheet 1 column A. I want to create a list of names on sheet 2 column A of the people from Sheet 1 column A that have a specific value (Y) in column z sheet 1. I might also want to different numeric values for the Z column
View 7 Replies View RelatedI have one column: in the first cell comes text (beginning either with the letter "L" or "R") and afterwards, in the next cell, comes a number (either "7" or "8"). Basically the column is made up of alternating cells containing either text (code of a movie) or numbers (responses to the respective movies). I want to find a formula which writes either:
- "correct" if the number "7" follows a cell containing the letter "L" or an "8" follows a cell containing the letter "R";
or
- "incorrect" if the number "7" follows a cell starting with the letter "R" or the number "8" follows a cell starting with the letter "L".
Basically the 8 is always correct with an R and the 7 with an L: ...
I need a formula that will look up a cell to get a figure from, but there is three of the same name (sometimes more, depending on different products sold) i.e. "Dept Total" (shown below & attached for easier reading) ....
View 7 Replies View RelatedI have a workbook that has 31 sheets representing the days in a month. Each sheet is identical to each other. The naming convention for each sheet is as follows...(1,2,3,...31). The very last sheet is named "Summary". On this sheet I have in column A the dates from the beginning of the month dragged down to the last day of the month in this format xx/xx/xx. To the right I have it equal to a particular cell (M33) corresponding to the date. So for 04/01/2012, the value in the cell to the right is (='1'!M33). Instead of repeating the process of "=" the click the next tab and get M33 on that tab....is there a faster way of doing this where I can enter a formula and drag it down pulling all the "M33" from each sheet?
View 6 Replies View RelatedI have 2 worksheets (worksheet 2 is Slicing and Worksheet 1 is Production).
Production worksheet D6 has a dropdown box for, 1, 2, 3, 4, 5, 6, 7, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6 and 7.7.
In the Production worksheet, I have in cell E6 =IF(D6=1,"ONE",IF(D6=2,"TWO",IF(D6=3,"THREE",IF(D6=4,"FOUR",IF(D6=5,"FIVE",IF(D6=6,"SIX",IF(D6=7,"SEVEN")))))))
In the Production worksheet, I have in cell F6 =IF(D6=1.1,"ONE",IF(D6=2.2,"TWO",IF(D6=3.3,"THREE",IF(D6=4.4,"FOUR",IF(D6=5.5,"FIVE",IF(D6=6.6,"SIX",IF(D6=7.7,"SEVEN")))))))
How do I show in Slicing worksheet cell B6, either 'ONE', 'TWO', 'THREE', 'FOUR', 'FIVE', 'SIX', 'SEVEN' as i have the formula =PRODUCTION!E6&PRODUCTION!F6 and the cell shows '
1 is a Master sheets with all prices of products etc etc.
2nd sheet is an update of all products that have changed in price.
There is a unique code for each product.
What i was hoping for is a way where on the second sheet the product (using the unique codes assigned to the product) is looked up on the master sheet and the NEW price on the 2nd sheet replaces the OLD price on the Master sheet?
How I can have a summary sheet that takes a number from the same cell on each sheet in my workbook and display it on the summary sheet in a list. one for each sheet.
And is it possible to create a summary page that automatically updates every time I create a new sheet? (adds the name of the sheet to the summary list (alphabetical order) and the value of the specific cell)? Excel 2007
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.
View 3 Replies View RelatedI am trying to create individualized worksheets based off a master worksheet. I have different committee names that I want to appear on the individualized sheets, based on whether the person is apart of that committee or not. On my master sheet, I choose the persons name under the committee and I want that committee name to show on the individualized sheet. For example, I want A1 on Master to show on Name 1, B3 and A10 on Master to show on Name 1, C3. Can I write a formula to achieve this or or am I asking too much? In my actual file, I will have about 10 names and 5 committees with one person being on up to all 5 of the committees.
View 1 Replies View RelatedI am trying to write a simple macro to copy a drop down list I created in cell O2 and then paste in the same sheet. I need it to paste starting in cell A2 and then move to A4 and so on for every even row cell through A9778. I would also like it to keep a border around the cell. I can't find direction on how to tell it every even cell or defining x and then doing x + 1...
View 3 Replies View Related