Use formula in a second file to obtain values in multiple cells in first file

[unkysammy]

I'd like to be able to get a value in a cell based on the value in another cell using the formula in another sheet/file - multiple times in the first sheet.

By way of simple example - let's say there are two files.

The main file "File1", which is where I want to input a value into one cell and get a response in another cell. The formula is in another file "File2.

(I'll use a simple formula in this example, but in the real problem it's a complex file with lookup tables etc.)

In File2, there is a formula in B1, such that inputting a value into A1 returns a value into B1, e.g.

B1=A1*2

In File1, if I input a value into A1, I want the answer to be returned in B1 based on the formula in File2, e.g.

if I enter "2" into A1, I get "4" in B1.

BUT, I want to be able to reference the same formula in File2 multiple times, i.e.in File1

if I input "3" into A2 I get "6" in B2

if I input "4" into A3 I get "8" in B3

and so on

i.e. the formula in B1, B2, B3 etc. of File1 needs to somehow put the value of A1, A2, A3 etc. into A1 of File2 and retrieve the response of B1 in File2, EVERY TIME.

Is this possible?? My googles have failed so far, but perhaps there is a standard function for this and I just don't know what to search.

Any help would be greatly appreciated!

[#2]

@unkysammy 

The recently introduced LAMBDA function enables you to do this with a "user-defined function" within a single workbook. I use this extensively in one of my workbooks, a workbook that has many tabs (sheets) within the single workbook.

I'm not aware of anything that would let you do it across workbooks (AKA "files") although I'd happily be corrected on that. Here's a link with a good description of LAMBDA and how it can be used.

[#3]

@mathetes  thanks for your reply! 

OK ... I'm starting to see how this might work, though I don't think I properly understand the syntax of the LAMBDA function just yet

(I should hopefully be able to work within 1 workbook with multiple sheets, by importing the data from the other workbook)

See example file - in Sheet1, I want to be able to put the values in column 2 and get the responses in column 3, by reference to the formula in B3 of Sheet2 (i.e. as though B2 of Sheet2 contained the relevant value in column 2 of Sheet2)

i.e. without putting the formula itself in the LAMBDA function (because in the real thing the formula is very complex)

Is this possible?? 

EDIT - forum doesn't allow uploading files - here's the Sheets

Sheet1

	ENTER VALUE	ANSWER
1		=LAMBDA(Sheet2!B3, Sheet2!B2)(B2)
2
3

Sheet2

FORMULA INPUT
FORMULA OUTPUT	=B2*2

[#4]

You can put a copy of your file, including the complicated formula, on OneDrive or GoogleDrive, and then post a link here that grants others access. Let's do that.