I'm looking for a reference function to pull the result from an out of sort table (see table example below). The result should always come from the reference value LT A14. The table is always a whole number 0,1000,2000,3000, etc. I've tried the following functions, but they all require data in ascending order and/or exact match of the value in A14. =VLOOKUP($A$14,A$2:B$10,TRUE) =INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2) =LOOKUP($A$14,A$2:A$10,B$2:B$10) Table example: A B 2 1000 0.525 3 6000 0.946 4 3000 0.675 5 0 0.457 6 4000 0.746 7 2000 0.606 8 8000 1.29 9 5000 0.835 10 7000 1.09 If A14 = 3125, then result in B14 is 0.675. I don't want to have to use a complex IF/AND, like: =IF(AND($A$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue IF/AND") Thanks, Kevin

0 |

3/1/2010 6:01:01 PM

On Mon, 1 Mar 2010 10:01:01 -0800, AFSSkier <AFSSkier@discussions.microsoft.com> wrote: >I'm looking for a reference function to pull the result from an out of sort >table (see table example below). The result should always come from the >reference value LT A14. The table is always a whole number 0,1000,2000,3000, >etc. > >I've tried the following functions, but they all require data in ascending >order and/or exact match of the value in A14. >=VLOOKUP($A$14,A$2:B$10,TRUE) >=INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2) >=LOOKUP($A$14,A$2:A$10,B$2:B$10) > >Table example: > A B >2 1000 0.525 >3 6000 0.946 >4 3000 0.675 >5 0 0.457 >6 4000 0.746 >7 2000 0.606 >8 8000 1.29 >9 5000 0.835 >10 7000 1.09 > >If A14 = 3125, then result in B14 is 0.675. > >I don't want to have to use a complex IF/AND, like: >=IF(AND($A$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue >IF/AND") > >Thanks, Kevin Try this modifying your second formula like this >=INDEX(A$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,1),2) or, a little simpler, just =INDEX(B$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,0)) Hope this helps / Lars-�ke

0 |

3/1/2010 6:15:14 PM

>If A14 = 3125, then result in B14 is 0.675. What is the "rule" for finding the correct result? Is it: the closest value that is less than the lookup value? -- Biff Microsoft Excel MVP "AFSSkier" <AFSSkier@discussions.microsoft.com> wrote in message news:0CF9A182-3DAD-4518-A6A4-66A3F409CE8E@microsoft.com... > I'm looking for a reference function to pull the result from an out of > sort > table (see table example below). The result should always come from the > reference value LT A14. The table is always a whole number > 0,1000,2000,3000, > etc. > > I've tried the following functions, but they all require data in ascending > order and/or exact match of the value in A14. > =VLOOKUP($A$14,A$2:B$10,TRUE) > =INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2) > =LOOKUP($A$14,A$2:A$10,B$2:B$10) > > Table example: > A B > 2 1000 0.525 > 3 6000 0.946 > 4 3000 0.675 > 5 0 0.457 > 6 4000 0.746 > 7 2000 0.606 > 8 8000 1.29 > 9 5000 0.835 > 10 7000 1.09 > > If A14 = 3125, then result in B14 is 0.675. > > I don't want to have to use a complex IF/AND, like: > =IF(AND($A$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue > IF/AND") > > Thanks, Kevin

0 |

3/1/2010 6:17:44 PM

