Matching number formats

I am using an INDEX/MATCH formula and do get a "VALUE NOT AVAILABLE" 
response. Both ranges have a "General" format. A [Copy][Paste 
Special][Formats] does not make the formats match. How can I make them 
respond to each other?
0
raystub (3)
5/12/2005 5:36:09 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
639 Views

Similar Articles

[PageSpeed] 0

On Thu, 12 May 2005 10:36:09 -0700, "raystub"
<raystub@discussions.microsoft.com> wrote:

>I am using an INDEX/MATCH formula and do get a "VALUE NOT AVAILABLE" 
>response. Both ranges have a "General" format. A [Copy][Paste 
>Special][Formats] does not make the formats match. How can I make them 
>respond to each other?

If you post your formula, and some samples of the data, I'm sure someone will
be able to help.  Also post the desired result from that sample data.

The "VALUE NOT AVAILABLE" result is not a standard feature of the built-in
INDEX or MATCH worksheet functions.  

And cell format has nothing at all to do with how those functions work.


--ron
0
ronrosenfeld (3122)
5/12/2005 7:26:49 PM
Thanks, Ron.
I did accomplish my goal, but it took a long time.
I formatted both ranges as text and then I had to go down the entire list 
and press [F2] [Enter] on each cell. For some reason, this worked.
I would still like to know from anyone if there is a better way of matching 
these formats.

"Ron Rosenfeld" wrote:

> On Thu, 12 May 2005 10:36:09 -0700, "raystub"
> <raystub@discussions.microsoft.com> wrote:
> 
> >I am using an INDEX/MATCH formula and do get a "VALUE NOT AVAILABLE" 
> >response. Both ranges have a "General" format. A [Copy][Paste 
> >Special][Formats] does not make the formats match. How can I make them 
> >respond to each other?
> 
> If you post your formula, and some samples of the data, I'm sure someone will
> be able to help.  Also post the desired result from that sample data.
> 
> The "VALUE NOT AVAILABLE" result is not a standard feature of the built-in
> INDEX or MATCH worksheet functions.  
> 
> And cell format has nothing at all to do with how those functions work.
> 
> 
> --ron
> 
0
raystub (3)
5/13/2005 5:28:03 PM
On Fri, 13 May 2005 10:28:03 -0700, "raystub"
<raystub@discussions.microsoft.com> wrote:

>Thanks, Ron.
>I did accomplish my goal, but it took a long time.
>I formatted both ranges as text and then I had to go down the entire list 
>and press [F2] [Enter] on each cell. For some reason, this worked.
>I would still like to know from anyone if there is a better way of matching 
>these formats.

It is likely there is a much simpler method than what you describe.  But
without more details, it is not possible for me to advise you further.


--ron
0
ronrosenfeld (3122)
5/13/2005 7:34:17 PM
Thank you for trying, Ron.
I don't know how to describe it more clearly.
I don't know hot to send copies of the data to the discussion group.



"Ron Rosenfeld" wrote:

> On Fri, 13 May 2005 10:28:03 -0700, "raystub"
> <raystub@discussions.microsoft.com> wrote:
> 
> >Thanks, Ron.
> >I did accomplish my goal, but it took a long time.
> >I formatted both ranges as text and then I had to go down the entire list 
> >and press [F2] [Enter] on each cell. For some reason, this worked.
> >I would still like to know from anyone if there is a better way of matching 
> >these formats.
> 
> It is likely there is a much simpler method than what you describe.  But
> without more details, it is not possible for me to advise you further.
> 
> 
> --ron
> 
0
raystub (3)
5/25/2005 6:14:02 PM
Reply:

Similar Artilces:

