Formula help error of #VALUE!

I am not sure I can explain this clearly but I will give it a shot

The cells are formatted to show time
Cell  A4 you enter in 1 or 2 to have cell A3 show the info from cell A2 (if
1) or B2 (if2)

Formula for Cell A3 is =(COUNTIF(A4,"=1")*A2)+(COUNTIF(A4,"=2")*B2)
It goes up to COUNTIF(A4,"=8")*H2)

Problem is this is a time sheet so cells are formatted for time like 8:00:00
AM. Cell B2 I need to be text (Vacation) so staff will get paid vacation day
and not show blank like a day off. When I enter text in cell B2 cellA3 shows
#VALUE!
How can I set this up so one can enter 1 and get a time in Cell A3 or 2 and
get text in Cell A3?

Hope I made this clear thank to all how help.

PS There is a summery sheet at the end which adds up hours in the row A3


0
NOeMAILl (15)
12/20/2007 4:15:06 AM
excel 39879 articles. 2 followers. Follow

4 Replies
422 Views

Similar Articles

[PageSpeed] 3

I am not sure, if i understand you completely, but you want to show
a cell according to the value of another cell, right?
you could either do:
=INDIRECT(ADDRESS(2,A4))
or
=OFFSET(A2,0,A4-1)

which is easier then you're formula.

Try to put the format of the cell to General
and give feedback if it works

Carlo


On Dec 20, 1:15 pm, "Bob Bob" <NOeMA...@mail.com> wrote:
> I am not sure I can explain this clearly but I will give it a shot
>
> The cells are formatted to show time
> Cell  A4 you enter in 1 or 2 to have cell A3 show the info from cell A2 (if
> 1) or B2 (if2)
>
> Formula for Cell A3 is =(COUNTIF(A4,"=1")*A2)+(COUNTIF(A4,"=2")*B2)
> It goes up to COUNTIF(A4,"=8")*H2)
>
> Problem is this is a time sheet so cells are formatted for time like 8:00:00
> AM. Cell B2 I need to be text (Vacation) so staff will get paid vacation day
> and not show blank like a day off. When I enter text in cell B2 cellA3 shows
> #VALUE!
> How can I set this up so one can enter 1 and get a time in Cell A3 or 2 and
> get text in Cell A3?
>
> Hope I made this clear thank to all how help.
>
> PS There is a summery sheet at the end which adds up hours in the row A3

0
12/20/2007 4:57:32 AM
=(COUNTIF(A4,"=1")

You don't need the " " or the =.

I'm sure there's a better way to do this but I'm struggling to understand 
your setup and your intent...

However, this will work:

=COUNTIF(A4,1)*MAX(A2)+COUNTIF(A4,2)*MAX(B2)

Maybe this:

=MAX(INDEX(A2:H2,A4))

-- 
Biff
Microsoft Excel MVP


"Bob Bob" <NOeMAILl@mail.com> wrote in message 
news:e%laj.21345$Tx.9156@pd7urf3no...
>I am not sure I can explain this clearly but I will give it a shot
>
> The cells are formatted to show time
> Cell  A4 you enter in 1 or 2 to have cell A3 show the info from cell A2 
> (if
> 1) or B2 (if2)
>
> Formula for Cell A3 is =(COUNTIF(A4,"=1")*A2)+(COUNTIF(A4,"=2")*B2)
> It goes up to COUNTIF(A4,"=8")*H2)
>
> Problem is this is a time sheet so cells are formatted for time like 
> 8:00:00
> AM. Cell B2 I need to be text (Vacation) so staff will get paid vacation 
> day
> and not show blank like a day off. When I enter text in cell B2 cellA3 
> shows
> #VALUE!
> How can I set this up so one can enter 1 and get a time in Cell A3 or 2 
> and
> get text in Cell A3?
>
> Hope I made this clear thank to all how help.
>
> PS There is a summery sheet at the end which adds up hours in the row A3
>
> 


