Trick Excel believes concatenated formula is really a formula for

Question

I am responsible for planning and I have 2 excel files (workbooks). One file is called "Schedule (2016)," and the other file is called "My Schedule Assistant."

The file or book "Schedule (2016)" is divided into several sheets for each payment period and lists all my people and where they will be assigned. Separate worksheets indicate payment periods (sheet tabs) as "PR # 1 xxxx, PR # 2 xxxx, PR # 3 xxxx", etc.

The purpose of the “Schedule Assistant” is to help me ensure that I listed the employee on a specific day along with where they worked the entire payment period, and then another section that will read from “Schedules (2016)” and tells me the assigned area and the employee who worked there. It sounds complicated, but actually it is not.

In the “My Schedule Assistant,” cells B3: B46 list the employee service mark, while cells D3: D46 list their post-assignments. Cells AA1: A022 lists the destination and employee who worked there. He gets this information by reading one of the PR # sheets and works great.

Currently, I can only read from the specified "Schedule (2016)" worksheet using the formula:

 =IFERROR(INDEX('[Schedule (2016).xlsx]PR #26 DEC 25'!$C$4:$C$47,MATCH("WC",'[Schedule (2016).xlsx]PR #26 DEC 25'!$F$4:$F$47,0)),"None")

" ", . , , , - AA1: AO22 / Excels.

, , , I16 Excel . :

=IFERROR(INDEX('[Schedule (2016).xlsx]I16'!$C$4:$C$47,MATCH("WC",'[Schedule (2016).xlsx]I16'!$F$4:$F$47,0)),"None")

., . , Excel , .

?

Answer 1

range , formula.

, 3 range ( , ):

• PR # 26 DEC 25
• I16

( B2 ) Data Validation , .. (. .1 2):

1. PR #26 DEC 25,2. I16,3. Any Other

.1

.2

:

=IFERROR(INDEX(
CHOOSE(LEFT($B$2,1),
'[Schedule (2016).xlsx]PR #26 DEC 25'!$C$4:$C$47,
'[Schedule (2016).xlsx]I16'!$C$4:$C$47,
'[Schedule (2016).xlsx]Any Other'!$C$4:$C$47),
MATCH("WC",
CHOOSE(LEFT($B$2,1),
'[Schedule (2016).xlsx]PR #26 DEC 25'!$F$4:$F$47,
'[Schedule (2016).xlsx]I16'!$F$4:$F$47,
'[Schedule (2016).xlsx]Any Other'!$F$4:$F$47),0)),"None")

, B2 (. . 3).

.3

Answer 2

VALUE (.....) ?

IFERROR (VALUE (.....), " " ) , . .