macro based on text or number
I want to do an if statement within a macro that will copy and paste to another cell, a field that is text. Anything numerical should be skipped. How do I do this? Here is one way. Set rng = Sheets("Sheet1).Range("A1") If Not IsNumeric(rng) Then rng.Copy Sheets("Sheet2").Range("A2") End If "JOSEPH WEBER" <JOSEPHWEBER@discussions.microsoft.com> wrote in message news:A5D52B8A-7923-416B-9C20-3F18A7594374@microsoft.com... >I want to do an if statement within a macro that will copy and paste to > another cell, a field...

How to HR user to modify GAL user phone number and address details.
Hello, I need to allow a HR user to modify GAL user phone number and address details. What is the cleanest way to allow these permissions only? Exchange 2003, outlook 2003 Quest Active Roles Server <vyaw2003@gmail.com> wrote in message news:1170223431.607031.241980@k78g2000cwa.googlegroups.com... > Hello, > I need to allow a HR user to modify GAL user phone number and address > details. > What is the cleanest way to allow these permissions only? > Exchange 2003, outlook 2003 > **I need to allow a HR user to modify the GAL through Outlook 2003 cant i just delega...

Date format in merged field
I am using Publisher 2000 (from Office 2000 Pro), under W2K. I am printing membership cards, merging data from an Excel spreadsheet. One of the merged text fields on the membership card is a date. How do I control the date format on the printed cards? When the spreadsheet had a date format of "8/4", the card printed as "August 8, 2004". I changed the spreadsheet date format to "August 4, 2004". Now the card prints "8/4/2004". I suspect, however, that it was something else that I did within Publisher that caused the date format results. - Da...

Master Transaction Number & Trx Source Number
In SOP what this numbers mean Master Transaction Number Trx Source Number What is the initial tables for this number. Thanks a lot. V, This really is a developer question that may be better posted/answered on the mbspartner.developer ng. That is for GP and SBF developer questions. Matt "Vitali V" <vitvov@dynamo-ny.com> wrote in message news:uIVuOK1sFHA.4028@TK2MSFTNGP10.phx.gbl... > In SOP what this numbers mean > Master Transaction Number > Trx Source Number > > What is the initial tables for this number. > > > Thanks a lot. > > Tha...

Formatting Heading Rows with AutoFormat
Hi, I have a user who is trying to use AutoFormat but, when it is applied, most of the rows are formatted with the heading style and the last few rows are formatted with the alternate shading. How do you tell Excel that the first three rows are the heading and the rest is the body of the spreadsheet? Excel seems to determine the heading cells by where the data type changes, in one or more columns. Perhaps the user's table has numbers as headings in some of the columns (e.g. Year), and numbers in those columns. In the headings, type an apostrophe before the number, to enter it as text...

Repeat row numbers
Excel 2003 I need to number rows on a large spreadsheet from 1 - 27, and repeat 1 - 27, etc to the end. I'd rather not copy and paste! TIA Carole O Put 1 in a1, and this formula in A2, and copy down: =if(a1=27,1,a1+1) Regards, Fred "Carole O" <CaroleO@discussions.microsoft.com> wrote in message news:2A01BD31-CEE3-45D9-B5B5-D0E060869E49@microsoft.com... > Excel 2003 > I need to number rows on a large spreadsheet from 1 - 27, and repeat 1 - > 27, > etc to the end. I'd rather not copy and paste! > > TIA > > Carole O...

conditional format is lost in publishing to html
I noticed that excel warns some formating may be lost if interactivity is checked when you publish as a webpage. I need for cells to change color to match acid or base readings. Is there a way that the conditional formatting can be preserved while keeping the interactivity for students who'll use the worksheet? Thanks! ...

Delete an unused check number
Is there a way to delete an unused check number not entered into the system? We used a check for a manual payment, but ended up voiding the check as it was not needed. Since the check was never printed, this check number does not appear in the system so I can't void it. But the physical check itself is obviously not usable now. Thanks for any help. Melissa, The only thing I can think of is entering a manual payment for $0 to use up this check number. -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "melissa" <melissa@discussions.microsoft.com> wrote in ...

Matching function or no match
I am using this formula to compare 2 list to see which numbers are not included in both column A and Col C. The formula I am using below pulls out the ones that i do have a match . Is there another function that I could use that would only pull out the ones that there is not a match? =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1) Thanks Uh =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),A1,"") -- HTH Bob "Donna" <donna@yahoo.com> wrote in message news:BE688A2B-3D42-45A0-AA3F-B65034C685E1@microsoft.com... > > I am using this for...