0
biffinpitt (3172)
12/20/2007 5:14:21 AM
Thank this work as well as the other post


"carlo" <carlo.ramundo@gmail.com> wrote in message
news:ba748400-852e-4ae5-acf1-d485aad2a688@i29g2000prf.googlegroups.com...
> I am not sure, if i understand you completely, but you want to show
> a cell according to the value of another cell, right?
> you could either do:
> =INDIRECT(ADDRESS(2,A4))
> or
> =OFFSET(A2,0,A4-1)
>
> which is easier then you're formula.
>
> Try to put the format of the cell to General
> and give feedback if it works
>
> Carlo
>
>
> On Dec 20, 1:15 pm, "Bob Bob" <NOeMA...@mail.com> wrote:
> > I am not sure I can explain this clearly but I will give it a shot
> >
> > The cells are formatted to show time
> > Cell  A4 you enter in 1 or 2 to have cell A3 show the info from cell A2
(if
> > 1) or B2 (if2)
> >
> > Formula for Cell A3 is =(COUNTIF(A4,"=1")*A2)+(COUNTIF(A4,"=2")*B2)
> > It goes up to COUNTIF(A4,"=8")*H2)
> >
> > Problem is this is a time sheet so cells are formatted for time like
8:00:00
> > AM. Cell B2 I need to be text (Vacation) so staff will get paid vacation
day
> > and not show blank like a day off. When I enter text in cell B2 cellA3
shows
> > #VALUE!
> > How can I set this up so one can enter 1 and get a time in Cell A3 or 2
and
> > get text in Cell A3?
> >
> > Hope I made this clear thank to all how help.
> >
> > PS There is a summery sheet at the end which adds up hours in the row A3
>


0
NOeMAILl (15)
12/21/2007 1:59:56 AM
Thank this work as well as the other post
"T. Valko" <biffinpitt@comcast.net> wrote in message
news:um1YucsQIHA.1204@TK2MSFTNGP03.phx.gbl...
> =(COUNTIF(A4,"=1")
>
> You don't need the " " or the =.
>
> I'm sure there's a better way to do this but I'm struggling to understand
> your setup and your intent...
>
> However, this will work:
>
> =COUNTIF(A4,1)*MAX(A2)+COUNTIF(A4,2)*MAX(B2)
>
> Maybe this:
>
> =MAX(INDEX(A2:H2,A4))
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "Bob Bob" <NOeMAILl@mail.com> wrote in message
> news:e%laj.21345$Tx.9156@pd7urf3no...
> >I am not sure I can explain this clearly but I will give it a shot
> >
> > The cells are formatted to show time
> > Cell  A4 you enter in 1 or 2 to have cell A3 show the info from cell A2
> > (if
> > 1) or B2 (if2)
> >
> > Formula for Cell A3 is =(COUNTIF(A4,"=1")*A2)+(COUNTIF(A4,"=2")*B2)
> > It goes up to COUNTIF(A4,"=8")*H2)
> >
> > Problem is this is a time sheet so cells are formatted for time like
> > 8:00:00
> > AM. Cell B2 I need to be text (Vacation) so staff will get paid vacation
> > day
> > and not show blank like a day off. When I enter text in cell B2 cellA3
> > shows
> > #VALUE!
> > How can I set this up so one can enter 1 and get a time in Cell A3 or 2
> > and
> > get text in Cell A3?
> >
> > Hope I made this clear thank to all how help.
> >
> > PS There is a summery sheet at the end which adds up hours in the row A3
> >
> >
>
>


0
NOeMAILl (15)
12/21/2007 2:00:04 AM
Reply:

Similar Artilces:

Help with formula #18
Hi All: I am WAY over my head here, and you all have always been great help, so hopefully someone can help with this. I had someone write this for me to use as an index at the top of a spread sheet. It works beautifully . The problem is I would like to move this index to sheet 2 of the same spreadsheet and when I copy and paste it, it does'nt work. Is there anyone who can modify this to work as an index in sheet 2 and will search sheet 1 for the results? TIA Keith =IF(#REF!="","",IF(ISNA(MATCH(#REF!,$H$31:$H$65536,0)),"",HYPERLINK("#"&C...

