MAP(TRIMRANGE(), LAMBDA into Template sheet

[LVExcel]

Hi all, this formula was recently presented for a question on BYROW

=MAP(TRIMRANGE(AC8:AC38),LAMBDA(row,IF(row>AY$7,"OVER","UNDER")))

Can it be customized to get data from another sheet?

I have two sheets, sheet-1 is my template used to import data to our billing system, it requires a particular format and column order.
Then I have my calculated detail data in sheet-2 with hundreds of columns and thousands of rows with my formulas within the detail.
I only need about 20 columns from Sheet-2 into my template (Sheet-1) in the correct order, but don't want to always have to copy formulas down, and check that I'm capturing every row in sheet-2, which constantly changes number of rows.

I tried to customize the formula but am not getting the correct results.

Example: Sheet-1 Column A1 will include all the data in Sheet-2 Column G:G; B1 will present all rows in E:E, and so forth.

Thanks in advance for your help.

 

[Georgiboy]

Yes, you can get it to work from another sheet and extend the range, as below:

=MAP(TRIMRANGE(Sheet2!AC8:AC10000),LAMBDA(row,IF(row>AY$7,"OVER","UNDER")))

However, with that particular formula, you wouldn't need to use MAP or LAMBDA, see below:

=IF(TRIMRANGE(Sheet2!AC8:AC10000)>AY$7,"OVER","UNDER")

 

[Peter_SSs]

You can also use Trim Refs to write the formula a little more succinctly than with the actual TRIMRANGE function. For example, this would do the same job as the last formula in the above post

=IF(Sheet2!AC8.:.AC10000>AY$7,"OVER","UNDER")

or if your data is set up so that there is always data starting at AC8 you would only need to trim the range at the bottom

=IF(Sheet2!AC8:.AC10000>AY$7,"OVER","UNDER")

 

[LVExcel]

Yes, you can get it to work from another sheet and extend the range, as below:

=MAP(TRIMRANGE(Sheet2!AC8:AC10000),LAMBDA(row,IF(row>AY$7,"OVER","UNDER")))

However, with that particular formula, you wouldn't need to use MAP or LAMBDA, see below:

=IF(TRIMRANGE(Sheet2!AC8:AC10000)>AY$7,"OVER","UNDER")

Thank you very much, that was very helpful.

 

[LVExcel]

You can also use Trim Refs to write the formula a little more succinctly than with the actual TRIMRANGE function. For example, this would do the same job as the last formula in the above post

=IF(Sheet2!AC8.:.AC10000>AY$7,"OVER","UNDER")

or if your data is set up so that there is always data starting at AC8 you would only need to trim the range at the bottom

=IF(Sheet2!AC8:.AC10000>AY$7,"OVER","UNDER")

Thank you very much Peter for all your help, that is simpler.

Follow-up Question for you:
Is there a way to use a combination of these functions in a formula to simply show data from another sheet.
I need to Map data from a couple of columns in Sheet2 to Sheet1, no calculations needed. What I'm looking to do is autofill which ever column from Sheet2 via an autofill formula, and not continue to check back to see if I'm capturing everything from Sheet2.

 

[Peter_SSs]

Thank you very much Peter for all your help, that is simpler.

You're welcome. Glad to contribute. Thanks for the follow-up.

Is there a way to use a combination of these functions in a formula to simply show data from another sheet.
I need to Map data from a couple of columns in Sheet2 to Sheet1, no calculations needed. What I'm looking to do is autofill which ever column from Sheet2 via an autofill formula, and not continue to check back to see if I'm capturing everything from Sheet2.

The best way might depend on exactly what you are trying to do and I'm not sure I understand what that is.
Do you mean if Sheet2 is like this ..

LVExcel.xlsm
	A	B	C
1	A1	B1	C1
2	A2	B2	C2
3	A3	B3	C3
4	A4	B4	C4
5	A5	B5	C5
6		B6	C6
7		B7	C7
8		B8	C8
9		B9	C9
10		B10
11		B11
12		B12
13
Sheet2

.. and you want to capture columns C and A then you might choose one of these methods. In the second method, where I have used row 1000, just choose a number bigger that your data will ever be.

LVExcel.xlsm
	A	B	C	D	E	F
1	C1	A1			C1	A1
2	C2	A2			C2	A2
3	C3	A3			C3	A3
4	C4	A4			C4	A4
5	C5	A5			C5	A5
6	C6				C6
7	C7				C7
8	C8				C8
9	C9				C9
10
Sheet1

Cell Formulas
Range	Formula
A1:B9	A1	=LET(a,TRIMRANGE(CHOOSECOLS(Sheet2!A:Z,3,1)),IF(a="","",a))
E1:E9	E1	=LET(a,Sheet2!C1:C1000,FILTER(a,a<>""))
F1:F5	F1	=LET(a,Sheet2!A1:A1000,FILTER(a,a<>""))
Dynamic array formulas.

If this is not what you meant then please give a small dummy sample and the expected results with XL2BB and explain again in relation to that sample data.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)