Client E-Mail and Web E-Mail not matching
I've got a few complaints from about 4 users where e-mail they see in their Outlook Client is not showing up in their Outlook Web Client when they check their e-mail from a remote location. We're using Exchange 2003 with SP2 installed and all the users have Office Outlook 2003 with SP1 Installed also. This seems to only have been a problem since we upgraded to Exchange SP2 two weeks ago. (Although I can't confirm it, but I wasn't told of any problems before th upgrade.) We had to upgrade to SP2 because of the 16 Gig limit so rollback is not an option. Best as I can t...

Accounting format for 0
I have had the same problem in both Excel 1997 and Excel 2000. I always use the Accounting format which gives a dash for a 0. However, every once in while I will get a 0 instead of a dash. When I do that I extend out the decimals and way out there are some numbers. I always check to make sure that any formulas I have either round or are from whole numbers so there is no possibility of it being a rounding problem. I think it's probably a glitch in the Excel program, but I haven't talked with anyone else who has encountered this. Mainly I think because most people don'...

Do not count minus-numbers
I have some number I would like to SUM, but I dont want excel to calculate the minus-number. Here is what I have - A1 10 B1 20 C1 30 D1 40 E1 -1000 F1 0 G1 0 I do it like this SUM(A1:G1) But I want it to say SUM(10+20+30+40+0+0) How can I do it like that ? May be =sumif(A1:G1,">0") "SpookiePower" <boxjunk2600@gmail.com> wrote in message news:4ef118e1-4171-4509-a5dd-eaf1fd0a91e6@v19g2000yqn.googlegroups.com... >I have some number I would like to SUM, but I dont want excel to > calculate the minus-number. > > Here is what I have - > > A1 10 ...

Autofilter Wildcards Not Working on Number Column
Hello everyone...n00b question. I'm using Excel 2003 to split up a daily spreadsheet between me and 2 of my peers. We split the orders based on the last 2 characters of the order. The orders are all 7 digits and all numerical. Exp... 3215453. For example... my number range is 00-33. Every morning I have to scroll through the spreadsheet and manually select my orders to obtain my daily workload, which can take up to 40 minutes. I tried using auto-filter with wildcards * & ?, but they don't work. I'm typing my custom filter values as follows ?????33 and *33, but they don't ...

Conditional Formatting with 3 options
Hi, i would like to know if anyone has successfully created conditional format with 3 options whereby 3 colours are displaye depending on the cell/formula result contents. I would say i have ver strong excel skills and although i could easily program this in VBA th spreadsheet needs to work without VBA in this instance. basically my problem is to monitor changes in performance between dates for a budget whereby a positive improvement of actual vs budge is one colour a negative performance is another colour ( now that wa the easy bit ) the difficult bit is to show a third colour when th var...

Remove Scientific Number
I have a list of customers code need to transfer in excel sheet. and there is a problem when the customer code contain 3E001 and etc.. will automatically convert into scientific number like 3.00E+01. how can i disable the feature so that I can get the correct value after import into Excel. Either pre-format the cell as text, or prefix the value with a single quote. Jerry Edward wrote: > I have a list of customers code need to transfer in excel sheet. and there > is a problem when the customer code contain 3E001 and etc.. will > automatically convert into scientific number like 3...

Matching cells #3
Here's my spreadsheet: A B C 1 1 1 2 3 3 3 4 6 4 5 10 5 8 11 6 9 7 10 8 9 10 11 12 13 How do I get the matching numbers on the same rows, like: A B C 1 1 1 2 3 3 3 4 4 5 5 6 6 7 8 8 9 9 10 10 10 11 11 12 13 13 I would just re-create the columns. 1. Copy column A into D. 2. In E1 put: =IF(COUNTIF(B:B,$A1),$A1,"") 3. Copy it over to F1 and then down as far as needed. 4. Select columns D thru F, copy them, and go to Edit > Paste Special > Value. Press OK. 5. Delete column A thru C. HTH Jason Atlanta, GA &g...

