If Or/and Statement 


If Or/and Statement  Excel 
View Answers 
I need help with a code so that if the lookup in W returns any value other than zero, it leaves X blank. If the lookup in W returns 0 then X=0
* W X 1 Tight Net Tight Net Rolls 2 3.25 5 3 2.25 3 4 0 0 5 0 0
Spreadsheet Formulas Cell Formula W2 =IF(VLOOKUP (C2,Table1,20,FALSE) ,VLOOKUP (C2,Table1,20,FALSE) ,0) W3 =IF(VLOOKUP (C3,Table1,20,FALSE) ,VLOOKUP (C3,Table1,20,FALSE) ,0) W4 =IF(VLOOKUP (C4,Table1,20,FALSE) ,VLOOKUP (C4,Table1,20,FALSE) ,0) X4 =IF(Table2[[#This Row],[Tight Net ]]=0,0,ISBLANK) W5 =IF(VLOOKUP (C5,Table1,20,FALSE) ,VLOOKUP (C5,Table1,20,FALSE) ,0)
Excel tables to the web >> Excel Jeanie HTML 4
* W X 1 Tight Net Tight Net Rolls 2 3.25 5 3 2.25 3 4 0 0 5 0 0
Spreadsheet Formulas Cell Formula W2 =IF(VLOOKUP (C2,Table1,20,FALSE) ,VLOOKUP (C2,Table1,20,FALSE) ,0) W3 =IF(VLOOKUP (C3,Table1,20,FALSE) ,VLOOKUP (C3,Table1,20,FALSE) ,0) W4 =IF(VLOOKUP (C4,Table1,20,FALSE) ,VLOOKUP (C4,Table1,20,FALSE) ,0) X4 =IF(Table2[[#This Row],[Tight Net ]]=0,0,ISBLANK) W5 =IF(VLOOKUP (C5,Table1,20,FALSE) ,VLOOKUP (C5,Table1,20,FALSE) ,0)
Excel tables to the web >> Excel Jeanie HTML 4
Similar Excel Tutorials
How to Find and Fix Errors in Complex Formulas in Excel
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
Remove Vlookup #N/A Error in Excel
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...
How to Use Multiple Functions and Formulas in a Single Cell in Excel
Lets learn how to put multiple functions and formulas in a single cell in Excel in order to build more complex form ...
Lets learn how to put multiple functions and formulas in a single cell in Excel in order to build more complex form ...
Introducing Logic into Formulas and Functions in Excel
In this tutorial I am going to introduce the idea of Logic in Formulas. A Logic test is a test that evaluates eithe ...
In this tutorial I am going to introduce the idea of Logic in Formulas. A Logic test is a test that evaluates eithe ...
Helpful Excel Macros
Vlookup Function That Searches The Entire Workbook  UDF
 This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
 This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Basic Web Query in Excel  Import Data from the Web into Excel
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Determine if a Cell Contains a Function in Excel  Great for Conditional Formatting and Validation  UDF
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Vlookup Macro to Return All Matching Results from a Sheet in Excel
 This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
 This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Similar Topics
I would like to code cell F3 so that if A3 & A4 are the same and D3 and D4 are the same then F3 is divided by 2 and F4= F3. If A3 is different from A4 and/or D3 is different from D4 then I do not want anything to happen to the value in F3
So using the example below if F3 were to entered as 58 being that A3=A4, D3 = D4 are all the same, F3 would be divided by 2 (equaling 29) and then F4 would equal the value of F3. Any suggestions???
* A B C D E F G H I J K L M N O P Q R 3 9Jun 2 160 Jumbo TC * * * 2 0 50.9 0 2.5 0 0 3.5 x 42 * 0 PCS 4 9Jun 3 160 Jumbo Alpina * * * 2 0 50.9 0 2.5 0 0 3.5 x 150 * 0 FT
Spreadsheet Formulas Cell Formula C3 =A3DATE(YEAR (A3) ,1,0) D3 =VLOOKUP(B3,Table1,2,FALSE) E3 =VLOOKUP(B3,Table1,3,FALSE) I3 =VLOOKUP(B3,Table1,5,FALSE) J3 =F3*VLOOKUP(B3,Table1,7,FALSE) K3 =VLOOKUP(B3,Table1,7,FALSE) L3 =F3*VLOOKUP(B3,Table1,9,FALSE) M3 =VLOOKUP(B3,Table1,9,FALSE) N3 =F3*VLOOKUP(B3,Table1,11,FALSE) O3 =F3*VLOOKUP(B3,Table1,12,FALSE) P3 =VLOOKUP(B3,Table1,13,FALSE) R3 =Q3*VLOOKUP(B3,Table1,14,FALSE)& " " & VLOOKUP(B3,Table1,15,FALSE) C4 =A4DATE(YEAR (A4) ,1,0) D4 =VLOOKUP(B4,Table1,2,FALSE) E4 =VLOOKUP(B4,Table1,3,FALSE) I4 =VLOOKUP(B4,Table1,5,FALSE) J4 =F4*VLOOKUP(B4,Table1,7,FALSE) K4 =VLOOKUP(B4,Table1,7,FALSE) L4 =F4*VLOOKUP(B4,Table1,9,FALSE) M4 =VLOOKUP(B4,Table1,9,FALSE) N4 =F4*VLOOKUP(B4,Table1,11,FALSE) O4 =F4*VLOOKUP(B4,Table1,12,FALSE) P4 =VLOOKUP(B4,Table1,13,FALSE) R4 =Q4*VLOOKUP(B4,Table1,14,FALSE)& " " & VLOOKUP(B4,Table1,15,FALSE)
Excel tables to the web >> Excel Jeanie HTML 4
So using the example below if F3 were to entered as 58 being that A3=A4, D3 = D4 are all the same, F3 would be divided by 2 (equaling 29) and then F4 would equal the value of F3. Any suggestions???
* A B C D E F G H I J K L M N O P Q R 3 9Jun 2 160 Jumbo TC * * * 2 0 50.9 0 2.5 0 0 3.5 x 42 * 0 PCS 4 9Jun 3 160 Jumbo Alpina * * * 2 0 50.9 0 2.5 0 0 3.5 x 150 * 0 FT
Spreadsheet Formulas Cell Formula C3 =A3DATE(YEAR (A3) ,1,0) D3 =VLOOKUP(B3,Table1,2,FALSE) E3 =VLOOKUP(B3,Table1,3,FALSE) I3 =VLOOKUP(B3,Table1,5,FALSE) J3 =F3*VLOOKUP(B3,Table1,7,FALSE) K3 =VLOOKUP(B3,Table1,7,FALSE) L3 =F3*VLOOKUP(B3,Table1,9,FALSE) M3 =VLOOKUP(B3,Table1,9,FALSE) N3 =F3*VLOOKUP(B3,Table1,11,FALSE) O3 =F3*VLOOKUP(B3,Table1,12,FALSE) P3 =VLOOKUP(B3,Table1,13,FALSE) R3 =Q3*VLOOKUP(B3,Table1,14,FALSE)& " " & VLOOKUP(B3,Table1,15,FALSE) C4 =A4DATE(YEAR (A4) ,1,0) D4 =VLOOKUP(B4,Table1,2,FALSE) E4 =VLOOKUP(B4,Table1,3,FALSE) I4 =VLOOKUP(B4,Table1,5,FALSE) J4 =F4*VLOOKUP(B4,Table1,7,FALSE) K4 =VLOOKUP(B4,Table1,7,FALSE) L4 =F4*VLOOKUP(B4,Table1,9,FALSE) M4 =VLOOKUP(B4,Table1,9,FALSE) N4 =F4*VLOOKUP(B4,Table1,11,FALSE) O4 =F4*VLOOKUP(B4,Table1,12,FALSE) P4 =VLOOKUP(B4,Table1,13,FALSE) R4 =Q4*VLOOKUP(B4,Table1,14,FALSE)& " " & VLOOKUP(B4,Table1,15,FALSE)
Excel tables to the web >> Excel Jeanie HTML 4
I've got a worksheet with several tables, each of which I've assigned a defined name. At the top of my worksheet I have several cells containing lookup functions, and these are repeated for each defined table on the sheet.
Right now I'm creating all my lookup functions for a single table, copying the cells containing these functions, and editing the references to the defined names in the copies by hand, giving me a final product something like this:
Code:
To get this result, I'd normally do all the formulas in Column A first, then copy these formulas to Columns BD, but doing that gives me the result below:
Code:
As you can see, I still have to go in to Columns BD and manually correct the table reference names. So, my question is this: is it possible to enter the text values Table1, Table2, etc. in Row 1, and somehow have all my vlookup functions extract the name of the table to use from these cells? I know I can't just do something like having the formula in A3 =VLOOKUP(A$2,A$1,2) since that will make the formula think that the range A1 itself is where I want to do the lookup. Any ideas?
Right now I'm creating all my lookup functions for a single table, copying the cells containing these functions, and editing the references to the defined names in the copies by hand, giving me a final product something like this:
Code:
A B C D 1 2 10 10 10 10 3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table2,2) =VLOOKUP(C$2,Table3,2) =VLOOKUP(D$2,Table4,2) 4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table2,3) =VLOOKUP(C$2,Table3,3) =VLOOKUP(D$2,Table4,3) 5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table2,4) =VLOOKUP(C$2,Table3,4) =VLOOKUP(D$2,Table4,4) 6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table2,5) =VLOOKUP(C$2,Table3,5) =VLOOKUP(D$2,Table4,5)
To get this result, I'd normally do all the formulas in Column A first, then copy these formulas to Columns BD, but doing that gives me the result below:
Code:
A B C D A B C D 1 2 10 10 10 10 3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table1,2) =VLOOKUP(C$2,Table1,2) =VLOOKUP(D$2,Table1,2) 4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table1,3) =VLOOKUP(C$2,Table1,3) =VLOOKUP(D$2,Table1,3) 5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table1,4) =VLOOKUP(C$2,Table1,4) =VLOOKUP(D$2,Table1,4) 6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table1,5) =VLOOKUP(C$2,Table1,5) =VLOOKUP(D$2,Table1,5)
As you can see, I still have to go in to Columns BD and manually correct the table reference names. So, my question is this: is it possible to enter the text values Table1, Table2, etc. in Row 1, and somehow have all my vlookup functions extract the name of the table to use from these cells? I know I can't just do something like having the formula in A3 =VLOOKUP(A$2,A$1,2) since that will make the formula think that the range A1 itself is where I want to do the lookup. Any ideas?
Hey Guys I Have I Q
I'd Like To Make To Make Every Thing Variable In "Gray Table" So I Can Choose According To My Need I Work
Ex
1 Ranges Variables So I Can Choose The Range I Need
2 Look Up Table Variable So I Can Choose Between Tables I need
3 Color Every Range With Specific Color Through Drop Down List Color
Sheet1
A B C D E F G H I J K L M N O P Q 1 A1 C 700 Range Tables Color Table1 Table2 Table3 2 A2 C 701 First LookUp A1 A4 Table1 Blue Sky A1 C 700 A1 Z 500 A1 Y 300 3 A3 C 702 Second LookUp A5 A13 Table3 Red A2 C 701 A2 Z 501 A2 Y 301 4 A4 C 703 Third LookUp A14 A18 Table2 Yellow A3 C 702 A3 Z 502 A3 Y 302 5 A5 Y 304 A4 C 703 A4 Z 503 A4 Y 303 6 A6 Y 305 A5 C 704 A5 Z 504 A5 Y 304 7 A7 Y 306 A6 C 705 A6 Z 505 A6 Y 305 8 A8 Y 307 A7 C 706 A7 Z 506 A7 Y 306 9 A9 Y 308 A8 C 707 A8 Z 507 A8 Y 307 10 A10 Y 309 A9 C 708 A9 Z 508 A9 Y 308 11 A11 Y 310 A10 C 709 A10 Z 509 A10 Y 309 12 A12 Y 311 A11 C 710 A11 Z 510 A11 Y 310 13 A13 Y 312 A12 C 711 A12 Z 511 A12 Y 311 14 A14 Z 513 A13 C 712 A13 Z 512 A13 Y 312 15 A15 Z 514 A14 C 713 A14 Z 513 A14 Y 313 16 A16 Z 515 A15 C 714 A15 Z 514 A15 Y 314 17 A17 Z 516 A16 C 715 A16 Z 515 A16 Y 315 18 A18 Z 517 A17 C 716 A17 Z 516 A17 Y 316 19 A18 C 717 A18 Z 517 A18 Y 317 20 A19 C 718 A19 Z 518 A19 Y 318 21 A20 C 719 A20 Z519 A20 Y 319
Spreadsheet Formulas Cell Formula B1 =VLOOKUP(A1,Table1,2,0) B2 =VLOOKUP(A2,Table1,2,0) B3 =VLOOKUP(A3,Table1,2,0) B4 =VLOOKUP(A4,Table1,2,0) B5 =VLOOKUP(A5,Table3,2,0) B6 =VLOOKUP(A6,Table3,2,0) B7 =VLOOKUP(A7,Table3,2,0) B8 =VLOOKUP(A8,Table3,2,0) B9 =VLOOKUP(A9,Table3,2,0) B10 =VLOOKUP(A10,Table3,2,0) B11 =VLOOKUP(A11,Table3,2,0) B12 =VLOOKUP(A12,Table3,2,0) B13 =VLOOKUP(A13,Table3,2,0) B14 =VLOOKUP(A14,Table2,2,0) B15 =VLOOKUP(A15,Table2,2,0) B16 =VLOOKUP(A16,Table2,2,0) B17 =VLOOKUP(A17,Table2,2,0) B18 =VLOOKUP(A18,Table2,2,0)
Excel tables to the web >> Excel Jeanie HTML 4
I'd Like To Make To Make Every Thing Variable In "Gray Table" So I Can Choose According To My Need I Work
Ex
1 Ranges Variables So I Can Choose The Range I Need
2 Look Up Table Variable So I Can Choose Between Tables I need
3 Color Every Range With Specific Color Through Drop Down List Color
Sheet1
A B C D E F G H I J K L M N O P Q 1 A1 C 700 Range Tables Color Table1 Table2 Table3 2 A2 C 701 First LookUp A1 A4 Table1 Blue Sky A1 C 700 A1 Z 500 A1 Y 300 3 A3 C 702 Second LookUp A5 A13 Table3 Red A2 C 701 A2 Z 501 A2 Y 301 4 A4 C 703 Third LookUp A14 A18 Table2 Yellow A3 C 702 A3 Z 502 A3 Y 302 5 A5 Y 304 A4 C 703 A4 Z 503 A4 Y 303 6 A6 Y 305 A5 C 704 A5 Z 504 A5 Y 304 7 A7 Y 306 A6 C 705 A6 Z 505 A6 Y 305 8 A8 Y 307 A7 C 706 A7 Z 506 A7 Y 306 9 A9 Y 308 A8 C 707 A8 Z 507 A8 Y 307 10 A10 Y 309 A9 C 708 A9 Z 508 A9 Y 308 11 A11 Y 310 A10 C 709 A10 Z 509 A10 Y 309 12 A12 Y 311 A11 C 710 A11 Z 510 A11 Y 310 13 A13 Y 312 A12 C 711 A12 Z 511 A12 Y 311 14 A14 Z 513 A13 C 712 A13 Z 512 A13 Y 312 15 A15 Z 514 A14 C 713 A14 Z 513 A14 Y 313 16 A16 Z 515 A15 C 714 A15 Z 514 A15 Y 314 17 A17 Z 516 A16 C 715 A16 Z 515 A16 Y 315 18 A18 Z 517 A17 C 716 A17 Z 516 A17 Y 316 19 A18 C 717 A18 Z 517 A18 Y 317 20 A19 C 718 A19 Z 518 A19 Y 318 21 A20 C 719 A20 Z519 A20 Y 319
Spreadsheet Formulas Cell Formula B1 =VLOOKUP(A1,Table1,2,0) B2 =VLOOKUP(A2,Table1,2,0) B3 =VLOOKUP(A3,Table1,2,0) B4 =VLOOKUP(A4,Table1,2,0) B5 =VLOOKUP(A5,Table3,2,0) B6 =VLOOKUP(A6,Table3,2,0) B7 =VLOOKUP(A7,Table3,2,0) B8 =VLOOKUP(A8,Table3,2,0) B9 =VLOOKUP(A9,Table3,2,0) B10 =VLOOKUP(A10,Table3,2,0) B11 =VLOOKUP(A11,Table3,2,0) B12 =VLOOKUP(A12,Table3,2,0) B13 =VLOOKUP(A13,Table3,2,0) B14 =VLOOKUP(A14,Table2,2,0) B15 =VLOOKUP(A15,Table2,2,0) B16 =VLOOKUP(A16,Table2,2,0) B17 =VLOOKUP(A17,Table2,2,0) B18 =VLOOKUP(A18,Table2,2,0)
Excel tables to the web >> Excel Jeanie HTML 4
I am trying to setup a formula from cells that are set to a drop down list. On occasions I may need all the cells filled with a description to which I use a lookup with that adds to the lookup value in the cell below it and so on. However of 5 cells that I am adding lookup values toghether, certain occasions will require that only 2 of 5 cells be filled. When I do not select a description using the dropdown list and the cell is blank the formula does not work. Below is the code I started to write which works until a cell is entered that has nothing lookup, then things go south.
=((D14*VLOOKUP(H3,Table1,4,FALSE))*G3)/VLOOKUP(H3,Table1,3,FALSE)+((D14*VLOOKUP(H4,Table1,4,FALSE))*G4)/VLOOKUP(H4,Table1,3,FALSE)
You can see I am trying to add the results of several lookups together. I run into problems when I get to a cell that does not have anything in it I get the N/A response. What do I need to do so that it takes action when there is something to lookup and acts as 0 when the cell is empty????
=((D14*VLOOKUP(H3,Table1,4,FALSE))*G3)/VLOOKUP(H3,Table1,3,FALSE)+((D14*VLOOKUP(H4,Table1,4,FALSE))*G4)/VLOOKUP(H4,Table1,3,FALSE)
You can see I am trying to add the results of several lookups together. I run into problems when I get to a cell that does not have anything in it I get the N/A response. What do I need to do so that it takes action when there is something to lookup and acts as 0 when the cell is empty????
I have this cell formula:
Code:
This code allows me to search for a record without having to place the entire record in cell A1. Also, if there is more than one record that starts with the given entry it will say "Need More".
However I would like to have the results of the entry if there are two possible records not just one.
For example, in cells A3 and A4 there would be formulas like this:
A3 = =if(countif(Table1,A1&"*")=2,VLOOKUP(A1&"*",Table1,1,First)),"N/A")
A4 =if(countif(Table1,A1&"*")=2,VLOOKUP(A1&"*",Table1,1,Second)),"N/A")
Did I explain that well enough. Please ask any questions. I am looking forward to this answer!
Thanks!
Nicole
Code:
=if(countif(Table1,A1&"*")>1,"Need More",VLOOKUP(A1&"*",Table1,1,FALSE))
This code allows me to search for a record without having to place the entire record in cell A1. Also, if there is more than one record that starts with the given entry it will say "Need More".
However I would like to have the results of the entry if there are two possible records not just one.
For example, in cells A3 and A4 there would be formulas like this:
A3 = =if(countif(Table1,A1&"*")=2,VLOOKUP(A1&"*",Table1,1,First)),"N/A")
A4 =if(countif(Table1,A1&"*")=2,VLOOKUP(A1&"*",Table1,1,Second)),"N/A")
Did I explain that well enough. Please ask any questions. I am looking forward to this answer!
Thanks!
Nicole
Hi there, can anybody give me a better way of constructing this formula?
Code:
I want to sum several vlookups and having to repeat the whole vlookup formula for each one seems inefficient to me.
Code:
=SUM(VLOOKUP(4040,Table1[#All],16,FALSE)+VLOOKUP(1510,Table1[#All],16,FALSE))
I want to sum several vlookups and having to repeat the whole vlookup formula for each one seems inefficient to me.
I would like change the Named Range in vlookup formulas using something like indirect reference. My formula is:
=vlookup(b4, Table1,4)*b4vlookup(b4,Table1,2)
the named range need to be changed dynamically. there are 14 tables. Table1,Table2,Table3...
Can anyone help?
Hi, I need a way to select a discontinuous range in VBA with these circumstances:
The data I'm working with is grouped into 5 by 5 regions, each region with a header above it, stacked one above the other with an empty line between each
There are also several "columns" of these tables, although I could put them on separate sheets if that simplifies the code
What I want to do is find the maximum value within a given location in each table. By this, I mean that I will be comparing the value in 1st row in the 1st column of the 1st table with the value in the 1st row in the 1st column of the 2nd table.
I can't seem to upload files here at work, but the tables look something like...
Table1
1 2 3 4 5
6 7 8 9 0
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5
Table2
6 7 0 9 0
1 2 3 4 5
6 7 0 9 0
6 7 0 9 0
6 7 0 9 0
With the end product being
Table2 Table2 Table1 Table2 Table2
Table1 Table1 Table1 Table1 Table1
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2
Thanks in advance, sorry for the mess
The data I'm working with is grouped into 5 by 5 regions, each region with a header above it, stacked one above the other with an empty line between each
There are also several "columns" of these tables, although I could put them on separate sheets if that simplifies the code
What I want to do is find the maximum value within a given location in each table. By this, I mean that I will be comparing the value in 1st row in the 1st column of the 1st table with the value in the 1st row in the 1st column of the 2nd table.
I can't seem to upload files here at work, but the tables look something like...
Table1
1 2 3 4 5
6 7 8 9 0
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5
Table2
6 7 0 9 0
1 2 3 4 5
6 7 0 9 0
6 7 0 9 0
6 7 0 9 0
With the end product being
Table2 Table2 Table1 Table2 Table2
Table1 Table1 Table1 Table1 Table1
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2
Thanks in advance, sorry for the mess
hello!
i am trying to transfer data from one table to another using vlookup function.
If the check box is ticked, this means that this invoice has been paid and returns a TRUE; otherwise, it remains blank or simply indicates a FALSE.
This is the table I am trying to populate to summarize all outstanding items. Currently I am using a vlookup function to do this. The problem is if an invoice for the quarter has been paid in Table1, i have to manually delete the formula in Table2 so as not to show it as outstanding.
=IF(ISNA(VLOOKUP(A4,Sheet1!A4:Q6,2,0)),0,(VLOOKUP(A4,Sheet1!A4:Q6,2,0)))
I know I can use the TRUE, FALSE and BLANK status in Table1 to make this work more efficient. However, when I tried to do a multiple vlookup function nested in IF, it's not picking any numbers at all.
I thought I'd consult your guidance on this. Any assistance you can extend is truly appreciated.
annais
i am trying to transfer data from one table to another using vlookup function.
If the check box is ticked, this means that this invoice has been paid and returns a TRUE; otherwise, it remains blank or simply indicates a FALSE.
This is the table I am trying to populate to summarize all outstanding items. Currently I am using a vlookup function to do this. The problem is if an invoice for the quarter has been paid in Table1, i have to manually delete the formula in Table2 so as not to show it as outstanding.
=IF(ISNA(VLOOKUP(A4,Sheet1!A4:Q6,2,0)),0,(VLOOKUP(A4,Sheet1!A4:Q6,2,0)))
I know I can use the TRUE, FALSE and BLANK status in Table1 to make this work more efficient. However, when I tried to do a multiple vlookup function nested in IF, it's not picking any numbers at all.
I thought I'd consult your guidance on this. Any assistance you can extend is truly appreciated.
annais
Hello,
I am trying to apply a COUNTIF function to only visible cells in a selected range. Here's the regular COUNTIF formula that I'm working with.
=COUNTIF(INDEX(TABLE1,0,MATCH($B8,TABLE1[#Headers],0)),VLOOKUP(18,$AF$7:$AG$28,2,FALSE))
Column B in the current sheet contains dates, which are also present in the header row in TABLE1. This countif formula does the following:
1) Uses INDEX(TABLE1,0,MATCH($B8,TABLE1[#Headers],0)) to define the range as the column in TABLE1 with a header that matches the date in $B8.
2) Uses VLOOKUP(18,$AF$7:$AG$28,2,FALSE) to define the search term through a VLOOKUP of a small table in the current sheet.
The formula works just fine. However, I'm unsure of how to get it to ignore hidden rows. I know that you can do this with simpler functions using SUBTOTAL, and I have a VBA solution that works but is extremely slow. Any ideas?
Thanks for your help.
How do I write this as one formula.
if(C1=1,(Vlookup(D8,Table1,2,False),"") OR
if(C1=2,(Vlookup(D8,Table2,2,False),"") OR
if(C1=3,(Vlookup(D8,Table3,2,False),"").
I have spent so much time trying to come up with correct syntax or formula with no success. Thank you very much.
if(C1=1,(Vlookup(D8,Table1,2,False),"") OR
if(C1=2,(Vlookup(D8,Table2,2,False),"") OR
if(C1=3,(Vlookup(D8,Table3,2,False),"").
I have spent so much time trying to come up with correct syntax or formula with no success. Thank you very much.
Is there a way to block the #N/A type stuff in a field until someone puts a value to lookup into it? Right now I'm entering stuff into the 2nd column and the surrounding columns are using it to look up their data, but as you can see its pretty messy until someone puts something in the 2nd column.
Is there some if statement or something I can use to make it just stay blank until someone enters a badge number into the 2nd column?
Roster
* B C D E 3 E14 283 Pierce 651 4 T13 248 Kissinger 645 5 #N/A * #N/A #N/A
Spreadsheet Formulas Cell Formula B3 =VLOOKUP(C3,vlookup!$A$2:$F$86,2,FALSE) D3 =VLOOKUP(C3,vlookup!$A$2:$F$86,3,FALSE) E3 =VLOOKUP(C3,vlookup!$A$2:$F$86,4,FALSE) B4 =VLOOKUP(C4,vlookup!$A$2:$F$86,2,FALSE) D4 =VLOOKUP(C4,vlookup!$A$2:$F$86,3,FALSE) E4 =VLOOKUP(C4,vlookup!$A$2:$F$86,4,FALSE) B5 =VLOOKUP(C5,vlookup!$A$2:$F$86,2,FALSE) D5 =VLOOKUP(C5,vlookup!$A$2:$F$86,3,FALSE) E5 =VLOOKUP(C5,vlookup!$A$2:$F$86,4,FALSE)
Excel tables to the web >> Excel Jeanie HTML 4
Thanks! btw this excel html jeanie thing is nice
Is there some if statement or something I can use to make it just stay blank until someone enters a badge number into the 2nd column?
Roster
* B C D E 3 E14 283 Pierce 651 4 T13 248 Kissinger 645 5 #N/A * #N/A #N/A
Spreadsheet Formulas Cell Formula B3 =VLOOKUP(C3,vlookup!$A$2:$F$86,2,FALSE) D3 =VLOOKUP(C3,vlookup!$A$2:$F$86,3,FALSE) E3 =VLOOKUP(C3,vlookup!$A$2:$F$86,4,FALSE) B4 =VLOOKUP(C4,vlookup!$A$2:$F$86,2,FALSE) D4 =VLOOKUP(C4,vlookup!$A$2:$F$86,3,FALSE) E4 =VLOOKUP(C4,vlookup!$A$2:$F$86,4,FALSE) B5 =VLOOKUP(C5,vlookup!$A$2:$F$86,2,FALSE) D5 =VLOOKUP(C5,vlookup!$A$2:$F$86,3,FALSE) E5 =VLOOKUP(C5,vlookup!$A$2:$F$86,4,FALSE)
Excel tables to the web >> Excel Jeanie HTML 4
Thanks! btw this excel html jeanie thing is nice
Hello All!
A newbie here. I appreciate any help. I am actually quite good in a # of areas  but not in VLookups. I basically have two tabs with similar lists of customers and misc info. One list has addresses and the other doesn't. Each customer has a customer # reflected on both tabbed page. My formula wld basically be: if customer # on tab 1 ='s customer # on tab 2  take address from tab 1 and place in customer's row on tab 2.
Thanks 4 anyone helping me with this. I have already entitled the data in tab 1 (where the address is) "Table1."
A sample I was using to try to duplicate is as follows: (It however has a little more info in it which is confusing me) 
=IF(VLOOKUP(D3,Table1,1)=D3,IF(VLOOKUP(D3,Table1,2)=0,"DNP",VLOOKUP(D3,Table1,2)&IF(LEN(VLOOKUP(D3,Table1,2))=7,"",""&VLOOKUP(C3,table2,2))),"Missing GL Number")
The CUST# is in Column B in Table 1 (1st Tab) and in Column E in the target table. The address is in column I. I realize I will be putting the VLookup formula in column I of the target table.
Also, if it is fairly easy if someone cld interpret the above sample  I'd be very grateful.
Again  thank you 4 taking the time to help. I really appreciate it. Marty
A newbie here. I appreciate any help. I am actually quite good in a # of areas  but not in VLookups. I basically have two tabs with similar lists of customers and misc info. One list has addresses and the other doesn't. Each customer has a customer # reflected on both tabbed page. My formula wld basically be: if customer # on tab 1 ='s customer # on tab 2  take address from tab 1 and place in customer's row on tab 2.
Thanks 4 anyone helping me with this. I have already entitled the data in tab 1 (where the address is) "Table1."
A sample I was using to try to duplicate is as follows: (It however has a little more info in it which is confusing me) 
=IF(VLOOKUP(D3,Table1,1)=D3,IF(VLOOKUP(D3,Table1,2)=0,"DNP",VLOOKUP(D3,Table1,2)&IF(LEN(VLOOKUP(D3,Table1,2))=7,"",""&VLOOKUP(C3,table2,2))),"Missing GL Number")
The CUST# is in Column B in Table 1 (1st Tab) and in Column E in the target table. The address is in column I. I realize I will be putting the VLookup formula in column I of the target table.
Also, if it is fairly easy if someone cld interpret the above sample  I'd be very grateful.
Again  thank you 4 taking the time to help. I really appreciate it. Marty
Good day,
I hope that I am not asking too much with this one.
I have a workbook with 3 worksheets, 2 containing data and 1 used as a search and display sheet. I enter information into a cell, press enter and the relevant data is displayed in various cells.
I would like to create a userform to do the job of the search/display sheet, this is mainly to make it look more professional!
What do I need to add to the userform? Textboxes, Labels, Command Buttons etc, etc.
How would I go about transferring/copying the following data into a userform?
Thanks
Ak
A1 B1 C1 D1 E1 P1 =TODAY() =NOW() A3 =COUNT(Master!A4:A301) Enter Product code below P N M E F S W S S G R A5 =VLOOKUP(C5,Master,7,FALSE) =VLOOKUP(C5,Master,8,FALSE) =VLOOKUP(C5,Master,9,FALSE) =VLOOKUP(C5,Master,10,FALSE) =VLOOKUP(C5,Master,11,FALSE) =VLOOKUP(C5,Master,12,FALSE) =VLOOKUP(C5,Master,13,FALSE) =VLOOKUP(C5,Master,14,FALSE) =VLOOKUP(C5,Master,15,FALSE) =VLOOKUP(C5,Master,16,FALSE) =VLOOKUP(C5,Master,17,FALSE) B M O M C M G H L S P A7 Product details =VLOOKUP(C5,Master,18,FALSE) =VLOOKUP(C5,Master,19,FALSE) =VLOOKUP(C5,Master,20,FALSE) =VLOOKUP(C5,Master,21,FALSE) =VLOOKUP(C5,Master,22,FALSE) =VLOOKUP(C5,Master,23,FALSE) =VLOOKUP(C5,Master,24,FALSE) =VLOOKUP(C5,Master,25,FALSE) =VLOOKUP(C5,Master,26,FALSE) =VLOOKUP(C5,Master,27,FALSE) =VLOOKUP(C5,Master,28,FALSE) Des A9 =IF(ISNA(VLOOKUP(C5,Master,2,FALSE)),"Incorrect Product code",VLOOKUP(C5,Master,2,FALSE)) =VLOOKUP(C5,Master,29,FALSE) =VLOOKUP(C5,Master,30,FALSE) =VLOOKUP(C5,Master,31,FALSE) =VLOOKUP(C5,Master,32,FALSE) =VLOOKUP(C5,Master,33,FALSE) =VLOOKUP(C5,Master,34,FALSE) =VLOOKUP(C5,Master,35,FALSE) =VLOOKUP(C5,Master,36,FALSE) =VLOOKUP(C5,Master,37,FALSE) =VLOOKUP(C5,Master,38,FALSE) AL =VLOOKUP(C5,Master,39,FALSE) =VLOOKUP(C5,Master,40,FALSE) =VLOOKUP(C5,Master,41,FALSE) =VLOOKUP(C5,Master,42,FALSE) =VLOOKUP(C5,Master,43,FALSE) =VLOOKUP(C5,Master,44,FALSE) =VLOOKUP(C5,Master,45,FALSE) =VLOOKUP(C5,Master,46,FALSE) =VLOOKUP(C5,Master,47,FALSE) =VLOOKUP(C5,Master,48,FALSE) A11 =VLOOKUP(C5,Master,3,FALSE) =VLOOKUP(C5,Master,49,FALSE) =VLOOKUP(C5,Master,50,FALSE) =VLOOKUP(C5,Master,51,FALSE) =VLOOKUP(C5,Master,52,FALSE) =VLOOKUP(C5,Master,53,FALSE) =VLOOKUP(C5,Master,54,FALSE) =VLOOKUP(C5,Master,55,FALSE) =VLOOKUP(C5,Master,56,FALSE) =VLOOKUP(C5,Master,57,FALSE) =VLOOKUP(C5,Master,58,FALSE) Wa =VLOOKUP(C5,Master,59,FALSE) =VLOOKUP(C5,Master,60,FALSE) =VLOOKUP(C5,Master,61,FALSE) =VLOOKUP(C5,Master,62,FALSE) =VLOOKUP(C5,Master,63,FALSE) =VLOOKUP(C5,Master,64,FALSE) =VLOOKUP(C5,Master,65,FALSE) =VLOOKUP(C5,Master,66,FALSE) =VLOOKUP(C5,Master,67,FALSE) =VLOOKUP(C5,Master,68,FALSE) A13 =VLOOKUP(C5,Master,4,FALSE) =VLOOKUP(C5,Master,69,FALSE) =VLOOKUP(C5,Master,70,FALSE) =VLOOKUP(C5,Master,71,FALSE) =VLOOKUP(C5,Master,72,FALSE) =VLOOKUP(C5,Master,73,FALSE) =VLOOKUP(C5,Master,74,FALSE) =VLOOKUP(C5,Master,75,FALSE) =VLOOKUP(C5,Master,76,FALSE) =VLOOKUP(C5,Master,77,FALSE) =VLOOKUP(C5,Master,78,FALSE) Ar =VLOOKUP(C5,Master,79,FALSE) =VLOOKUP(C5,Master,80,FALSE) =VLOOKUP(C5,Master,81,FALSE) =VLOOKUP(C5,Master,82,FALSE) =VLOOKUP(C5,Master,83,FALSE) =VLOOKUP(C5,Master,84,FALSE) =VLOOKUP(C5,Master,85,FALSE) =VLOOKUP(C5,Master,86,FALSE) =VLOOKUP(C5,Master,87,FALSE) =VLOOKUP(C5,Master,88,FALSE) A15 =VLOOKUP(C5,Master,5,FALSE) =VLOOKUP(C5,Master,89,FALSE) =VLOOKUP(C5,Master,90,FALSE) =VLOOKUP(C5,Master,91,FALSE) =VLOOKUP(C5,Master,92,FALSE) =VLOOKUP(C5,Master,93,FALSE) =VLOOKUP(C5,Master,94,FALSE) =VLOOKUP(C5,Master,95,FALSE) A17 =COUNTA(Ingredients!A4:A200) =COUNTIF(Master!C4:C302,"Yes") Enter Part code below =(C19) Comprises A19 =VLOOKUP(C19,ing,3,FALSE) =VLOOKUP(C19,ing,4,FALSE) =VLOOKUP(C19,ing,5,FALSE) =VLOOKUP(C19,ing,6,FALSE) =VLOOKUP(C19,ing,7,FALSE) Des =VLOOKUP(C19,ing,8,FALSE) =VLOOKUP(C19,ing,9,FALSE) =VLOOKUP(C19,ing,10,FALSE) =VLOOKUP(C19,ing,11,FALSE) =VLOOKUP(C19,ing,12,FALSE) A21 =IF(ISNA(VLOOKUP(C19,ing,2,FALSE)),"Incorrect Part code",VLOOKUP(C19,ing,2,FALSE)) =VLOOKUP(C19,ing,13,FALSE) =VLOOKUP(C19,ing,14,FALSE) =VLOOKUP(C19,ing,15,FALSE) =VLOOKUP(C19,ing,16,FALSE) =VLOOKUP(C19,ing,17,FALSE) A23 E23 P23
I hope that I am not asking too much with this one.
I have a workbook with 3 worksheets, 2 containing data and 1 used as a search and display sheet. I enter information into a cell, press enter and the relevant data is displayed in various cells.
I would like to create a userform to do the job of the search/display sheet, this is mainly to make it look more professional!
What do I need to add to the userform? Textboxes, Labels, Command Buttons etc, etc.
How would I go about transferring/copying the following data into a userform?
Thanks
Ak
A1 B1 C1 D1 E1 P1 =TODAY() =NOW() A3 =COUNT(Master!A4:A301) Enter Product code below P N M E F S W S S G R A5 =VLOOKUP(C5,Master,7,FALSE) =VLOOKUP(C5,Master,8,FALSE) =VLOOKUP(C5,Master,9,FALSE) =VLOOKUP(C5,Master,10,FALSE) =VLOOKUP(C5,Master,11,FALSE) =VLOOKUP(C5,Master,12,FALSE) =VLOOKUP(C5,Master,13,FALSE) =VLOOKUP(C5,Master,14,FALSE) =VLOOKUP(C5,Master,15,FALSE) =VLOOKUP(C5,Master,16,FALSE) =VLOOKUP(C5,Master,17,FALSE) B M O M C M G H L S P A7 Product details =VLOOKUP(C5,Master,18,FALSE) =VLOOKUP(C5,Master,19,FALSE) =VLOOKUP(C5,Master,20,FALSE) =VLOOKUP(C5,Master,21,FALSE) =VLOOKUP(C5,Master,22,FALSE) =VLOOKUP(C5,Master,23,FALSE) =VLOOKUP(C5,Master,24,FALSE) =VLOOKUP(C5,Master,25,FALSE) =VLOOKUP(C5,Master,26,FALSE) =VLOOKUP(C5,Master,27,FALSE) =VLOOKUP(C5,Master,28,FALSE) Des A9 =IF(ISNA(VLOOKUP(C5,Master,2,FALSE)),"Incorrect Product code",VLOOKUP(C5,Master,2,FALSE)) =VLOOKUP(C5,Master,29,FALSE) =VLOOKUP(C5,Master,30,FALSE) =VLOOKUP(C5,Master,31,FALSE) =VLOOKUP(C5,Master,32,FALSE) =VLOOKUP(C5,Master,33,FALSE) =VLOOKUP(C5,Master,34,FALSE) =VLOOKUP(C5,Master,35,FALSE) =VLOOKUP(C5,Master,36,FALSE) =VLOOKUP(C5,Master,37,FALSE) =VLOOKUP(C5,Master,38,FALSE) AL =VLOOKUP(C5,Master,39,FALSE) =VLOOKUP(C5,Master,40,FALSE) =VLOOKUP(C5,Master,41,FALSE) =VLOOKUP(C5,Master,42,FALSE) =VLOOKUP(C5,Master,43,FALSE) =VLOOKUP(C5,Master,44,FALSE) =VLOOKUP(C5,Master,45,FALSE) =VLOOKUP(C5,Master,46,FALSE) =VLOOKUP(C5,Master,47,FALSE) =VLOOKUP(C5,Master,48,FALSE) A11 =VLOOKUP(C5,Master,3,FALSE) =VLOOKUP(C5,Master,49,FALSE) =VLOOKUP(C5,Master,50,FALSE) =VLOOKUP(C5,Master,51,FALSE) =VLOOKUP(C5,Master,52,FALSE) =VLOOKUP(C5,Master,53,FALSE) =VLOOKUP(C5,Master,54,FALSE) =VLOOKUP(C5,Master,55,FALSE) =VLOOKUP(C5,Master,56,FALSE) =VLOOKUP(C5,Master,57,FALSE) =VLOOKUP(C5,Master,58,FALSE) Wa =VLOOKUP(C5,Master,59,FALSE) =VLOOKUP(C5,Master,60,FALSE) =VLOOKUP(C5,Master,61,FALSE) =VLOOKUP(C5,Master,62,FALSE) =VLOOKUP(C5,Master,63,FALSE) =VLOOKUP(C5,Master,64,FALSE) =VLOOKUP(C5,Master,65,FALSE) =VLOOKUP(C5,Master,66,FALSE) =VLOOKUP(C5,Master,67,FALSE) =VLOOKUP(C5,Master,68,FALSE) A13 =VLOOKUP(C5,Master,4,FALSE) =VLOOKUP(C5,Master,69,FALSE) =VLOOKUP(C5,Master,70,FALSE) =VLOOKUP(C5,Master,71,FALSE) =VLOOKUP(C5,Master,72,FALSE) =VLOOKUP(C5,Master,73,FALSE) =VLOOKUP(C5,Master,74,FALSE) =VLOOKUP(C5,Master,75,FALSE) =VLOOKUP(C5,Master,76,FALSE) =VLOOKUP(C5,Master,77,FALSE) =VLOOKUP(C5,Master,78,FALSE) Ar =VLOOKUP(C5,Master,79,FALSE) =VLOOKUP(C5,Master,80,FALSE) =VLOOKUP(C5,Master,81,FALSE) =VLOOKUP(C5,Master,82,FALSE) =VLOOKUP(C5,Master,83,FALSE) =VLOOKUP(C5,Master,84,FALSE) =VLOOKUP(C5,Master,85,FALSE) =VLOOKUP(C5,Master,86,FALSE) =VLOOKUP(C5,Master,87,FALSE) =VLOOKUP(C5,Master,88,FALSE) A15 =VLOOKUP(C5,Master,5,FALSE) =VLOOKUP(C5,Master,89,FALSE) =VLOOKUP(C5,Master,90,FALSE) =VLOOKUP(C5,Master,91,FALSE) =VLOOKUP(C5,Master,92,FALSE) =VLOOKUP(C5,Master,93,FALSE) =VLOOKUP(C5,Master,94,FALSE) =VLOOKUP(C5,Master,95,FALSE) A17 =COUNTA(Ingredients!A4:A200) =COUNTIF(Master!C4:C302,"Yes") Enter Part code below =(C19) Comprises A19 =VLOOKUP(C19,ing,3,FALSE) =VLOOKUP(C19,ing,4,FALSE) =VLOOKUP(C19,ing,5,FALSE) =VLOOKUP(C19,ing,6,FALSE) =VLOOKUP(C19,ing,7,FALSE) Des =VLOOKUP(C19,ing,8,FALSE) =VLOOKUP(C19,ing,9,FALSE) =VLOOKUP(C19,ing,10,FALSE) =VLOOKUP(C19,ing,11,FALSE) =VLOOKUP(C19,ing,12,FALSE) A21 =IF(ISNA(VLOOKUP(C19,ing,2,FALSE)),"Incorrect Part code",VLOOKUP(C19,ing,2,FALSE)) =VLOOKUP(C19,ing,13,FALSE) =VLOOKUP(C19,ing,14,FALSE) =VLOOKUP(C19,ing,15,FALSE) =VLOOKUP(C19,ing,16,FALSE) =VLOOKUP(C19,ing,17,FALSE) A23 E23 P23
I have a worksheet with the following formula
=IF(ISNA(VLOOKUP(G12,'F:\My Documents\PERep3&4.xls'!Table1,2,FALSE)),0,VLOOKUP(G12,'F:\My Documents\PERep3&4.xls'!Table1,2,FALSE))
I have tried using shift + F5 to replace the following, but cannot get it to work
1) 'F:\My Documents with 'c:\My Documents
2) PERep3&4 with liverep3&4
It would be appreciated if you could assist
Howard
=IF(ISNA(VLOOKUP(G12,'F:\My Documents\PERep3&4.xls'!Table1,2,FALSE)),0,VLOOKUP(G12,'F:\My Documents\PERep3&4.xls'!Table1,2,FALSE))
I have tried using shift + F5 to replace the following, but cannot get it to work
1) 'F:\My Documents with 'c:\My Documents
2) PERep3&4 with liverep3&4
It would be appreciated if you could assist
Howard
Here is my attempt.
=IF(A1="table1",VLOOKUP(B4,Matrix.xls!table1,2,1)*B6),IF(A1="table2",VLOOKUP(B4,Matrix.xls!table2,2,1)*B6),IF(A1="table3",VLOOKUP(B4,Matrix.xls!table3,2,1)*B6),IF(A1="table4",VLOOKUP(B4,Matrix.xls!table4,2,1)*B6),""))))
I have a validation in cell A1 with a dropdown list, table1,table2,table3,table4.
I want to be able to select that and then pull data from the matrix sheet based on the ranges with the same name using a vlookup returning the 2nd column.
Is this possible using this formula? Think I may be barking up the wrong tree here.
Thank you
=IF(A1="table1",VLOOKUP(B4,Matrix.xls!table1,2,1)*B6),IF(A1="table2",VLOOKUP(B4,Matrix.xls!table2,2,1)*B6),IF(A1="table3",VLOOKUP(B4,Matrix.xls!table3,2,1)*B6),IF(A1="table4",VLOOKUP(B4,Matrix.xls!table4,2,1)*B6),""))))
I have a validation in cell A1 with a dropdown list, table1,table2,table3,table4.
I want to be able to select that and then pull data from the matrix sheet based on the ranges with the same name using a vlookup returning the 2nd column.
Is this possible using this formula? Think I may be barking up the wrong tree here.
Thank you
Hi All,
I am trying to add two Time values together from an imported report. Both formats are in HH:MM:SS. However I get a value error when columns b does not go over a an hour i.e only has a MM:SS value. Does anyone know how to get around this.
Secondly is there anyway to show values that are over 24 hours in a sum, i.e. row 24 should be 25 hours, 5 mins and 41 seconds but shows as 02:05:41.
Collection
I J K 23 3:51:57 :23:45 #VALUE! 24 22:34:27 03:31:14 02:05:41 25 4:39:24 :11:43 #VALUE! 26 6:29:11 :18:32 #VALUE! 27 11:17:56 02:57:12 14:15:08
Spreadsheet Formulas Cell Formula I23 =IF(ISNA (VLOOKUP (B23,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B23,'EWS 01'!A:N,4,FALSE) ) J23 =IF(ISNA (VLOOKUP (B23,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B23,'EWS 01'!A:N,6,FALSE) ) K23 =J23+I23 I24 =IF(ISNA (VLOOKUP (B24,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B24,'EWS 01'!A:N,4,FALSE) ) J24 =IF(ISNA (VLOOKUP (B24,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B24,'EWS 01'!A:N,6,FALSE) ) K24 =J24+I24 I25 =IF(ISNA (VLOOKUP (B25,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B25,'EWS 01'!A:N,4,FALSE) ) J25 =IF(ISNA (VLOOKUP (B25,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B25,'EWS 01'!A:N,6,FALSE) ) K25 =J25+I25 I26 =IF(ISNA (VLOOKUP (B26,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B26,'EWS 01'!A:N,4,FALSE) ) J26 =IF(ISNA (VLOOKUP (B26,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B26,'EWS 01'!A:N,6,FALSE) ) K26 =J26+I26 I27 =IF(ISNA (VLOOKUP (B27,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B27,'EWS 01'!A:N,4,FALSE) ) J27 =IF(ISNA (VLOOKUP (B27,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B27,'EWS 01'!A:N,6,FALSE) ) K27 =J27+I27
Excel tables to the web >> Excel Jeanie HTML 4
I am trying to add two Time values together from an imported report. Both formats are in HH:MM:SS. However I get a value error when columns b does not go over a an hour i.e only has a MM:SS value. Does anyone know how to get around this.
Secondly is there anyway to show values that are over 24 hours in a sum, i.e. row 24 should be 25 hours, 5 mins and 41 seconds but shows as 02:05:41.
Collection
I J K 23 3:51:57 :23:45 #VALUE! 24 22:34:27 03:31:14 02:05:41 25 4:39:24 :11:43 #VALUE! 26 6:29:11 :18:32 #VALUE! 27 11:17:56 02:57:12 14:15:08
Spreadsheet Formulas Cell Formula I23 =IF(ISNA (VLOOKUP (B23,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B23,'EWS 01'!A:N,4,FALSE) ) J23 =IF(ISNA (VLOOKUP (B23,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B23,'EWS 01'!A:N,6,FALSE) ) K23 =J23+I23 I24 =IF(ISNA (VLOOKUP (B24,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B24,'EWS 01'!A:N,4,FALSE) ) J24 =IF(ISNA (VLOOKUP (B24,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B24,'EWS 01'!A:N,6,FALSE) ) K24 =J24+I24 I25 =IF(ISNA (VLOOKUP (B25,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B25,'EWS 01'!A:N,4,FALSE) ) J25 =IF(ISNA (VLOOKUP (B25,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B25,'EWS 01'!A:N,6,FALSE) ) K25 =J25+I25 I26 =IF(ISNA (VLOOKUP (B26,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B26,'EWS 01'!A:N,4,FALSE) ) J26 =IF(ISNA (VLOOKUP (B26,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B26,'EWS 01'!A:N,6,FALSE) ) K26 =J26+I26 I27 =IF(ISNA (VLOOKUP (B27,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B27,'EWS 01'!A:N,4,FALSE) ) J27 =IF(ISNA (VLOOKUP (B27,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B27,'EWS 01'!A:N,6,FALSE) ) K27 =J27+I27
Excel tables to the web >> Excel Jeanie HTML 4
Below you'll see the long sheet. I highlighted the cells with issues. Seems my VLOOKUP is not pulling correct info. Help please.
Excel 2003 I J K L M N O P 2 TRIP NUMBER IN courier 3 GUA UPS Counts Trips Name 4 S27 UPS FedEx 0 10 United 5 113 NOW UPS 0 15 United 6 3 NOW NOW 0 20 United 7 134 NOW US MAIL 0 21 United 8 113 NOW Prestige 0 23 United 9 316 NOW #N/A 0 24 United 10 323 NOW 25 United 11 121 NOW 30 United 12 213 NOW 33 United 13 25 NOW 34 United 14 102 NOW 35 United 15 PAY #N/A 36 United 16 528 NOW 101 NOW 17 109 NOW 102 NOW 18 UAM UPS 103 NOW 19 47 NOW 104 NOW 20 AKR Premier 105 NOW 21 UAM UPS 106 NOW 22 UAM UPS 107 NOW 23 S27 UPS 108 NOW 24 30A NOW 109 NOW 25 25A NOW 110 NOW 26 AKR Premier 111 NOW 27 48 NOW 112 NOW 28 E26 USCARGO 113 NOW 29 FPM FedEx 114 NOW 30 CLE Premier 115 NOW 31 20 NOW 12 USMAIL 32 30A NOW 120 NOW 33 15 NOW 121 NOW 34 25A NOW 122 NOW 35 CLE Premier 123 NOW 36 34 NOW 124 NOW 37 44 NOW 125 NOW 38 33 NOW 126 NOW 39 24 NOW 127 NOW 40 CLE Premier 130 NOW 41 24 NOW 131 NOW 42 33 NOW 134 NOW 43 CLE Premier 135 NOW 44 FAM FedEx 136 NOW 45 FAM FedEx 137 NOW 46 UAM UPS 138 NOW 47 30A NOW 139 NOW 48 S27 UPS 140 NOW 49 FAM FedEx 143 NOW 50 CLE Premier 144 NOW 51 S27 UPS 145 NOW 52 FAM FedEx 15A United 53 323 NOW 202 NOW 54 COL Premier 203 NOW 55 CLE Premier 204 NOW 56 CLE Premier 205 NOW 57 GUA UPS 206 NOW 58 106 NOW 207 NOW 59 MUP #N/A 208 NOW 60 CLE Premier 209 NOW 61 PAY #N/A 210 NOW 62 CLE Premier 211 NOW 63 CLE Premier 212 NOW 64 AKR Premier 213 NOW 65 214 NOW 214 NOW 66 PAY #N/A 21A United 67 PAY #N/A 25A United 68 FAM FedEx 26 USCARGO 69 GUA UPS 301 NOW 70 CLE Premier 302 NOW 71 CLE Premier 303 NOW 72 FAM FedEx 304 NOW 73 23 NOW 305 NOW 74 CLE Premier 306 NOW 75 COL Premier 307 NOW 76 S27 UPS 309 NOW 77 CLE Premier 30A United 78 COL Premier 310 NOW Oct2010
Worksheet Formulas Cell Formula J3 =VLOOKUP( LEFT( I3 )&"*",$O$3:$P$104,2,FALSE ) J4 =VLOOKUP( LEFT( I4 )&"*",$O$3:$P$104,2,FALSE ) J5 =VLOOKUP( LEFT( I5 )&"*",$O$3:$P$104,2,FALSE ) J6 =VLOOKUP( LEFT( I6 )&"*",$O$3:$P$104,2,FALSE ) J7 =VLOOKUP( LEFT( I7 )&"*",$O$3:$P$104,2,FALSE ) J8 =VLOOKUP( LEFT( I8 )&"*",$O$3:$P$104,2,FALSE ) J9 =VLOOKUP( LEFT( I9 )&"*",$O$3:$P$104,2,FALSE ) J10 =VLOOKUP( LEFT( I10 )&"*",$O$3:$P$104,2,FALSE ) J11 =VLOOKUP( LEFT( I11 )&"*",$O$3:$P$104,2,FALSE ) J12 =VLOOKUP( LEFT( I12 )&"*",$O$3:$P$104,2,FALSE ) J13 =VLOOKUP( LEFT( I13 )&"*",$O$3:$P$104,2,FALSE ) J14 =VLOOKUP( LEFT( I14 )&"*",$O$3:$P$104,2,FALSE ) J15 =VLOOKUP( LEFT( I15 )&"*",$O$3:$P$104,2,FALSE ) J16 =VLOOKUP( LEFT( I16 )&"*",$O$3:$P$104,2,FALSE ) J17 =VLOOKUP( LEFT( I17 )&"*",$O$3:$P$104,2,FALSE ) J18 =VLOOKUP( LEFT( I18 )&"*",$O$3:$P$104,2,FALSE ) J19 =VLOOKUP( LEFT( I19 )&"*",$O$3:$P$104,2,FALSE ) J20 =VLOOKUP( LEFT( I20 )&"*",$O$3:$P$104,2,FALSE ) J21 =VLOOKUP( LEFT( I21 )&"*",$O$3:$P$104,2,FALSE ) J22 =VLOOKUP( LEFT( I22 )&"*",$O$3:$P$104,2,FALSE ) J23 =VLOOKUP( LEFT( I23 )&"*",$O$3:$P$104,2,FALSE ) J24 =VLOOKUP( LEFT( I24 )&"*",$O$3:$P$104,2,FALSE ) J25 =VLOOKUP( LEFT( I25 )&"*",$O$3:$P$104,2,FALSE ) J26 =VLOOKUP( LEFT( I26 )&"*",$O$3:$P$104,2,FALSE ) J27 =VLOOKUP( LEFT( I27 )&"*",$O$3:$P$104,2,FALSE ) J28 =VLOOKUP( LEFT( I28 )&"*",$O$3:$P$104,2,FALSE ) J29 =VLOOKUP( LEFT( I29 )&"*",$O$3:$P$104,2,FALSE ) J30 =VLOOKUP( LEFT( I30 )&"*",$O$3:$P$104,2,FALSE ) J31 =VLOOKUP( LEFT( I31 )&"*",$O$3:$P$104,2,FALSE ) J32 =VLOOKUP( LEFT( I32 )&"*",$O$3:$P$104,2,FALSE ) J33 =VLOOKUP( LEFT( I33 )&"*",$O$3:$P$104,2,FALSE ) J34 =VLOOKUP( LEFT( I34 )&"*",$O$3:$P$104,2,FALSE ) J35 =VLOOKUP( LEFT( I35 )&"*",$O$3:$P$104,2,FALSE ) J36 =VLOOKUP( LEFT( I36 )&"*",$O$3:$P$104,2,FALSE ) J37 =VLOOKUP( LEFT( I37 )&"*",$O$3:$P$104,2,FALSE ) J38 =VLOOKUP( LEFT( I38 )&"*",$O$3:$P$104,2,FALSE ) J39 =VLOOKUP( LEFT( I39 )&"*",$O$3:$P$104,2,FALSE ) J40 =VLOOKUP( LEFT( I40 )&"*",$O$3:$P$104,2,FALSE ) J41 =VLOOKUP( LEFT( I41 )&"*",$O$3:$P$104,2,FALSE ) J42 =VLOOKUP( LEFT( I42 )&"*",$O$3:$P$104,2,FALSE ) J43 =VLOOKUP( LEFT( I43 )&"*",$O$3:$P$104,2,FALSE ) J44 =VLOOKUP( LEFT( I44 )&"*",$O$3:$P$104,2,FALSE ) J45 =VLOOKUP( LEFT( I45 )&"*",$O$3:$P$104,2,FALSE ) J46 =VLOOKUP( LEFT( I46 )&"*",$O$3:$P$104,2,FALSE ) J47 =VLOOKUP( LEFT( I47 )&"*",$O$3:$P$104,2,FALSE ) J48 =VLOOKUP( LEFT( I48 )&"*",$O$3:$P$104,2,FALSE ) J49 =VLOOKUP( LEFT( I49 )&"*",$O$3:$P$104,2,FALSE ) J50 =VLOOKUP( LEFT( I50 )&"*",$O$3:$P$104,2,FALSE ) J51 =VLOOKUP( LEFT( I51 )&"*",$O$3:$P$104,2,FALSE ) J52 =VLOOKUP( LEFT( I52 )&"*",$O$3:$P$104,2,FALSE ) J53 =VLOOKUP( LEFT( I53 )&"*",$O$3:$P$104,2,FALSE ) J54 =VLOOKUP( LEFT( I54 )&"*",$O$3:$P$104,2,FALSE ) J55 =VLOOKUP( LEFT( I55 )&"*",$O$3:$P$104,2,FALSE ) J56 =VLOOKUP( LEFT( I56 )&"*",$O$3:$P$104,2,FALSE ) J57 =VLOOKUP( LEFT( I57 )&"*",$O$3:$P$104,2,FALSE ) J58 =VLOOKUP( LEFT( I58 )&"*",$O$3:$P$104,2,FALSE ) J59 =VLOOKUP( LEFT( I59 )&"*",$O$3:$P$104,2,FALSE ) J60 =VLOOKUP( LEFT( I60 )&"*",$O$3:$P$104,2,FALSE ) J61 =VLOOKUP( LEFT( I61 )&"*",$O$3:$P$104,2,FALSE ) J62 =VLOOKUP( LEFT( I62 )&"*",$O$3:$P$104,2,FALSE ) J63 =VLOOKUP( LEFT( I63 )&"*",$O$3:$P$104,2,FALSE ) J64 =VLOOKUP( LEFT( I64 )&"*",$O$3:$P$104,2,FALSE ) J65 =VLOOKUP( LEFT( I65 )&"*",$O$3:$P$104,2,FALSE ) J66 =VLOOKUP( LEFT( I66 )&"*",$O$3:$P$104,2,FALSE ) J67 =VLOOKUP( LEFT( I67 )&"*",$O$3:$P$104,2,FALSE ) J68 =VLOOKUP( LEFT( I68 )&"*",$O$3:$P$104,2,FALSE ) J69 =VLOOKUP( LEFT( I69 )&"*",$O$3:$P$104,2,FALSE ) J70 =VLOOKUP( LEFT( I70 )&"*",$O$3:$P$104,2,FALSE ) J71 =VLOOKUP( LEFT( I71 )&"*",$O$3:$P$104,2,FALSE ) J72 =VLOOKUP( LEFT( I72 )&"*",$O$3:$P$104,2,FALSE ) J73 =VLOOKUP( LEFT( I73 )&"*",$O$3:$P$104,2,FALSE ) J74 =VLOOKUP( LEFT( I74 )&"*",$O$3:$P$104,2,FALSE ) J75 =VLOOKUP( LEFT( I75 )&"*",$O$3:$P$104,2,FALSE ) J76 =VLOOKUP( LEFT( I76 )&"*",$O$3:$P$104,2,FALSE ) J77 =VLOOKUP( LEFT( I77 )&"*",$O$3:$P$104,2,FALSE ) J78 =VLOOKUP( LEFT( I78 )&"*",$O$3:$P$104,2,FALSE )
Excel 2003 I J K L M N O P 2 TRIP NUMBER IN courier 3 GUA UPS Counts Trips Name 4 S27 UPS FedEx 0 10 United 5 113 NOW UPS 0 15 United 6 3 NOW NOW 0 20 United 7 134 NOW US MAIL 0 21 United 8 113 NOW Prestige 0 23 United 9 316 NOW #N/A 0 24 United 10 323 NOW 25 United 11 121 NOW 30 United 12 213 NOW 33 United 13 25 NOW 34 United 14 102 NOW 35 United 15 PAY #N/A 36 United 16 528 NOW 101 NOW 17 109 NOW 102 NOW 18 UAM UPS 103 NOW 19 47 NOW 104 NOW 20 AKR Premier 105 NOW 21 UAM UPS 106 NOW 22 UAM UPS 107 NOW 23 S27 UPS 108 NOW 24 30A NOW 109 NOW 25 25A NOW 110 NOW 26 AKR Premier 111 NOW 27 48 NOW 112 NOW 28 E26 USCARGO 113 NOW 29 FPM FedEx 114 NOW 30 CLE Premier 115 NOW 31 20 NOW 12 USMAIL 32 30A NOW 120 NOW 33 15 NOW 121 NOW 34 25A NOW 122 NOW 35 CLE Premier 123 NOW 36 34 NOW 124 NOW 37 44 NOW 125 NOW 38 33 NOW 126 NOW 39 24 NOW 127 NOW 40 CLE Premier 130 NOW 41 24 NOW 131 NOW 42 33 NOW 134 NOW 43 CLE Premier 135 NOW 44 FAM FedEx 136 NOW 45 FAM FedEx 137 NOW 46 UAM UPS 138 NOW 47 30A NOW 139 NOW 48 S27 UPS 140 NOW 49 FAM FedEx 143 NOW 50 CLE Premier 144 NOW 51 S27 UPS 145 NOW 52 FAM FedEx 15A United 53 323 NOW 202 NOW 54 COL Premier 203 NOW 55 CLE Premier 204 NOW 56 CLE Premier 205 NOW 57 GUA UPS 206 NOW 58 106 NOW 207 NOW 59 MUP #N/A 208 NOW 60 CLE Premier 209 NOW 61 PAY #N/A 210 NOW 62 CLE Premier 211 NOW 63 CLE Premier 212 NOW 64 AKR Premier 213 NOW 65 214 NOW 214 NOW 66 PAY #N/A 21A United 67 PAY #N/A 25A United 68 FAM FedEx 26 USCARGO 69 GUA UPS 301 NOW 70 CLE Premier 302 NOW 71 CLE Premier 303 NOW 72 FAM FedEx 304 NOW 73 23 NOW 305 NOW 74 CLE Premier 306 NOW 75 COL Premier 307 NOW 76 S27 UPS 309 NOW 77 CLE Premier 30A United 78 COL Premier 310 NOW Oct2010
Worksheet Formulas Cell Formula J3 =VLOOKUP( LEFT( I3 )&"*",$O$3:$P$104,2,FALSE ) J4 =VLOOKUP( LEFT( I4 )&"*",$O$3:$P$104,2,FALSE ) J5 =VLOOKUP( LEFT( I5 )&"*",$O$3:$P$104,2,FALSE ) J6 =VLOOKUP( LEFT( I6 )&"*",$O$3:$P$104,2,FALSE ) J7 =VLOOKUP( LEFT( I7 )&"*",$O$3:$P$104,2,FALSE ) J8 =VLOOKUP( LEFT( I8 )&"*",$O$3:$P$104,2,FALSE ) J9 =VLOOKUP( LEFT( I9 )&"*",$O$3:$P$104,2,FALSE ) J10 =VLOOKUP( LEFT( I10 )&"*",$O$3:$P$104,2,FALSE ) J11 =VLOOKUP( LEFT( I11 )&"*",$O$3:$P$104,2,FALSE ) J12 =VLOOKUP( LEFT( I12 )&"*",$O$3:$P$104,2,FALSE ) J13 =VLOOKUP( LEFT( I13 )&"*",$O$3:$P$104,2,FALSE ) J14 =VLOOKUP( LEFT( I14 )&"*",$O$3:$P$104,2,FALSE ) J15 =VLOOKUP( LEFT( I15 )&"*",$O$3:$P$104,2,FALSE ) J16 =VLOOKUP( LEFT( I16 )&"*",$O$3:$P$104,2,FALSE ) J17 =VLOOKUP( LEFT( I17 )&"*",$O$3:$P$104,2,FALSE ) J18 =VLOOKUP( LEFT( I18 )&"*",$O$3:$P$104,2,FALSE ) J19 =VLOOKUP( LEFT( I19 )&"*",$O$3:$P$104,2,FALSE ) J20 =VLOOKUP( LEFT( I20 )&"*",$O$3:$P$104,2,FALSE ) J21 =VLOOKUP( LEFT( I21 )&"*",$O$3:$P$104,2,FALSE ) J22 =VLOOKUP( LEFT( I22 )&"*",$O$3:$P$104,2,FALSE ) J23 =VLOOKUP( LEFT( I23 )&"*",$O$3:$P$104,2,FALSE ) J24 =VLOOKUP( LEFT( I24 )&"*",$O$3:$P$104,2,FALSE ) J25 =VLOOKUP( LEFT( I25 )&"*",$O$3:$P$104,2,FALSE ) J26 =VLOOKUP( LEFT( I26 )&"*",$O$3:$P$104,2,FALSE ) J27 =VLOOKUP( LEFT( I27 )&"*",$O$3:$P$104,2,FALSE ) J28 =VLOOKUP( LEFT( I28 )&"*",$O$3:$P$104,2,FALSE ) J29 =VLOOKUP( LEFT( I29 )&"*",$O$3:$P$104,2,FALSE ) J30 =VLOOKUP( LEFT( I30 )&"*",$O$3:$P$104,2,FALSE ) J31 =VLOOKUP( LEFT( I31 )&"*",$O$3:$P$104,2,FALSE ) J32 =VLOOKUP( LEFT( I32 )&"*",$O$3:$P$104,2,FALSE ) J33 =VLOOKUP( LEFT( I33 )&"*",$O$3:$P$104,2,FALSE ) J34 =VLOOKUP( LEFT( I34 )&"*",$O$3:$P$104,2,FALSE ) J35 =VLOOKUP( LEFT( I35 )&"*",$O$3:$P$104,2,FALSE ) J36 =VLOOKUP( LEFT( I36 )&"*",$O$3:$P$104,2,FALSE ) J37 =VLOOKUP( LEFT( I37 )&"*",$O$3:$P$104,2,FALSE ) J38 =VLOOKUP( LEFT( I38 )&"*",$O$3:$P$104,2,FALSE ) J39 =VLOOKUP( LEFT( I39 )&"*",$O$3:$P$104,2,FALSE ) J40 =VLOOKUP( LEFT( I40 )&"*",$O$3:$P$104,2,FALSE ) J41 =VLOOKUP( LEFT( I41 )&"*",$O$3:$P$104,2,FALSE ) J42 =VLOOKUP( LEFT( I42 )&"*",$O$3:$P$104,2,FALSE ) J43 =VLOOKUP( LEFT( I43 )&"*",$O$3:$P$104,2,FALSE ) J44 =VLOOKUP( LEFT( I44 )&"*",$O$3:$P$104,2,FALSE ) J45 =VLOOKUP( LEFT( I45 )&"*",$O$3:$P$104,2,FALSE ) J46 =VLOOKUP( LEFT( I46 )&"*",$O$3:$P$104,2,FALSE ) J47 =VLOOKUP( LEFT( I47 )&"*",$O$3:$P$104,2,FALSE ) J48 =VLOOKUP( LEFT( I48 )&"*",$O$3:$P$104,2,FALSE ) J49 =VLOOKUP( LEFT( I49 )&"*",$O$3:$P$104,2,FALSE ) J50 =VLOOKUP( LEFT( I50 )&"*",$O$3:$P$104,2,FALSE ) J51 =VLOOKUP( LEFT( I51 )&"*",$O$3:$P$104,2,FALSE ) J52 =VLOOKUP( LEFT( I52 )&"*",$O$3:$P$104,2,FALSE ) J53 =VLOOKUP( LEFT( I53 )&"*",$O$3:$P$104,2,FALSE ) J54 =VLOOKUP( LEFT( I54 )&"*",$O$3:$P$104,2,FALSE ) J55 =VLOOKUP( LEFT( I55 )&"*",$O$3:$P$104,2,FALSE ) J56 =VLOOKUP( LEFT( I56 )&"*",$O$3:$P$104,2,FALSE ) J57 =VLOOKUP( LEFT( I57 )&"*",$O$3:$P$104,2,FALSE ) J58 =VLOOKUP( LEFT( I58 )&"*",$O$3:$P$104,2,FALSE ) J59 =VLOOKUP( LEFT( I59 )&"*",$O$3:$P$104,2,FALSE ) J60 =VLOOKUP( LEFT( I60 )&"*",$O$3:$P$104,2,FALSE ) J61 =VLOOKUP( LEFT( I61 )&"*",$O$3:$P$104,2,FALSE ) J62 =VLOOKUP( LEFT( I62 )&"*",$O$3:$P$104,2,FALSE ) J63 =VLOOKUP( LEFT( I63 )&"*",$O$3:$P$104,2,FALSE ) J64 =VLOOKUP( LEFT( I64 )&"*",$O$3:$P$104,2,FALSE ) J65 =VLOOKUP( LEFT( I65 )&"*",$O$3:$P$104,2,FALSE ) J66 =VLOOKUP( LEFT( I66 )&"*",$O$3:$P$104,2,FALSE ) J67 =VLOOKUP( LEFT( I67 )&"*",$O$3:$P$104,2,FALSE ) J68 =VLOOKUP( LEFT( I68 )&"*",$O$3:$P$104,2,FALSE ) J69 =VLOOKUP( LEFT( I69 )&"*",$O$3:$P$104,2,FALSE ) J70 =VLOOKUP( LEFT( I70 )&"*",$O$3:$P$104,2,FALSE ) J71 =VLOOKUP( LEFT( I71 )&"*",$O$3:$P$104,2,FALSE ) J72 =VLOOKUP( LEFT( I72 )&"*",$O$3:$P$104,2,FALSE ) J73 =VLOOKUP( LEFT( I73 )&"*",$O$3:$P$104,2,FALSE ) J74 =VLOOKUP( LEFT( I74 )&"*",$O$3:$P$104,2,FALSE ) J75 =VLOOKUP( LEFT( I75 )&"*",$O$3:$P$104,2,FALSE ) J76 =VLOOKUP( LEFT( I76 )&"*",$O$3:$P$104,2,FALSE ) J77 =VLOOKUP( LEFT( I77 )&"*",$O$3:$P$104,2,FALSE ) J78 =VLOOKUP( LEFT( I78 )&"*",$O$3:$P$104,2,FALSE )
I apologize for the double post but I may not have been clear in my earlier one. I also wanted to include an example.
When using time values, formatted as Custom>h:mm, in a vlookup statement I get a not available error. If I type in the value or copy and paste it in the vlookup will work. I am not sure if it is the function I am using to subtract one minute or it is a formatting error.
This is what it looks like (missing in the formulas is D2 =A2):
Sheet1
A B C D E 1 Time Price ($) Time Price ($) 2 22:44 49.66 22:44 49.66 3 22:43 50.66 22:43 #N/A 4 22:41 52.05 22:42 #N/A 5 22:35 52.07 22:41 #N/A 6 22:33 54.06 22:40 #N/A 7 22:29 54.43 22:39 #N/A 8 22:18 69.66 22:38 #N/A 9 22:00 73.01 22:37 #N/A 10 21:47 73.01 22:36 #N/A 11 21:41 75.82 22:35 #N/A 12 21:00 83.9 22:34 #N/A 13 20:18 75.82 22:33 #N/A 14 20:03 73.01 22:32 #N/A 15 20:00 69.66 22:31 #N/A 16 19:22 69.66 22:30 #N/A 17 19:00 73.01 22:29 #N/A
Spreadsheet Formulas Cell Formula E2 =VLOOKUP(D2,$A$2:$B$17,2,FALSE) D3 =D2TIME(0,1,0) E3 =VLOOKUP(D3,$A$2:$B$17,2,FALSE) D4 =D3TIME(0,1,0) E4 =VLOOKUP(D4,$A$2:$B$17,2,FALSE) D5 =D4TIME(0,1,0) E5 =VLOOKUP(D5,$A$2:$B$17,2,FALSE) D6 =D5TIME(0,1,0) E6 =VLOOKUP(D6,$A$2:$B$17,2,FALSE) D7 =D6TIME(0,1,0) E7 =VLOOKUP(D7,$A$2:$B$17,2,FALSE) D8 =D7TIME(0,1,0) E8 =VLOOKUP(D8,$A$2:$B$17,2,FALSE) D9 =D8TIME(0,1,0) E9 =VLOOKUP(D9,$A$2:$B$17,2,FALSE) D10 =D9TIME(0,1,0) E10 =VLOOKUP(D10,$A$2:$B$17,2,FALSE) D11 =D10TIME(0,1,0) E11 =VLOOKUP(D11,$A$2:$B$17,2,FALSE) D12 =D11TIME(0,1,0) E12 =VLOOKUP(D12,$A$2:$B$17,2,FALSE) D13 =D12TIME(0,1,0) E13 =VLOOKUP(D13,$A$2:$B$17,2,FALSE) D14 =D13TIME(0,1,0) E14 =VLOOKUP(D14,$A$2:$B$17,2,FALSE) D15 =D14TIME(0,1,0) E15 =VLOOKUP(D15,$A$2:$B$17,2,FALSE) D16 =D15TIME(0,1,0) E16 =VLOOKUP(D16,$A$2:$B$17,2,FALSE) D17 =D16TIME(0,1,0) E17 =VLOOKUP(D17,$A$2:$B$17,2,FALSE)
Thank you.
When using time values, formatted as Custom>h:mm, in a vlookup statement I get a not available error. If I type in the value or copy and paste it in the vlookup will work. I am not sure if it is the function I am using to subtract one minute or it is a formatting error.
This is what it looks like (missing in the formulas is D2 =A2):
Sheet1
A B C D E 1 Time Price ($) Time Price ($) 2 22:44 49.66 22:44 49.66 3 22:43 50.66 22:43 #N/A 4 22:41 52.05 22:42 #N/A 5 22:35 52.07 22:41 #N/A 6 22:33 54.06 22:40 #N/A 7 22:29 54.43 22:39 #N/A 8 22:18 69.66 22:38 #N/A 9 22:00 73.01 22:37 #N/A 10 21:47 73.01 22:36 #N/A 11 21:41 75.82 22:35 #N/A 12 21:00 83.9 22:34 #N/A 13 20:18 75.82 22:33 #N/A 14 20:03 73.01 22:32 #N/A 15 20:00 69.66 22:31 #N/A 16 19:22 69.66 22:30 #N/A 17 19:00 73.01 22:29 #N/A
Spreadsheet Formulas Cell Formula E2 =VLOOKUP(D2,$A$2:$B$17,2,FALSE) D3 =D2TIME(0,1,0) E3 =VLOOKUP(D3,$A$2:$B$17,2,FALSE) D4 =D3TIME(0,1,0) E4 =VLOOKUP(D4,$A$2:$B$17,2,FALSE) D5 =D4TIME(0,1,0) E5 =VLOOKUP(D5,$A$2:$B$17,2,FALSE) D6 =D5TIME(0,1,0) E6 =VLOOKUP(D6,$A$2:$B$17,2,FALSE) D7 =D6TIME(0,1,0) E7 =VLOOKUP(D7,$A$2:$B$17,2,FALSE) D8 =D7TIME(0,1,0) E8 =VLOOKUP(D8,$A$2:$B$17,2,FALSE) D9 =D8TIME(0,1,0) E9 =VLOOKUP(D9,$A$2:$B$17,2,FALSE) D10 =D9TIME(0,1,0) E10 =VLOOKUP(D10,$A$2:$B$17,2,FALSE) D11 =D10TIME(0,1,0) E11 =VLOOKUP(D11,$A$2:$B$17,2,FALSE) D12 =D11TIME(0,1,0) E12 =VLOOKUP(D12,$A$2:$B$17,2,FALSE) D13 =D12TIME(0,1,0) E13 =VLOOKUP(D13,$A$2:$B$17,2,FALSE) D14 =D13TIME(0,1,0) E14 =VLOOKUP(D14,$A$2:$B$17,2,FALSE) D15 =D14TIME(0,1,0) E15 =VLOOKUP(D15,$A$2:$B$17,2,FALSE) D16 =D15TIME(0,1,0) E16 =VLOOKUP(D16,$A$2:$B$17,2,FALSE) D17 =D16TIME(0,1,0) E17 =VLOOKUP(D17,$A$2:$B$17,2,FALSE)
Thank you.
Hello everyone, its been a while since my last post but after searching the boards for hours, I give up and need to ask for some help. Thanks to all who have posted and answered, its always a great help! I am sorry if I dont fully explain myself, I may be a little "punch drunk" fm searching for so long..... here is my delima.....
I have created a spreadsheet where I enter the date and time into cell A3 (formatted as ddmmmyy, hh:mm). In cell A4, i have created a formula that uses a vlookup to search "table1" for the local time zone conversion factor. My formula reads "=A3VLOOKUP(A1,Table1,4,FALSE). My formula works great, as long as you subtract the vlookup value fm A3. As I travel across timezones, eventually I will need to add time to A3.
How can I write this formula to return the local time, weather it is GMT 7:00 or GMT +4:00, without going in and actually changing the formula to read "=A3+VLOOKUP(A1,Table1,4,FALSE)? I need it to search the table to find out weather it should add 7 hrs, or subtract 8 hrs (depending on the timezone I am in). Is there a better way to create table1 so that when it looks it up, it will "just know" to add/subtract x hrs fm the time entered?
Currently I am still a little unsure about which time format to use when entering the station time (i.e. New York GMT 4:00; do I use hh:mm, [h]:mm, general, etc?), but I want the dispayed results to be displayed juat as I entered the original time (ddmmmyy, hh:mm).
ANY help to get me headed in the right direction would be great. Thanks again for the posts!
Mark
I have created a spreadsheet where I enter the date and time into cell A3 (formatted as ddmmmyy, hh:mm). In cell A4, i have created a formula that uses a vlookup to search "table1" for the local time zone conversion factor. My formula reads "=A3VLOOKUP(A1,Table1,4,FALSE). My formula works great, as long as you subtract the vlookup value fm A3. As I travel across timezones, eventually I will need to add time to A3.
How can I write this formula to return the local time, weather it is GMT 7:00 or GMT +4:00, without going in and actually changing the formula to read "=A3+VLOOKUP(A1,Table1,4,FALSE)? I need it to search the table to find out weather it should add 7 hrs, or subtract 8 hrs (depending on the timezone I am in). Is there a better way to create table1 so that when it looks it up, it will "just know" to add/subtract x hrs fm the time entered?
Currently I am still a little unsure about which time format to use when entering the station time (i.e. New York GMT 4:00; do I use hh:mm, [h]:mm, general, etc?), but I want the dispayed results to be displayed juat as I entered the original time (ddmmmyy, hh:mm).
ANY help to get me headed in the right direction would be great. Thanks again for the posts!
Mark
Hi,
I'vd got a table with a column called key that contains a single letter and then a number of any length.
Capture.JPG
I want to get the maximum number that exists in the table column, ignoring the letter prefix.
I've tried array formulas;
{=MAX(RIGHT(Table1[@Key],LEN(Table1[@Key])1))} returns 1
{=MAX(RIGHT(Table1[Key],LEN(Table1[Key])1))} returns 0
and non array formulas
=MAX(RIGHT(Table1[@Key],LEN(Table1[@Key])1)) returns 1
=MAX(RIGHT(Table1[Key],LEN(Table1[Key])1)) returns 1
I want it to return 6, as this is the maximum...
Thanks
Dave
I am TRYING to write a formula that incorporates "VLOOKUP" and "RIGHT"  and can't seem to get it right. Below is what I have:
(VLOOKUP (c2,TABLE1,(RIGHT(5,4)),FALSE)
Where I am trying to pull the 5th column from a table defined, and get only the last 4 digits.
Can anyone help?
THANKS
(VLOOKUP (c2,TABLE1,(RIGHT(5,4)),FALSE)
Where I am trying to pull the 5th column from a table defined, and get only the last 4 digits.
Can anyone help?
THANKS
I have two Tables, Table1 and Table2, in a single Excel spreadsheet. Table1 is our master log, and Table2 contains only Provider Names, Contact Names, Phone Numbers, and Email Addresses. I need to compare column K from Table1 with column AT from Table2. Whenever Excel finds a perfect match, I need Excel to copy AW:AY to AB:AD. For each value in column AT, there will be several identical matches in column K.
I have search long hours for a solution, have tried to implement vlookup() and index(match()), but cannot figure either one of them. Any help would be greatly appreciated.
fyi, Table1 is almost 1500 rows long, while Table2 is not quite 80.
Is there a function that will repopulate this list automatically without the multiple entries? I've been using lookup functions and max to add reference numbers on the side but was wondering if there is a simpler way.
Summary Sheet
I J K L 5 1 02.5151 1 02.5151 6 2 02.5160 2 02.5160 7 3 02.5166 3 02.5166 8 4 02.5221 4 02.5221 9 0 02.5221 5 02.5225 10 0 02.5221 6 02.5383 11 5 02.5225 7 02.5432
Spreadsheet Formulas Cell Formula L5 =IF(ISERROR(VLOOKUP(K5,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K5,$I$5:$J$30,2,FALSE)) I6 =IF(COUNTIF($J$5:J6,J6)=1,MAX($I$5:I5)+1,0) L6 =IF(ISERROR(VLOOKUP(K6,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K6,$I$5:$J$30,2,FALSE)) I7 =IF(COUNTIF($J$5:J7,J7)=1,MAX($I$5:I6)+1,0) L7 =IF(ISERROR(VLOOKUP(K7,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K7,$I$5:$J$30,2,FALSE)) I8 =IF(COUNTIF($J$5:J8,J8)=1,MAX($I$5:I7)+1,0) L8 =IF(ISERROR(VLOOKUP(K8,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K8,$I$5:$J$30,2,FALSE)) I9 =IF(COUNTIF($J$5:J9,J9)=1,MAX($I$5:I8)+1,0) L9 =IF(ISERROR(VLOOKUP(K9,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K9,$I$5:$J$30,2,FALSE)) I10 =IF(COUNTIF($J$5:J10,J10)=1,MAX($I$5:I9)+1,0) L10 =IF(ISERROR(VLOOKUP(K10,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K10,$I$5:$J$30,2,FALSE)) I11 =IF(COUNTIF($J$5:J11,J11)=1,MAX($I$5:I10)+1,0) L11 =IF(ISERROR(VLOOKUP(K11,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K11,$I$5:$J$30,2,FALSE))
Excel tables to the web >> Excel Jeanie HTML 4
Summary Sheet
I J K L 5 1 02.5151 1 02.5151 6 2 02.5160 2 02.5160 7 3 02.5166 3 02.5166 8 4 02.5221 4 02.5221 9 0 02.5221 5 02.5225 10 0 02.5221 6 02.5383 11 5 02.5225 7 02.5432
Spreadsheet Formulas Cell Formula L5 =IF(ISERROR(VLOOKUP(K5,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K5,$I$5:$J$30,2,FALSE)) I6 =IF(COUNTIF($J$5:J6,J6)=1,MAX($I$5:I5)+1,0) L6 =IF(ISERROR(VLOOKUP(K6,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K6,$I$5:$J$30,2,FALSE)) I7 =IF(COUNTIF($J$5:J7,J7)=1,MAX($I$5:I6)+1,0) L7 =IF(ISERROR(VLOOKUP(K7,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K7,$I$5:$J$30,2,FALSE)) I8 =IF(COUNTIF($J$5:J8,J8)=1,MAX($I$5:I7)+1,0) L8 =IF(ISERROR(VLOOKUP(K8,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K8,$I$5:$J$30,2,FALSE)) I9 =IF(COUNTIF($J$5:J9,J9)=1,MAX($I$5:I8)+1,0) L9 =IF(ISERROR(VLOOKUP(K9,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K9,$I$5:$J$30,2,FALSE)) I10 =IF(COUNTIF($J$5:J10,J10)=1,MAX($I$5:I9)+1,0) L10 =IF(ISERROR(VLOOKUP(K10,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K10,$I$5:$J$30,2,FALSE)) I11 =IF(COUNTIF($J$5:J11,J11)=1,MAX($I$5:I10)+1,0) L11 =IF(ISERROR(VLOOKUP(K11,$I$5:$J$30,2,FALSE)),"",VLOOKUP(K11,$I$5:$J$30,2,FALSE))
Excel tables to the web >> Excel Jeanie HTML 4
Hello,
I am trying to count the number of unique values in a range (four columns wide, called Name One through Name Four) based on some criteria (currently just the Year). The problem is that my values are in text format so I can't do this:
Code:
So this is what I am trying:
Code:
But this second formula doesn't work, seemingly because the range in the Match function is a range that has more than one column.
These formulas I have tried came from Microsoft's Excel Help. Any ideas on what formula I should try instead?
I attached a sample, but it's not very pretty.
Thanks
**
I am trying to count the number of unique values in a range (four columns wide, called Name One through Name Four) based on some criteria (currently just the Year). The problem is that my values are in text format so I can't do this:
Code:
=SUM(IF(FREQUENCY(IF((Table1[[Name One]:[Name Four]]<>"")*(Table1[Year]=A13),Table1[[Name One]:[Name Four]],""), IF((Table1[[Name One]:[Name Four]]<>"")*(Table1[Year]=A13),Table1[[Name One]:[Name Four]],""))>0,1,0))
So this is what I am trying:
Code:
=SUM(IF(FREQUENCY(IF((LEN(Table1[[Name One]:[Name Four]])>0)*(Table1[Year]=A13),MATCH(Table1[[Name One]:[Name Four]],Table1[[Name One]:[Name Four]],0),""), IF((LEN(Table1[[Name One]:[Name Four]])>0)*(Table1[Year]=A13),MATCH(Table1[[Name One]:[Name Four]],Table1[[Name One]:[Name Four]],0),""))>0,1,0))
But this second formula doesn't work, seemingly because the range in the Match function is a range that has more than one column.
These formulas I have tried came from Microsoft's Excel Help. Any ideas on what formula I should try instead?
I attached a sample, but it's not very pretty.
Thanks
**