Example6.xlsx .i just cant remember what it is im meant to be using as i havnt done it since school. basicly on the attached spreadsheet, there are 2 tabs. the common column is Contract number, if the data in the contract number column matches then i need the figures for 20/40/TEU to show against the Special aggreement by code tab.
View 3 Replies
I need to indicate if the figures for this week has gone up or down compared to last weeks figures.
So looking at the attached, I need to add a column (I5) to sheet Admitted Patients WE 20081005, so it compares the % treated in <18 wks figure by specialty to previous weeks figures on sheet Admitted Patients WE 20080921.
Ideally I would love an arrow either point up if the figures has increased or down if they have reduced.
Normally, when we use the =MAX() function, figures / numbers will appear. Is there any formula that can show the row the highest data is in? Let's just say that the figure is 3080 and it is under 'Transportation' row. Generally, when I apply =MAX() fx, 3080 will appear. Is it possible for it to show Transportation by using formula instead?
View 4 Replies View Related[URL]
attached is a returns sheets that a qty is logged against part number every week. Some of the data I can use pivots on ( no problem ), but I have an issue on the returns tab where I think index/match / sum or something else will be better. At the moment because of the layout of data I have to drag each week number into the pivot value, do a greater than zero filter then summarize as a sum (yuk) I just cant get my head around index/match /sum. It would be great just to be able to enter a week number, and all the warehouse, part numbers and qty came through. There is a short explanation on the returns tab of what im looking for.
so in essence I want week numbers across the top, but be able to enter a year on the returns tab that looks at the week number. I don't know enough about the formula to change.
I am using Excel 2007, I have a customer sheet and a sales sheet in the same work book. My customer sheet lists all customers with a single entry only each with a unique customer code. My Sales sheet lists at an item level but does include the customer code so if my customer has purchased more than one item the customer code can be repeated on the Sales sheet on multiple rows.
When I do a vlookup to display the amount each customer has spent on the customer sheet, only the very first sales figure is displayed, if further down the sales sheet the same customer has made another purchase I want this figure to be added to the first figure and all I want to see on my customer sheet is the final total figure.
The vlookup formula I am currently using is:
=VLOOKUP(G2,Sales!$A$2:$B$1785,2,FALSE),
I'm thinking maybe a Vlookup will not do what I require, maybe I need a different formula/function?
Code:
' write data to txt file
For j = 1 To 3
For k = 1 To 21
Print FNum, OPTOarray(j, k)
Next k
Next j
I am writing an Array to a text file but I keep getting an error that the Print statement requires a suitable object. I tried Debug but all this did was put the data into the immediate window and created a file that was empty.
"????.Print FNum, OPTOarray(j, k)".
The data in the immediate window is correct but when you open the text file with notepad there is nothing to be seen.
In my code, I'm calculating a very simple ratio and have the result displayed in a message box. However, I want to have this value only show 1 or 2 significant figures (e.g. if I take 9 / 7, I want to show 1.3 and not 1.28571).
View 2 Replies View RelatedI dont know the correct terminology with whick to phrase my question, but I would like to know if its possible to copy say columns A-J on "worksheet A", and copy columns A-J on "worksheet B", each of which have different column widths, and paste them both to "worksheet C"?
My problem is columns A-J on "worksheet A" are perfect and copy and past fine to "worksheet C". But when I copy columns A-J from "worksheet B",which have different widths, and paste them to "worksheet C" right below what I had previously pasted from "worksheet A", the column widths interfere with each other.
I have tried a special paste, and it seems to re format everything above the current page its pasting?
1. Alphabetize the worksheets that have a red tab color. The number of worksheets with a red tab color will vary from time to time.
2. All worksheets with a red tab color are formatted the same. I would like to copy the same range of cells (AP5:BP9) on all n number of worksheets with a red tab color to a worksheet labeled "Posting". Also, since AP5:BP9 are cells with formulas and formatting, the ranges need to be copied with formats and value and number formats. The first copied range needs to be on line 8 on the Posting worksheet, the second range to start on line 15, etc.
3. Last, but not really last, I would like to start the code with deleting whatever is currently on the Posting worksheet starting at line 8.
I have attached a small sample. The sample does not reference the same range as above on the worksheets with a red tab color, nor does it contain the formulas and formatting.
I am trying to use the if statement, if a cell = a cell that has a word in it then show content of another cell that has a name in it.
View 5 Replies View RelatedWhen functions like MATCH, LOOKUP, etc are being used the result can be #N/A. An additional IF statement is required to NOT show the #N/A. The Example =if(ISERROR(match ... very long...),"",match ... very long ...) Read =IF(A is Not OK, "", A) A (very long formula) is used twice If your formula is very long the total formula even get twice as long. Is there a way to shorten this?
View 4 Replies View RelatedColumn A = program which could be 100%, 90/10 or 80/20
Column B = Dollar amount submitted
Column C = results
I need a formula in C1 that says:
if A1=100% then B1*100%, if A1=90/10 then B1*90%, if A1=80/20 then B1*80%.
With 2 programs my if statement worked fine, but now that there's a 3rd I can't get my if statement to work properly.
I have written a short VBA code to create a Paste Special values keyboard shortcut.
Is there a way or a place that I can put this code so that every time I open any workbook in Excel, or open Excel itself that this VBA code will be active and I can use the keyboard shortcut?
i am trying to amend my if statement in the 6 WEEK COLUMN to only show for entries that have 13 in the weeks column, I want the entries that show 2 or 26 to come up with a blank result. I have attached an extract for your information.
View 4 Replies View RelatedI have three columns of Vlookup data and three columns with a Vlookup formula. I need a fourth column to look at the three columns and see which ones are returning no value (cell with #N/A) in them and then return the cell that does have a value in them or the first row that has a value in it. The values are actually NAICS title descriptions and not numbers.
Column1 Column2 Column3
#N/A #N/A Electrical Componenets Retail
#N/A Boat Dealer Boat Dealer
Tire Retail #N/A Tire Retail
I need to lookup data from four different columns to match a value in another sheet. The lookup value has to start with "4" in order for it to be the correct match. I've tried the following formula for just two columns:
=IF(VLOOKUP($C2,'Sheet 1'!$D$2:$H$10000,5,FALSE)="4*",VLOOKUP($C2,'Sheet 1'!$D$2:$H$10000,5,FALSE),IF(VLOOKUP($C2,'Sheet 2'!$E$2:$G$10000,3,FALSE)="4*", VLOOKUP($C2,'Sheet 2'!$E$2:$G$10000,3,FALSE),"False"))
The problem I have is that the wildcard function isn't working in my If statement (apparentley that's how Excel was designed). The first vlookup returns the value "40042565" but Excel doesn't show it matching the criteria with the wildcard ("4*").
Any way to lookup the value in C2, search through multiple columns, and return the value that starts with "4"?
Let's say for instance I have a vlookup that displays a the quantity of available items in stock pulled from a column in another workbook. Occasionally these numbers are negatives as they are backordered I would like to display these values as zero.
View 2 Replies View RelatedHow can you return a Vlookup as a true value in an If statement? For example
View 2 Replies View RelatedI want to add another vlookup criteria into the statement below and im having some difficulty, its currently has two called "Name" and "QrtName", i want to add another called "PlaceName". I have a named range called "Data" for the data the vlookup options use. It is working fine other only i want to add the third vlookup option:
View 9 Replies View RelatedIn the example below I have created a validation list in B1 that references D2:D8. What I am then trying to do for cell B2 is create a vlookup and nested if statement that says if b1 is a number lookup the value in column D and return the value in colunm E and if b1 doesn't = a number, ie 'none' then return a null value. The same goes for B3 where if b1 is a number lookup the value in column D and return the value in columm F and if b1 doesn't = a number, ie 'none' then return a null value.dropdown list problem.
************************************************************************>Microsoft Excel - Book1.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEF1Numbernone*NumberDescriptionRef2Description**none**3Ref**1one44***2two55***3three66***4four77***5five88***6six9Sheet2*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have a list of parts that I have to create in a system. It is easier to do if I create a list of materials ahead of time of what components are needed. What I have done is create a part list that contains a list of parts, what the cost, whether they are manufactured or purchased, and what the sub component is if they are manufactured.
This worked great till I ran into my first sub component that contain its own sub components. I am attaching the spreadsheet so everyone can see what I am doing or more to the point trying to do. IF VBA is a better solution that would be great.
What I need is a formula (or VBA if it is better) that brings back the sub-components and their sub-components for each part until the parts tree for the original part number reaches the base purchased sub-parts.
I have the 1st worksheet set up in three different ways. The first one you run into is simply a length calculator and can be ignored.
The second way is the original way I had the spreadsheet working and it works fine for parts with only one sub-component. If a sub-part has more than one sub-component then it doesn't work.
The third section is where I started building what I thought would work, but it is clumsy and only will work nicely if I can place an IF statement that will only bring back a value for a sub-component if one exists otherwise it will remain blank.
What I would like is to have a formula that will auto fill the information IF and only if it exists for each sub-part listing all sub-components ( I need if it exists, cost, whether it is manufactured or purchased, any and all sub-components [followed by the same information as the parent] and vendor).
I am trying to use vlookup on the the income statements I am using. I have a workbook called overview with all the months and need the "total" of each month to appear automatically. The previous vlookup formula does not seem to be working.
View 13 Replies View RelatedI have two cells with the following equations:
H3=IFERROR(VLOOKUP(A3&TEXT($A$1,"m/d/yyyy"),'CLABSI_Raw'!$A$1:$M$300,13,FALSE)," ")
I3=IFERROR(VLOOKUP(A3&TEXT($A$1,"m/d/yyyy"),'CLABSI_Raw'!$A$1:$M$300,10,FALSE)," ")
In an adjacent cell, I am trying to use an if statement
J3=if(and(H3<1,I3<1),"yes","no")
I am not getting a return in cell J3.
Is there any way to combine the VLOOKUP and IF Statement formula's into one
formula or is there any other kind of formula which does both?
I am trying to match up two sheets and I the match criteria is based on the data from columns B, C, D, E, and G. Sheet 1 contains ~20,000 line items of data. Sheet 2 contains ~250 line items. I am comparing the info in sheet2 vs. the data in sheet1. Both sheets are formatted the same.
For each row in sheet2, I need to compare against all rows in sheet1. For example; for the first row in sheet2, determine if there is a row in sheet1 that contains the exact same data for columns B, D, E, and G (all must be true) and the data in column C is False. Examples:
I have a statement in a cell in which there is a date field.
Is it possible to apply Vlookup on the statement and change the Date value keeping the statement same ???????
I need some help with vlookup/if statement ......
View 9 Replies View RelatedOK i have put the membership cards on the same sheet as my raw data so to make the formulas easier. On the membership card i have under membership level i have the formula : =VLOOKUP($J$3,A:E,4,FALSE)
there is 3 types of level bronze, silver and gold
what i would like is if the level is gold after then an image to be placed in the cell rather than the word gold and a different image for silver and a different image for bronze.
I am building a template for a report. The report resides on the first sheet of the workbook and the subsequent sheets is where the user would paste the appropriate reports so that the formulas on the first sheet can pull the appropriate data. I have all my formulas working fine with one exception.
The data in question needs to be pulled from the sheet "Paste Adcap Report Here" and the column is AA. The common piece of data between the two sheets is in column C of the Adcap sheet and column B of the main report sheet. What I need to do is conduct a vlookup between the report sheet and the Adcap sheet using the account id's and then return either the date value in row AA or the word ongoing which would be those accounts which do not have a preset end date. So far a co-worker had been able to come up with the following:
=If(VLOOKUP(B16, 'Paste Adcap Report Here'!C:AA,25,0) >0,"cap","ongoing")
The code is a bit screwy as she had to leave early. The problem we encountered was the entry in column AA for a non-ending campaign. It is represented by two hypens (--). This is what tied her up. we could not figure out why the formula was returning an #n/a instead of the term "ongoing" when it encountered the (--). Again, if the formula pulls a (--) then the word "ongoing" needs to be displayed and if the formula pulls an actual date value then it needs to display this date.
if a value appears in a column 4 times or more how can you do a vlookup which will show the 1st 2nd 3rd and 4th values.
when i currently write the vlookup formula it shows the 4th time the value appears
=VLOOKUP(B5,B14:D1854,3,0)