Lars-Åke, Your suggestion for using the Floor function works perfectly. The following formulas work great for what I needed. =INDEX(B$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,0)) or =VLOOKUP(FLOOR($A$14,1000),$A$2:$B$10,2,FALSE) -- Thanks, Kevin "Lars-Åke Aspelin" wrote: > On Mon, 1 Mar 2010 10:01:01 -0800, AFSSkier > <AFSSkier@discussions.microsoft.com> wrote: > > >I'm looking for a reference function to pull the result from an out of sort > >table (see table example below). The result should always come from the > >reference value LT A14. The table is always a whole number 0,1000,2000,3000, > >etc. > > > >I've tried the following functions, but they all require data in ascending > >order and/or exact match of the value in A14. > >=VLOOKUP($A$14,A$2:B$10,TRUE) > >=INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2) > >=LOOKUP($A$14,A$2:A$10,B$2:B$10) > > > >Table example: > > A B > >2 1000 0.525 > >3 6000 0.946 > >4 3000 0.675 > >5 0 0.457 > >6 4000 0.746 > >7 2000 0.606 > >8 8000 1.29 > >9 5000 0.835 > >10 7000 1.09 > > > >If A14 = 3125, then result in B14 is 0.675. > > > >I don't want to have to use a complex IF/AND, like: > >=IF(AND($A$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue > >IF/AND") > > > >Thanks, Kevin > > > Try this modifying your second formula like this > > >=INDEX(A$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,1),2) > > or, a little simpler, just > > =INDEX(B$2:B$10,MATCH(FLOOR($A$14,1000),A$2:A$10,0)) > > Hope this helps / Lars-Åke > . >

0 |

3/1/2010 8:15:02 PM

Although this can be done (of course), you (or your principal) are making it difficult. Why not sort the table (ascending) so you can use standard VLOOKUP functionality? If that is not possible, please give some more information about the (type of) problem you are trying to solve; we are just not prepared to believe things should be that difficult.:-) -- Kind regards, Niek Otten Microsoft MVP - Excel "AFSSkier" <AFSSkier@discussions.microsoft.com> wrote in message news:0CF9A182-3DAD-4518-A6A4-66A3F409CE8E@microsoft.com... > I'm looking for a reference function to pull the result from an out of > sort > table (see table example below). The result should always come from the > reference value LT A14. The table is always a whole number > 0,1000,2000,3000, > etc. > > I've tried the following functions, but they all require data in ascending > order and/or exact match of the value in A14. > =VLOOKUP($A$14,A$2:B$10,TRUE) > =INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2) > =LOOKUP($A$14,A$2:A$10,B$2:B$10) > > Table example: > A B > 2 1000 0.525 > 3 6000 0.946 > 4 3000 0.675 > 5 0 0.457 > 6 4000 0.746 > 7 2000 0.606 > 8 8000 1.29 > 9 5000 0.835 > 10 7000 1.09 > > If A14 = 3125, then result in B14 is 0.675. > > I don't want to have to use a complex IF/AND, like: > =IF(AND($A$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue > IF/AND") > > Thanks, Kevin

0 |

3/1/2010 8:18:11 PM

I would be a lot easier if the table was not shared or if it was a perfect world. But as you know, we're all in end-user hell & it's not a perfect world. The table is imported in ascending order. But the end-users are able to sort as they need it printed. I know my posted example doesn't reflect this, I simplified it for illustration. -- Thanks, Kevin "Niek Otten" wrote: > Although this can be done (of course), you (or your principal) are making it > difficult. Why not sort the table (ascending) so you can use standard > VLOOKUP functionality? > > If that is not possible, please give some more information about the (type > of) problem you are trying to solve; we are just not prepared to believe > things should be that difficult.:-) > > -- > Kind regards, > > Niek Otten > Microsoft MVP - Excel > > > "AFSSkier" <AFSSkier@discussions.microsoft.com> wrote in message > news:0CF9A182-3DAD-4518-A6A4-66A3F409CE8E@microsoft.com... > > I'm looking for a reference function to pull the result from an out of > > sort > > table (see table example below). The result should always come from the > > reference value LT A14. The table is always a whole number > > 0,1000,2000,3000, > > etc. > > > > I've tried the following functions, but they all require data in ascending > > order and/or exact match of the value in A14. > > =VLOOKUP($A$14,A$2:B$10,TRUE) > > =INDEX(A$2:B$10,MATCH($A$14,A$2:A$10,1),2) > > =LOOKUP($A$14,A$2:A$10,B$2:B$10) > > > > Table example: > > A B > > 2 1000 0.525 > > 3 6000 0.946 > > 4 3000 0.675 > > 5 0 0.457 > > 6 4000 0.746 > > 7 2000 0.606 > > 8 8000 1.29 > > 9 5000 0.835 > > 10 7000 1.09 > > > > If A14 = 3125, then result in B14 is 0.675. > > > > I don't want to have to use a complex IF/AND, like: > > =IF(AND($A$14>3000, $A$14<3999),VLOOKUP(3000,A$2:B$10,2,FALSE),"continue > > IF/AND") > > > > Thanks, Kevin >