VB Code help
Here is a portion of one of my VB Macros in Excel: If Selection.Count > 0 Then MsgBox ("Average = ") & (RunningTotal / Selection.Count), vbInformation, "Average" End If My question: How do I format the number (RunningTotal/Selection.Count) to look like 0.000, instead of 0.0000000000000000?? What should my code look like with the new formatting. Thanks Chris Chris, Try this: ....Format((RunningTotal / Selection.Count), "0.000")... -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- &qu...

Help needed with Money 2003 files
Hi there, I've been using money 2003 for years even when upgrading to new PC's as I had the original disk. I've just had to rebuild my hard drive and now cannot find the disk anywhere so cannot install Money. As you cannot now buy Money, I've tried downloading the trial version of Money so that I can access all the account details/balances but it says that they're not compatible with earlier versions of money. Is there anything that I can do to get these files open or have I lost all my records for the last 6 years? I'd really appreciate your assistance. Jayne EggHead...

Move Mailbox errors
I'm currently migrating from Exchange 2000 to Exchange 2003. We're in a native mode Windows 2003 AD. I am migrating about 500 mailbox from a standalone Exchange 2000 to a WIndows 2003 / Exchange 2003 Active/passive cluster configuration. Mailbox moves have been going great up until yesterday. While moving the users in the normal way,2 mailboxes hung at 100%. In the "state" column, it says: "Saving changes to the directory". I let it sit at this stage for more than 10 hours. I then have to cancel the task, then it says cancel running tasks, and this wil...

outlook 2007 error message in archive folders
for no discernable reason some and only some of my archive folders will no longer open and give the following error message: the message interface has returned an unknown error. If the problem persists restart Oultlook I have restarted not only outlook but rebooted my computer with no positive result. I have also gone back a month to a restore point but this has not changed the position. As I say it is only some files and then only some subfiles in larger files. One month ago there were no issues. I have added no new programs to the computer Try running scanpst.exe against y...

How do you combine two columns to one? Help!!!!!!
I have address numbers in column A and street names in column B, I want to combine all of both columns into one without going through the whole process for each individual cells. HELP!!!!!! "sttrumpet" <sttrumpet@discussions.microsoft.com> wrote in message news:F72E43CE-CA99-4CD0-BCA1-85EFADCB459D@microsoft.com... > I have address numbers in column A and street names in column B, I want to > combine all of both columns into one without going through the whole process > for each individual cells. HELP!!!!!! Use the fuction =A1&B1 in cell B1 Drag the function ...

List Box Values
Can I use values from a list box/combo box in a formula and if so how do I do that? TIA Sorry wrong NG "Mike Cramsey" <mcrams@adams.net> wrote in message news:%23b6IdNn1EHA.3908@TK2MSFTNGP12.phx.gbl... > Can I use values from a list box/combo box in a formula and if so how do I > do that? > TIA > ...

change null to a value of 0
I have a control on a form with a default value of "0" that users have occasionally started to change the value in the control, but then exit the form with a Null value. This affects various reports that include this data. I'd like to have the value return to 0 either in the event they exit the control without typing in a value. I am assuming it would require code in the On Exit Event of that control, but I am not sure what code would accomplish this. I would appreciate any help!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-f...

If Cell Color is yellow set adjacent cell to value
Could someone help me with the VB for this please. I want to search column D and for each cell in column D that i highlighted Yellow I want to set the Value of that cell to Column G i the same row. So if column D15 is highlighted yellow and has a value of 10 I wan cell G15 to be set to 10. This will save me so much time. Thanks for your help. S -- Message posted from http://www.ExcelForum.com Hi I think you should start by looking at Chip's site: http://www.cpearson.com/excel/colors.htm I reckon you'll need to set up a User Defined Function for it. -- Andy. "serhat &...