Autonumber numbers
Is there a way, in a new Access 2007 database, to start the numbers of an Autonumber field at a number other than 1? It would be very convenient for us to start at 10001. Marsh An autonumber field is not supposed to be used as a user's numbering system. If you want to have a numbering system, then you should create it by creating a number field and applying your math and logic to it. This way you can start it at whatever number you wish and can control number issuing... But as far as an autonumber is concerned... you are relying on something in a way you should not b...

Attachment format changes
Recently sent several e-mails using Outlook that were either pdf or xls format. Receipient got dat files and a warning not to open them. What's going on? How does the format get changed? Make sure you use HTML or Plain Text format when sending messages to Outlook Express users, or users of any other e-mail client besides Outlook. Don't use Rich Text format, which is proprietary to Outlook and will cause problems for other recipients, one of which is that they won't get your attachments and will likely get that ".dat" file instead. -- Jocelyn Fiorello MVP - Outlo...

Lookup Formula: Return 1st match, then 2nd match, then 3rd match
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B = Task. A B Bob Reconcile Cash Tim Do Sales Report Bob Create presentation Tim Prepare financial statements Bob Hire staff person Now on tab two, I want to create another list that pulls all the tasks together by person. For example, Tab 2 would look like this: Bob Reconcile Cash Bob Create presentation Bob Hire staff person Tim Do Sales Report Tim Prepare financial statement. I realize I can just sor...

Index Match Functions
Has anyone ever combined the Index and Match functions to do lookups? ...

Not matching transactions
I have it set to not automatically do anything. I've tried resetting the account quite a few times with the help of technicians, but it's too long and involved, and I'm tired of rebalancing the account every time. I have 3 accounts, 1 works without passport and does fine. One of the others I can manually download from the web site, and have no problems doing it like so and decided this is the best way, prior tries it wouldn't work with passport. But the other still insists they are new transactions, and I can't download from their site directly. They only allow...

Vlookups yielding wrong N/A results (numbers stored as texts and viceversa)
Hi there, my problem is that I have to do a number of vlookups (exact matches) on customer codes (which are numeric values). Most of the times, Excel returns an N/A error, even when it shouldn't, because the code is in both tables. My understanding is that this happens when Excel treats the code in the first table as a number, and the one in the second table as a text. How do I solve this extremely annoying problem? Since Excel is a spreadsheet and not a database, it allows you to choose a formatting, but not to set a data type for a field. So how do I make sure Excel treats the custome...

how to insert roman numbers
I am trying to insert roman numerals such as Grade 2 For Roman 2, have you tried holding the shift key while pressing the i key twice? -TedMi "drisc229" <drisc229@discussions.microsoft.com> wrote in message news:A1DE5488-E49E-4F81-8539-2E87A9662BD9@microsoft.com... >I am trying to insert roman numerals such as Grade 2 How do you want to use the Roman numerals? If you want a numbered list, just choose the desired numbering format (i,ii,iii or I,II,III). If you want a sequential list with entries anywhere in the text, use SEQ fields. If you want static ...

data in row didnt match import type 12-30-05
while importing almost 800 accounts , i got the following error for majority of accounts 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? It looks like a data problem. You need to check if all dates are correct (and filled), if numbers are numbers (not letters), etc... Regards, -- Erik van Hoof CWR Mobility Check our weblog at: http://www.cwrmobility.com/weblog "Aam" <Aam@discussions.microsoft.com> wrote in message news:C51E8ED0-443D-4B35-B436-0A9EDB09B419@microsoft.com... > while impo...

numbering replies in outlook 2000
Could anybody please tell me how replies can be numbered in outlook. For example: Re[2], Re[3] A reply would be greatly appreciate ----------------------------------------------- ~~ Message posted from http://www.OutlookForum.com ~~ View and post usenet messages directly from http://www.OutlookForum.com ...