0 |

3/1/2010 8:57:03 PM

For example - if the answer to a calculation is 18, then look in sheet named "18". If 19, then look in sheet "19". Thanks. Marcus Use the INDIRECT function. E.g., =INDIRECT("'"&A1&"'"&"!A10") This will return the value in cell A10 from the sheet named in cell A1. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Marcus Fox" <please-reply-via-newsgroup-th@-i-posted-to.com> wrote in message news:m4cjg.33798$uP.14385@newsfe2-gui.ntli.net... > F...

Can anyone tell me which table holds FIFO balances in it? Thanks, Jocelyn Jocelyn, IV00102 has the quantities but I suspect your looking for IV10200 which has the history and the current FIFO layers. More info here: http://msdynamicsgp.blogspot.com/2007/09/weekly-dynamic-inventory-value-via-sql.html Mark (DynamicAccounting.net) http://www.dynamicaccounting.net On Oct 1, 3:44 pm, Jocelyn <Joce...@discussions.microsoft.com> wrote: > Can anyone tell me which table holds FIFO balances in it? > > Thanks, > Jocelyn ...

How to sort by year with this format Sept 9/03 Is the data formated to look like that or is it a text entry? if it is a format. It will sort just using any sorting procedure. if it is text use a helper column with =right(A1,2) "Betty" wrote: > How to sort by year with this format Sept 9/03 > ...

Hi all,What sort of SQL query could be used in an Access database to determine the existence of a table? I can do this in SQL server by looking at sysobjects - but how does one do it in Access? I have an application that needs to check to see if a table exists and, if not, create it on-the-fly.Thanks,-bruce create procedure sphappyasif exists (select name from sysobjects where id =object_id('mytable'))begindrop table mytableendOn Mar 20, 4:51 pm, "Microsoft Newsgroups" <broe...@cfl.rr.com> wrote:> Hi all,>> What sort of SQL query could be used in an Access da...

Hi! I have 2 tables: Table Users: ID Name Password Birth Table Status: ID Status Both tables are related by "ID" field, but both tables have not the same number of elements I want to obtain a new table with this structure: Table Result: ID Name Password Birth Status As I said before both tables have not the same number of elements (rows) so in my result table, "Status" field should be empty for those "ID"'s not found in "Status" table. Example: Users: 1 2 3 ...

I have the following table and I would like to have each item on one row # Acc# Dr Cr # Count Info 1271 1030 4.67 1 3 Cr 2200 0.27 1 3 Dr 6050 4.41 1 3 Dr 1288 1030 7.87 2 2 Cr 6090 7.87 2 2 Dr 1617 1030 61.9 3 4 Cr 2200 2.59 3 4 Dr 6050 4.11 3 4 Dr 6550 55.2 3 4 Dr 1958 1000 45.9 4 5 Cr 1000 39.2 4 5 Cr 2200 4.01 4 5 Dr 6310 37.4 4 5 Dr 6630 43.7 4 5 Dr There is info that can help with determining items Count - checks how many items should be transposed Info - provides the information whea...

Hi, i need to sum the values in a table based on a name match in COLUMN B and a text match in ROW 4 - say for each occurrence of "Jim Smith" in range B7:B505,when "text" appears in range E4:GC4, SUM all cells which will contain number values- so if:- text1 text2 text3 text1 Jim Smith 3 6 2 4 Sue Brown 1 5 1 7 Mark Bosman 2 9 3 6 Jim Smith 5 4 2 3 the result would have ...

Hello, I'm having a weird problem with the table of contents. I have been editing a long document and am regularly updating the TOC. I have just noticed that the spaces between the lines were various in the TOC and thus used the style inspector to find out why. It appeared that TOC 1, TOC 2, TOC 3, TOC 4...styles were all there in the TOC. I have no idea why, I'm creating the TOC repeatedly, removing or replacing the previous one and when creating, I'm choosing TOC 1 as the style but when the TOC is inserted, the problem is the same. Single space here, 1.5 space th...

