Formula help error of #VALUE!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Bob - 20 Dec 2007 04:15 GMT

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

Reply to this Message

carlo - 20 Dec 2007 04:57 GMT

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

> I am not sure I can explain this clearly but I will give it a shot
>
[quoted text clipped - 15 lines]
>
> PS There is a summery sheet at the end which adds up hours in the row A3

Reply to this Message

Bob Bob - 21 Dec 2007 01:59 GMT

Thank this work as well as the other post

> I am not sure, if i understand you completely, but you want to show
> a cell according to the value of another cell, right?
[quoted text clipped - 29 lines]
> >
> > PS There is a summery sheet at the end which adds up hours in the row A3

Reply to this Message

T. Valko - 20 Dec 2007 05:14 GMT

=(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))

Signature

Biff
Microsoft Excel MVP

>I am not sure I can explain this clearly but I will give it a shot
>
[quoted text clipped - 20 lines]
>
> PS There is a summery sheet at the end which adds up hours in the row A3

Reply to this Message

Bob Bob - 21 Dec 2007 02:00 GMT

Thank this work as well as the other post

> =(COUNTIF(A4,"=1")
>
[quoted text clipped - 35 lines]
> >
> > PS There is a summery sheet at the end which adds up hours in the row A3

Reply to this Message