how do you create a cumulative line graph? i.e. it adds each valu.
With a column that adds the appropriate values? If you want to continue this discussion, please use the body of the message for your post. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <D8605870-E872-4407-A375-AC74778F4A8F@microsoft.com>, cmurphy@discussions.microsoft.com says... > > ...

Error Number: 0x800ccc0e when I try to get messages
Hello, I am a postmaster of our network and I am having some problems with Outlook 2000 and our pop3 server. The problem is that randomly, Outlook's users can not download their mail because they get the error number 0x800ccc0e when they try to send amd receive mail. We are using Microsoft Outlook 2000 SP-3 (9.0.0.6627) and our server is a Linux Debian running a 2.4.27 kernel. I have tried with ipop3d and in.qpopper as a pop3 daemons and with both I get the error. There is no firewall between them and this is the tcpdump capture from the server (our pop3 server runs at port 40110, bu...

80040e19 error occurring with 2nd NIC installed
I am experiencing the 80040e19 error when expanding public folders in Exchange System Manager, Exchange 2000. I have exhausted all suggested MS articles and *still* have not resolved the issue. No evidence of URLScan or iislockdown being run on the server. Strange thing is is that when I disable the newly installed NIC through device manager the public folders can be expanded perfectly. On enabling the NIC, same error. The server is the PDC, Exchange and Intranet server, running IIS on port 80 for local intranet site and the admin website on port 7715. I have checked the host head...

Minor gridline values at tick marks
Couldn't find my earlier question on this matter. I have an XY scatter graph with log10 axes. How do I display the minor gridline values between the powers of 10 (between .1 and 1, between 1 and 10, and so on)? As an aside, how do I look for all my posts, especially if I don't recall which Excel category in which it was posted? Thanks Hi, There may be more elegant and straight-forward methods that I don't know. But you can mimic what you want by adding two more series to your graph as described below: Assuming that the x- and y- axes are in the decades ...

Need help with using a bar graph in a report
I have a report that separates information about representatives by manager. I need the graphs to give a quick summary on a few stats from each rep but only show for that manager's team and not all information. For example: Manager A has RepA, RepB, and RepC under him. Each rep has Aux1, Aux2, Aux3 information which needs to be displayed. My goal is to show Manager A with his Reps A,B, and C of their Aux1, 2, and 3. Then, under the next manager Header I want Manager B to show Rep D, E, and F with their Aux1, 2, and 3 information. Etc thru Manager G. Please help... Th...

Formula problem #9
Hi, I have entered a small formula in a cell =A1+B1 the result is also fine but if i have edit the formula i have to again type the entire thing. Is there a shortcut to this where i can easily do the editing of the formula, i guess i am missing something out here. Hi, I guess you are not able to see the formula in the formula bar if it is so then go to Tools|Options|View|check the formula bar check box|ok -- _______________________ Click "Yes" if it helps ________ Thanks Suleman Peerzade "Niroo" wrote: > Hi, > > I have entered a small formula in a cell ...

