Forum Discussion
Use formula in a second file to obtain values in multiple cells in first file
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.
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 |
- 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.