Formula help error of #VALUE!

  • Thread starter Thread starter Bob Bob
  • Start date Start date
B

Bob Bob

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
 
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
 
=(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))
 
Thank this work as well as the other post


carlo said:
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
Click to expand...
 
Thank this work as well as the other post
T. Valko said:
=(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))
Click to expand...
 
Back
Top