space then date causes error
On my spreadsheet they have to type in a date and other calculations are taken from this point. Of the hundreds that have used it there have been no problems, however as always there will always be one....... When they fill in the date they type a space and then type the date, this throws up errors in the other calculations. Not altogether important but is there an easy fix for this, so that if anyone types a space it is ignored, I dont want to get involved in VBA/macro stuff. Right click the sheet tab>view code>insert this>modify range to suit Private Sub Worksheet_Change(By...

Copy sheet in Excel
Hi Wonder if anyone of you encounter this problem before? When I try to copy worksheet in a workbook, the following prompt comes up, indicating name conflict. "A formula or sheet you want to move or copy contains the name 'aaaa', which already exists on the destination worksheet. Do you want to use this version of the name? - To use the name as defined in the destination sheet, click Yes - To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box" When I clicked yes, another prompt comes up indicating ...

Open CSV files without auto-formatting values
Hello, I am having difficulties while opening CSV files: If there is value i.e "6.1" which is decimal, Excel interprets it as date = 01. June (According to regional date settings) If there is value "16.04.07" which is actually date 2007-04-16, Excel again converts it according to regional settings, and finally there is 2016.04.07 Is there any settings or macros available, so I can open CSV file, and treat each column just as TEXT ? without any auto-guessings about cell type. Import wizard is not good solution this time. Also playing with regional settings is not ...

Help with Userform
I need to setup a restricted means of allowing users to edit data in existing cells on a spreadsheet through a macro (userform?). I want to create a userform that will read data from these existing cells and display their contents in a series of text boxes. The user will then have the choice to edit any data item, if they need to, or leave the data as is. Upon the user hitting the OK key on the userform, the edited data will be entered in their previous cell locations. Any help or examples will be appreciated! Thanks ...

ameritrade plus error
When I try to download my information from ameritrade plus i get a invalid org error. Anyone else experience this problem. Help Please.. >When I try to download my information from ameritrade >plus i get a invalid org error. Anyone else experience >this problem? YES......EVERYONE!!!!!!!!! ...

Accessing the schema type value from XSLT ...
Folks, Have any of you figured out a way in .NET 1.1 to access the scheme type of a Xml node from XSLT? Appreciate any pointers or code snippets that you can provide. After some extensive research (& head-scratching) I understand I need the PSVI(Post Schema Validated Infoset). I can't seem to figure out how to use PSVI with XSLT. Am I on the right track here? Thanks, -Naraendira -- NaraendiraKumar R. R. wrote: > Have any of you figured out a way in .NET 1.1 to access the scheme type of a > Xml node from XSLT? Appreciate any pointers or code snippets that you can >...

Money2002 Setup Error
When I try to setup (windows98) I get the following: "This program has performed an illegal operation and will be shut down. If the problem persists, contact the program vendor." Here are the details: SETUP executed an invalid instruction in module ADVPACK.DLL at 015f:715f5858. Registers: EAX=0066cf04 CS=015f EIP=715f5858 EFLGS=00010206 EBX=0066d364 SS=0167 ESP=0066cdf4 EBP=0066d014 ECX=0066f8d0 DS=0167 ESI=00000348 FS=6017 EDX=0044003c ES=0167 EDI=0066d350 GS=0000 Bytes at CS:EIP: fe ff ff 50 ff 15 68 11 5f 71 56 8d 85 fc fe ff Stack dump: 00000080 bff77b14 71604004 00667000 0...

Change cell value between EMPTY and X when i click on the cell.
Hello, How can i change the cell value between empty and X when i click on the cell. This is for the cells in column B of all the visible sheets. Can you provide some code please..... Thanxxxx, Luc Paste this code into all visable sheets and change the range of B1:B10 to your needs Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("B1:B10")) Is Nothing Then Target.Value = "X" End If End Sub "Luc" wrote: > Hello, > > How can i change the cell value between empty...

sum negative and positive values separately
Hi, I have a time series like this: 1995, -.05 1996, -2.1 1997, 3.6 1998, 4.3 1999, -0.7 2000, -0.08 and I would like to sum just the positive and negative value separately in this way 1995, -2.15 1997, 7.9 1999, -0.78 And I don't know if it is To sum only positive values use =SUMIF(B1:B10,">0") and to sum only negative values =SUMIF(B1:B10,"<0") But I cannot see how you get the second tables from the first. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "bora" <bora@unisi.it> wrote in message news:1...

Currency values set to 2 decimal places
Is it possible to set a currency value fixed to 2 decimal points a opposed to just rounded up or down. for example if 33.3% discount was applied to �10 it would be �6.66666 which would display as �6.67 Therefore if you had 3 x �6.67 it woul work it out as �20.00 and not �20.01. Pau -- clappu ----------------------------------------------------------------------- clappus's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1613 View this thread: http://www.excelforum.com/showthread.php?threadid=27569 Yes, set the format to Currency, and that will work. -- HTH R...