How to pull data with Specified header text from one sheet to another sheet ? Data attached, click on link

[Bhavana95]

Result should be in Data result tab

 

[CSmith]

Try using table and/or spill formulas.

 

[Bhavana95]

Thank you for the reply but it is not working

 

[CSmith]

Try this one:

Rates &Taxes.xlsx

Raw data Company Code,Fiscal Year,Account,BS PL,Sch Ref,SAP / Oracle,Description,Grouping,Sub-grouping,Document Number,Document Type,Posting Period,Year/month,Reference,Document Header Text,Document Date,Posting Date,Profit Center,Amount in doc. curr.,Document currency IN40,2022,7839000000,PL,2...

docs.google.com

 

[Peter_SSs]

Try this one:

Rates &Taxes.xlsx

Raw data Company Code,Fiscal Year,Account,BS PL,Sch Ref,SAP / Oracle,Description,Grouping,Sub-grouping,Document Number,Document Type,Posting Period,Year/month,Reference,Document Header Text,Document Date,Posting Date,Profit Center,Amount in doc. curr.,Document currency IN40,2022,7839000000,PL,2...

docs.google.com

@CSmith Please refer to #4 of the Forum Rules, particularly the last two paragraphs. Providing a sample workbook is allowed, but the formulas/vba code/power query code etc should also be provided directly in the forum. The reasons for that are outlined in the rule mentioned.

 

[CSmith]

@CSmith Please refer to #4 of the Forum Rules, particularly the last two paragraphs. Providing a sample workbook is allowed, but the formulas/vba code/power query code etc should also be provided directly in the forum. The reasons for that are outlined in the rule mentioned.

My apologies.
Here is the info of first three columns

Rates &Taxes.xlsx
	A	B	C
3	Company Code	Fiscal Year	Account
4	IN40	2022	7839000000
Data result

Cell Formulas
Range	Formula
A4:C394	A4	=IF(ISBLANK(INDIRECT("tblRawData["&A3&"]")),"",INDIRECT("tblRawData["&A3&"]"))
Dynamic array formulas.

 

[Fluff]

Another option for 2021 or 365

=FILTER('Raw data'!A3:AB1000,ISNUMBER(MATCH('Raw data'!A2:AB2,A3:O3,0)))

 

[Bhavana95]

Try this one:

Rates &Taxes.xlsx

Raw data Company Code,Fiscal Year,Account,BS PL,Sch Ref,SAP / Oracle,Description,Grouping,Sub-grouping,Document Number,Document Type,Posting Period,Year/month,Reference,Document Header Text,Document Date,Posting Date,Profit Center,Amount in doc. curr.,Document currency IN40,2022,7839000000,PL,2...

docs.google.com

Hi Thank you for the reply but the formula is not working and can u please explain formula as well, so that i can use this for other data sets as well

 

[CSmith]

Hi Thank you for the reply but the formula is not working and can u please explain formula as well, so that i can use this for other data sets as well

You should be able to download here the xlsx version using indirect spilled table column references. Apologies it did not seem to translate across to google sheets well.
Rates & Taxes.xlsx

MrExcel-1235574 - Rates &Taxes.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS
3	Company Code	Fiscal Year	Account	BS PL	Sch Ref	SAP / Oracle	Description	Grouping	Sub-grouping	Document Number	Document Type	Posting Period	Year/month	Reference	Document Header Text	Document Date	Posting Date	Profit Center	Amount in doc. curr.	Document currency	Amount in local currency	ABS	Remarks	Local Currency	Amount in loc.curr.2	Local currency 2	Eff.exchange rate	Text	User name	Clearing date	Clearing Document	Vendor	Vendor Name	Offsetting acct no.	Purchasing Document	Sales Document	Trading Partner	Material	WBS element	Order	Sales Document Item	Entry Date	Cost Center	Assignment	Purchasing Document	Vendor Type	Vendor Number	G/L Account	Net due date	Asset Subnumber	Asset	Payment date	Customer	Bill to party	Ship to party	Sold to party	Customer Code	Customer Name	Customer Pan Number	Customer Tan Number	InvoiceNo.TDS deduct	Invoice date	Invoice Amount	Invoice reference	Terms of Payment	Aging bucket	No of days aged	Aging date	Vendor Status	Related Inv. Is Customer-Disputed	Tax code
4	IN40	2022	7839000000	PL	22	SAP	Other Taxes (not Income) Expense	Other expenses	Rates and taxes	100018681	ZA	12	2022/12		IJAT-40825	9/30/2022	9/30/2022	IN000	16200000	INR	16200000	16200000		INR	203958	USD	1,00000	Being reclass of tax cost which was booked under s	A3200108												0	10/4/2022	IN4088008	01000186812022				7839000000													186812022		0	100018681		6	2913632				VT
Data result

Cell Formulas
Range	Formula
A4:BS394	A4	=IF(ISBLANK(INDIRECT("tblRawData["&A$3&"]")),"",INDIRECT("tblRawData["&A$3&"]"))
Dynamic array formulas.

 

[Fluff]

Thank you for the reply but the formula is not working

Did you try the formula I suggested, it will be far more efficient than using Indirect which is very slow & volatile