Formula help error of #VALUE!

[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

 

[carlo]

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

 

[T. Valko]

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

 

[Bob Bob]

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?
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

 

[Bob Bob]

Thank this work as well as the other post

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