I am working with some data that consists of two types of messages: one containing vessel speed and another containing vessel type. Both messages share a common ship identifier number. What I'd like to do is run through column A (ship ID), check column B (ship type), and if column b has a value, to paste that value in all instances of that ship ID.
View 1 RepliesI am trying to create a logical test with three possible results:
h2 <10.2 "needs improvement"
(if false) h2=10.2 "meets standard"
if false h2>10.2 "exceeds standard"
Basically, I am referring to a cell to determine if it meets, exceeds or does not meed a specific standard. In this case 10.2.
I am trying write code withVBA to save separate file in a different folder with Loop.
Write a code with "loop" till it finds empty cell in the column and for every change in number a separate file needs to be saved in specified folder with file name as "10010, 10011,10012... and so on with data copied in the file saved.
Below is the data.
10010
10011
10011
[Code] ....
I am working on a homework assignment, the books method was crap so I did my own method. I am almost done except for 1 last thing. I cannot find any function that will allow for 3 logical tests. I have a screen shot here explaining what I need to happen.
View 2 Replies View RelatedHow we represent AND OR in a function how many logical sequence can 1 if take
example =if((w4>u4 and v4=0) or (t4>r4 and s4=0);"yes";"no") can excel(2003) accept this , or there is an error in it what is error?
I have following formula that returns #value!
=IF(OR(OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D))="",OFFSET(LIVE_JNL,ROW(5:5),
COLUMN(C:D))=0,""),OFFSET(LIVE_JNL,ROW(5:5),COLUMN(C:D)))
Where the value in the cell which is referenced is either blank or zero then blank must be returned, otherwise the value which is being referenced by the offset formula.
what was wrong with this formula, an error message comes up saying VALUE, im trying to get the formula to say that - If 2 dates are different then subtract one from the other to get a number of working days, but if the dates are both the same then to -1 from the value as for some reason the value is coming back as 2 when it should be 0?
=IF(OR(M17>0,N17>0),NETWORKDAYS(M17,N17,holidays)+1+Q17,IF(OR(M17=N17),NETWORKDAYS(M17,N17,holidays)-1,0))
My logical test is =IF("D2"="E2",1,2). When I autofill or copy down, the row numbers do not change to "D3"="E3", etc. How do I delineate text and also have the ability for autofill or copy to change the rows as it goes?
View 2 Replies View RelatedBasically I have a column (lets call it column A) whereby I manually fill the cells green once I have received some documents, another column which has a numeric value in it (column B) and I want to create a third column which basically just copies column B but ONLY if column A is filled with a colour (actual colour doesn't matter cause I only use green)
I tried using the IF function but I don't know how to use cell colour as the logical test
Basically, I'm trying to calculate a pooled z-score from z-scores already stored in different cells.
Each individual z-score are calculated like this :
D1=IF(A1=999,999,((A1-10)/3)).
They are derived from standard scores with a mean of 10 and a SD of 3. I use 999 to mark missing values and this work flawlessly for single z-score cells.That way, if A1 is a missing value (999), Excel returns a value of 999.
Now here's my problem. How do I tell Excel to ignore 999 values if I am pooling multiple z-scores together?
Let's say A1, B1 and C1 are single z-scores. I want D1 to be the pooled z-score result, but I want it to ignore A1,B1 and/or C1 if their value is 999. For instance, if A1=+2.0 , B1 = 999 and C1= -1.50, I want my D1 cell to ignore B1 in the calculation.
I've tried : D1=IF(A1,B1,C1=999,999,(LC5+LI5+IK5)/3)) but it sure doesn't work...
i am using the IF script, and i am also using the COUNT script in the logical part of the IF statement. i need to make sure that both K3 and V3 have a value inside of the cell in order for the statement to work, although when just K3 has a value, the IF - TRUE statement works anyway. any ideas on how to fix this? =IF(COUNT(K3,V3),SUM(K3+V3),"")
View 4 Replies View RelatedHow do I change the color of a cell as the result of a logical function?
For example: =IF(A1<A2,A1 is yellow, A2 is yellow).
I would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.
The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?
I have one workbook that needs two macros.
On the "Complete Backlog" tab of my workbook, I want users to enter in the requested information based on the column header. Then I would like a Macro attached to a button that says "Refresh" that the user would click after they have entered in all of the information. This macro should look in Column M (WIP Status) and if any of the cells say "Close", it should Cut the entire row from the spreadsheet(Ex. A2:M2) and Paste it into the speadsheet titled "Closed Jobs".
This is so that as jobs are closed/finished, they are removed and stored on a separate sheet. The items would have to be pasted so that it pastes into the next available row - not just on top of each other.
I also need another macro that i can put into a button that doesn't "delete" a row from the sheet, but just copies over to another sheet - so that there are two instances in the workbook.
If would look something like: If a cell in "Column G / Director" of the "Complete Backlog" speadsheet is equal to "Snodgress" then copy columns A-L of the same row to the spreadsheet titled "Snodgress" - of course skipping down the rows to the next blank row.
.....is equal to "Herr" copy row to "Herr" spreadsheet.
....is equal to "McCormick" copy row to "McCormick" spreadsheet.
and so on.
excel 2007. Here is the situation:
I am using the Index and Match function to lookup for two specific criterias in a different worksheet. So far, it is working well, but it gets complicated. I want to look for the criterias in 6 different worksheets based on what a certain column is saying. Here is an example:
A
B
C
D
[Code].....
So, if the continent is Asia in the column A, I want Excel to look in the Asia worksheet for the city and the venue and return me the contact information. Same, if the continent says Europe, I want it to look in the Europe worksheet or the city and the venue and return me the contact information. So on and so forth.
The formula I have at the moment is this:
{=INDEX('Asia'!$1:$1048576;MATCH(B3&C3;'Asia'!D:D&'Asia'!E:E;0);9)}
How do I incorporate the logical test for it to look for the proper worksheet knowing that all my continent worksheet have the same structure?
Can I write Copy and paste function in an IF statement
PTPO #Part #Line #DescriptionVendor #UOMLTPO Entry #Due DateAllowed DaysWork daysQty OrderQty RecvdUnder/ OverRectp dateDays Po lateMB550427917495072PANEL, SW MOLDED SLVR0BAY008EA204/16/20075/14/200728215014-365/7/200702013135/30/2007-280232023236/1/2007-28025
formulae in the column Q " days PO, Late" =IF(P3
How would I create a shortcut to automatically copy the contents of cell H1 to cell K1, in other word I would like to hit a shortcut key and have the contents of that cell pasted 3 cells to the right on the same line. I have to do this over and over again down the worksheet. I am working on taxes and want to copy values over to the expenses column as I find them.
I would also like to know if it is possible while having cell D1 highlighted I could have a shortcut created that would copy the value three cells over in H1 to cell K1. That would be the fastest, but I don't know if it is possible?
The problem is that whenever I have any browser open, IE, Chrome, Firefox, etc... the Cut & Paste, Copy & Paste function does not work correctly in Excel 2010.
When I Cut or Copy the blinking marquee around my selection briefly appears and then disappears. When I try to paste, I only have two options under the paste special function: Unicode Text and Text, same with cut and paste, however, the text doesn't actually cut, it only copies.
As soon as I close down any of the mentioned browsers, the full functionality of the cut/copy & paste functions are restored, no need to restart excel.
I need to have open a browser most of the time for work as our system is web based, so closing and re-opening is more than just an annoyance.
First I want to copy the calculations from sheet "Calc" to sheet "Cost" then in column J in the same row, the background color will change like conditional format on numbers. The code works when copy the calculations from sheet "Calc" to sheet "Cost" but the color function (codes in sheet "Cost") does not work after pasted in sheet "Cost". But the color funtion does works when enter numbers in Column J alone. I know there must be a simple codes that can do this while paste from other sheet. This code is in module
View 3 Replies View Relatedwhat I do with excel: I have an excel sheet that has over 18,000 rows in it. Since it would be a nightmare to scroll around to find what I want, I use the Custom Sort and Filter options under Editing>Sort&Filter. So for example, I can omit 17,800 rows using a specific setting so that I can work with a more reasonable 200 rows. Moreover, the 200 rows comes from all over the spreadsheet. Meaning their row numbers are not always consecutive.
Here's the problem: Whenever I try to copy anything from this "edited or filtered" excel sheet, the resulting paste is not an exact copy. Excel perfectly copies the first rows up until the point where the row numbers ceases to be consecutive. So, the copy function messes up somehow when the data being copied comes from a different section of the original 18,000 rows.
In case this isn't clear enough...
Let's say that the original file has rows 1,2,3,4,5,6,7,8,9,10
Once I filter/custom sort, I see rows 1,2,3,8,9,10
When I try to copy/paste 2,3,8,9, excel copies 2,3, but messes up the rest of the 8,9. And I end up with a totally useless copy that's generally shorter than it's supposed to be.
I want to copy and paste from one sheet to another based on column a using a macro copy button.
E.g. if column a value = apple then copy that row into the apple sheet.
I want this macro to find in this case "406" in column A which is at the very end of the last block of 160 rows of information.
I then want it to move up 159 rows and copy 160 rows of information underneath the last block of information.
i.e.go to A5280 , then go to a5121, copy rows 5121:5280 to 5281.
It falls over on the very last line of code I can see A5281 selected but it won't paste....
I'm stuck on the final piece of my macro project. I've attached a workbook with two sheets: sheet 1 is what I currently have, and sheet 2 is what I'd like my report to look like when complete.
On Sheet 1:
- column B is called Supervisor Name
- Columns D-O are months of the year, with either a Yes or No in each cell.
I'd like to write a macro that will:
- Take all unique values in Supervisor Name column, and paste these Above the current table.
- For each Supervisor, and each month, I'd like it to calculate, as a %, the number of Yes mentions in each month divided by the total cells (Yes/(Yes+No)).
Sheet 2 contains the output, in the format I'd like to see it.
Sheet 2:
Supervisor Name
October
November
December
Supervisor 1
8.3%
[Code] .......
I am trying to copy one worksheet using the "move/copy" function that is available when you right click a tab name and want to copy the worksheet in the same workbook
The steps I'm using are:
1. Right click the tab name
2. Select Move or Copy,
3. Select Create a Copy
4. Click OK
Doing all of the above does not work. When the new worksheet opens, all columns are the same width. It seems to be stuck on "autofit column width" setting of 8.5. The original worksheet is several columns wide all with different width settings.
I've also used Copy, Paste Special and selected column widths and that does not work either.
I have data from (row 1, column 1) to (row 53, column 5) on 283 consecutive worksheets in a singular excel file that I would like to be presented on a singular worksheet starting from the data on worksheet 1 and descending to the data on worksheet 283.
I am looking for a copy and paste loop solution that will copy the data from each page and sequentially paste the results on a singular output page in descending order (worksheet 1 data, worksheet 2 data... etc) so that I can sort the data.
I tried writing a macro to copy data from one tab and paste it on another. It gave me an error message every time.
So I tried recording one doing just that, and when I went to play it, it still gave me an error.
So here is what I came up with:
I have a total of 30 sheets in my workbook. I would like to copy rows 21:1000 from sheets 5 - 27 and paste them into sheet 4 starting on row 21. The data ranges vary within sheets 5 - 27. For example, sheet 6 has data in rows 21:50, whereas sheet 10 has data in rows 21:500. I chose 21:1000 as a safe measure to ensure that all data starting on rows 21 onward gets copied.
I am having trouble thinking of how to create a macro which will copy the data from sheets 5 - 27 and paste into sheet 4 leaving no blank spaces in between pasted rows. Ultimately my goal is to use the filter boxes I have set up in the column headers of sheet 4 to quickly sort all the data pasted from sheets 5 - 27.
im trying to record a macro that allows me to copy b22-b25 so i can copy it sp its on my clipboard to use in other applications
but i have this and it doesnt allow me to paste
I need a macro that will copy row 7, then let user select desired row, then the macro will paste the copied row onto it.
View 5 Replies View Relatedi'm currently building a project with VBS2008, i'm currently stuck on 2 things.
I have the beginning of my code here(it's a button):
Private Sub Button14_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button14.Click
Shell("C:Program FilesMicrosoft OfficeOFFICE11EXCEL.EXE")
AppActivate("Adobe Reader")
'copy stuff wants to go here
'Then i want to paste into Excel
AppActivate("Microsoft Excel")
'Then run a macro
(PERSONAL.XLS!Eden)
End Sub
Adobe Reader will always be running.
I'm trying to copy all text data from Adobe and paste it into Excel. I'm having trouble trying to mimic pressing [Ctrl-A] [Ctrl-C] and then mimic the paste [CTRL-V] into excel, then running the Macro in Excel.