Return value based on text/ value in another cell

[1988craig]

Hello everyone,

I am attempting to auto populate table two, based on the input of table one.

Table one is a manual input of man hours.
Table two is the available hours for planning purposes- where there is a numerical input in table 1, I want table two to populate 7 in the corresponding cell.

where text is entered in table one this will need to represent some form of '0' in table two.

Not sure where to start with this as the text & numerical data varies in table 1.

Unfortunately I cannot change the format of table one due to this being used/ owned by another party but I need table two to populate as per above for the available labour hours for planning purposes.

Greatly appreciate any assiatance, thanks in advance.

Example .xlsx
	A	B	C	D	E	F
2		Table 1. manual input
3
4		Monday	Tuesday	Wednesday	Thursday	Friday
5	Dave 1	8.5	RDO	8.5	AL	8.5
6	Dave 2	9	8.5	9	AL	7.5
7	Dave 3	7.5	ERT	7.5	AL	ERT
8	Dave 4	8.5	7.5	TR	AL	7.5
9	Dave 5	8.5	7.5	TR	RDO	7.5
10	Dave 6	RDO	7.5	8.5	AL	TR
11	Dave 7	7.5	7.5	ERT	AL	TR
12	Dave 8	7.5	8.5	7.5	AL	7.5
13	Dave 9	7.5	9	7.5	AL	8.5
14	Dave 10	7.5	7.5	7.5	AL	RDO
15
16		Table 2. from input table 1.
17
18		Monday	Tuesday	Wednesday	Thursday	Friday
19	Dave 1	7	-	7	AL	7
20	Dave 2	7	7	7	AL	7
21	Dave 3	7	-	7	AL	-
22	Dave 4	7	7	-	AL	7
23	Dave 5	7	7	-	-	7
24	Dave 6	-	7	7	AL	-
25	Dave 7	7	7	-	AL	-
26	Dave 8	7	7	7	AL	7
27	Dave 9	7	7	7	AL	7
28	Dave 10	7	7	7	AL	-
Sheet1

 

[jasonb75]

Based on your example, the simple way would be to enter

=IF(ISNUMBER(B5),7,0)

into B19, then fill the table from that.

 

[1988craig]

Based on your example, the simple way would be to enter

=IF(ISNUMBER(B5),7,0)

into B19, then fill the table from that.

Hi Jason,

Many thanks, that was a lot simpler than I thought the formula would be (I'm an excel novice...) . Very much appreciated!!