I've created a lovely invoice in Excel for my company. When I generate a new invoice from the template it puts in the date using the TODAY() function, but unfortunately this updates if I ever open the invoice again to look at it. In Word there is a "CREATEDATE" function which inserts the date that the file was created first, and never after does it change. Is there any way to accomplish this same task in Excel? Thanks, Michael mleenheer @ yahoo.com ML, Keyboard shortcut to date stamp is put your cursor in the cell you want and push -- ctrl ; --(thats ctrl and semicolon...

I have Office 2003 including Outlook 2003 installed on a Win XP Pro machine. When Outlook opens two Personal Folders appear in the Mail window, one has the default icon, the second has the archive icon. I cannot delete either of these. In Properties|Advanced they both have the same exact path name to Outlook.pst. In the Calendar window under My Calendars appears Calendar and Calendar in Personal folders. In the Contacts window two sets of contact folders appear As Contacts and Contacts in Personal folders, etc. Likewise for the Tasks and Notes windows - duplicate icons appear. If I dele...

How can I access the user-defined functions?? I want to modify one of the functions and don't see how to get into it. Thanks in advance... -Andrea Report Writer functions.... shown as user defined functions in calculated fields are created by a Dexterity developer and cannot be created or altered by a user. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer MBS Support - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessaril...

Hi, this is my need: I should add an entry to the context menu in the list of email, a heading such as "Send to" to which I could associate my code (such as VB 6.0 COM component) to perform some "custom" operation on email selected. Recognizing that this question is my very first of its kind, ie although I'm developer for many years I never had to address the need to "expand" the functionality of a package of office. I would be grateful to anyone who was in to give me a hand, at least to address me on how/where retrieve documention for this need...

Hello, I have this code setup in Excel but I want to be able to upload multiple rows at one time. Is there a way to incorporate this into my code? Also, is there a way to upload cells that are blank as well? Right now it won't allow me to upload cells that are blank. Can this be done? Sub UploadData() Dim MyCn As ADODB.Connection Dim SQLStr As String Set MyCn = New ADODB.Connection MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _ "DBQ=\\Bcar1\bcar-net\Quality-ISO\Quality-Control\BC-Quality-Control\BC QA mdb\BC Quality Action Database.mdb;SystemDB=\...

Hi, I'm looking for any specyfication of new functionalities in version 3.0. We are writing new functionalites in base of 1.2 version - maybe it will not needed and we spend time. search google for microsoft crm and you will see lots of news items related to version 3.0 -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Rafal" <Rafal@discussions.microsoft.com> wrote in message news:9C72D354-3ACC-479D-9B17-BC65036F978A@microsoft.com... > Hi, I'm looking for any specyfication of new functionalities in version 3.0. > We are writing new functionali...

Good morning folks! I hope I have a simple one for you but I am stumped! On sheet #1 (called: Kub Kar List) I have my sponsors listed down Col "F" and their slogan in Col "H". On another sheet (SLOGAN) I would like to be able to list the sponsor slogan (='Kub Kar List'!$H3) also incrementing downwards. My problem is if I delete a sponsor from sheet#1 (highlight row and delete) I get a #REF error because that line was deleted and my calc on sheet#2 changes! I would like my reference NOT to change no matter whats done on the first sheet! What ...

