A formula for an OR function

[Susan]

I need a formula to refer to the same cell on multiple sheets to transfer
information to another sheet. The Excel Help directions to me to enter an =
sign and then the name of the function, and then an opening parethesis. Then
for each sheet hold down shift and click on the sheet tab and then the cell.
This is not working. I don't know what the name of the function is.

I have been entering: =('Soil Scientist'!D1,'Engineer'!D1,'Soil
COnservationist'!D1). I've tried adding OR and LOOKUP between the equal sign
and the parenthesis.

These are sheets where people enter their name into cell D1 on one of the 3
sheets, and it transfers it to another sheet, which is a fill-in form.

Susan

 

[Sandy]

Susan
If Soil Scientist'!D1=Jill Engineer'!D1=Bill Soil Conservationist'!D1=Bob,
what are you looking for the formula to return?
Sandy

 

[Susan]

The response would be their name that they type into cells D1 on any one of
the 3 spreadsheets, depending whether they are a Soil Scientist, an Engineer,
or a Soil Conservationist.

Thanks for responding,
Susan

 

[Pete_UK]

Susan,

you could have 3 hyperlink functions on the main sheet which would take
the user directly to cell D1 on the appropriate sheet if they click
"Soil Scientist" (for example). Is this what you want to do?

Pete

 

[Susan]

No. One of the 3 spreadsheets with career titles will be filled in with
various info, depending on the person's specific career and transferred to
the 4th sheet, which is a form.

Susan

 

[Susan]

I was using the formula ='Soil Conservationist'!D1 within the form when I
only had one career spreadsheet and it transferred the info from the Soil
Conservationist sheet to the form just fine. But when I added the other 2
sheets for Engineer and Soil Scientist, it wouldn't return anything.

Susan