Dear expert, I am not good at function VBA, but I know that can help me to save a lot of memory. Say a cell in K10, L10 and M10 Contain figures 7,8,10 How can I write a function say in J10 type =cross(K10,L10,M10) Outcome can be 4. If And(M10>L10, L10>K10) = Show "All DOWN" If And(K10>L10, L10>M10) = Show "All UP" If And(K10>L10, L10<M10) = Show "K cross L UP" If And(K10<L10, L10>M10) = Show "K cross L down" Now I have a lengthy function in J10 which is going to copy from J10 to J32000 =IF(AND(K10>L10,L10&g...

Hi When opening a meeting in Outlook 2010, it's possible to go to the tracking pane and see who accepted, declined or are tentative. For severel years (since Outlook 2003), I've missed the possiblity to click the 'Response' column, to sort the responses! This must be an easy thing to implement! In addition, I'm missing three reply buttons in the open meeting window; - Reply accepted - Reply declined - Reply tentative Please concider implementing these suggestions! Cheers, Martin ---------------- This post is a suggestion for Microsoft, and Mi...

I've used this feature a lot, but can't seem to get this task accomplished... I have a column with numbers, but the first character is an apostrophe ('). I want to removed this character from this column so I can format the column successfully. Thoughts?? Thanks for your help! In a helper column, try: =SUBSTITUTE(A1,"'","") Copy down as needed. Then copy the helper column and using 'Paste Special' ->Values, paste the date back to the original column. Regards, Paul "DNA" <dnoel@fsgbank.com> wrote in message news:c4293322...

Using Publisher 2002 .... To prevent tables slipping I've tried Ctrl 'M' and creating a table on this page. I select all and colour the lines in black. (This creates a slipproof template) I then copy using Ctrl 'C' Back to original page, Ctrl 'V', the table I copied is now positioned exactly on top of the under page. I want to change this table into "No Lines" in order that I may type in the data using the 'tab' key to proceed to the next box. *exactly* but I'm unable to carry out this function. I'd appreciate guidance please. John ...

How to format a text in concatenate function. for eg. "mm2" . i want to do in concatenate function , the last no. 2 should be in superscript position. pls suggest answer or alternative -- NITESH G ------------------------------------------------------------------------ NITESH G's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17590 View this thread: http://www.excelforum.com/showthread.php?threadid=482742 Hi One way with your number in A1 this formula in B1 =A1&" mm�" to type the �, hold down the Alt key and type 0178 from the numer...

I have upgraded my computer to a P4 3Ghz and FX5600 256Mb Graphics Card and New Matrox Hard Drive.. Since formatting and re-installation of PUBLISHER i can not see my Tables or Word art and a few graphics only the Fonts are Visible. The documents print fine everything is there but i can not see them on the monitor. I made up a new File saved it then re executed it and the same thing happens. Have all updates in, changed refresh rates, latest Nvidia driver, Reinstalled Publisher and updates. PLEASE HELP ME BEFORE IT GOES OUT THE WINDOW!!!!!! Why is it that the first thing people wan...

When using Contacts as an Address Book, is there a way to change the sort order without changing the Default Full Name order? Currently when I select "To..." while composing a new message, Contacts are sorted by first name. I'd like to sort them by last name. Thanks. -- Lew One of the most frequently asked questions (probably becuase it's one of the most carefully hidden options): Go to Tools > E-mail accounts > View or change existing directories or address books > Outlook Address Book > Change. > Set your sort order there -- Russ Valentine [MVP-Out...

Hi, We recently upgraded some of our machines to Office 2003, from Office 2000, however are now experiencing serious problems with Excel. It appears that when a file containing pivot tables (all files were created in Excel 2000) is modified with Excel 2003, the pivot tables somehow become corrupt. The next time the file is opened, Excel reports an error and tries to repair the file, dumping the Pivot tables due to a problem with their integrity. The exact message is as below; PivotTable report 'PivotTable1' on '[Book1.xls]Sheet1' was discarded due to integrity problems. Pivo...

Hi, I am developing a C# application to create Excel Pivot tables. I have developed the same by using methods in the Microsoft.Office.Interop.Excel. But the size of the output excel files is too large. Is there any option to reduce the file size. Given below the code used to generate Excel File . private string CreateReport(Excel.Application xlApp, string queryReports, string reportPath) { try { bool IsErrorInWorkBook = false; string strGrandTotal = null; FileInfo fpPubReports = new FileInfo(...

I am using a pivot table to show the amount of product that is being used in a month. What I would like to do is color code any month that has less than 20 days usage in any month. This is part of a table that I am using: Product Days Qty Orderld Month 1234 23 12 60 1 2345 26 90 60 1 9586 18 85 60 2 4759 21 65 60 3 2589 12 34 60 4 2589 23 56 60 5 1234 10 89 60 6 1234 23 12 60 ...