Forum Discussion
Pull employee payroll details from multiple payrolls
To fully automate this sheet we need to restructure data, not sure right now how to do that better.
As intermediate variant we may generate data for one employee block, ID will be the source. Plus rename sheets having everywhere 3-letterrs month name (June 2023 -> Jun 2023).
With that into C1 we return the name
=LET( Result, XLOOKUP( $H1, VSTACK('Jan 2023:Dec 2023'!$A$4:$A$100), VSTACK('Jan 2023:Dec 2023'!$B$4:$B$100) ), IFERROR(Result, "no or wrong data") )Into B4 monthly data
=LET( GetMonth, "'" & TEXT($A4, "mmm yyyy") & "'", IdRange, "!$A$4:$A$100", DataRange, "!$C$4:$L$100", Result, XLOOKUP( OFFSET($A4, -MONTH($A4)-2,7), INDIRECT(GetMonth & IdRange), INDIRECT(GetMonth & DataRange) ), IFERROR(Result, "no or wrong data") )and drag it down to next months.
After copy entire range A1:K16, paste into the A17, enter another Id into this block and repeat. All IDs are in O3.
Sir,
Please find attached a sample excel file, the last tap is "Individual Yearly Summary" I want the data extracted in that particular excel sheet.
Thanks for looking into my difficulty, look forward for your further support.
Regards,
Ronald Pinto
To fully automate this sheet we need to restructure data, not sure right now how to do that better.
As intermediate variant we may generate data for one employee block, ID will be the source. Plus rename sheets having everywhere 3-letterrs month name (June 2023 -> Jun 2023).
With that into C1 we return the name
=LET(
Result, XLOOKUP(
$H1,
VSTACK('Jan 2023:Dec 2023'!$A$4:$A$100),
VSTACK('Jan 2023:Dec 2023'!$B$4:$B$100) ),
IFERROR(Result, "no or wrong data")
)Into B4 monthly data
=LET(
GetMonth, "'" & TEXT($A4, "mmm yyyy") & "'",
IdRange, "!$A$4:$A$100",
DataRange, "!$C$4:$L$100",
Result, XLOOKUP(
OFFSET($A4, -MONTH($A4)-2,7),
INDIRECT(GetMonth & IdRange),
INDIRECT(GetMonth & DataRange) ),
IFERROR(Result, "no or wrong data")
)and drag it down to next months.
After copy entire range A1:K16, paste into the A17, enter another Id into this block and repeat. All IDs are in O3.
Sir,
I copied the formula which you had provided by making some changes in identification of column and row range. I didn’t have issue with the first formula but with second one I have an issue.
In Cell C1 I copied the formula and name correctly returns, No issue with this below formula
=LET(Result,XLOOKUP($H1,VSTACK('JAN 2023:DEC 2023'!$B$5:$B$100),VSTACK('JAN 2023:DEC 2023'!$C$5:$C$100)),IFERROR(Result,"No Data") )
In Cell B5 I copied the formula and made required changes in Column name and number. The result is incorrect, I am not getting the details of employee, instead I am getting the total sums below each column. I have made the following changes
=LET( GetMonth, "'" & TEXT($A5, "mmm yyyy") & "'", IdRange, "!$B$5:$B$100", DataRange, "!$F$5:$V$100", Result, XLOOKUP( OFFSET($A5, -MONTH($A5)-2,7), INDIRECT(GetMonth & IdRange), INDIRECT(GetMonth & DataRange) ), IFERROR(Result, "Nil") )
The Excel sheet where I have copied this formula has to capture from 45 names (rows) in each monthly sheet. The Column used in the monthly sheet are from A to V and the data to be captured begins at Column F5 and goes upto Column V5 and below rows up to F45 to V45. In-between that in Column G5 to G45 is a remark column consisting of “Text”.
Can you please help to correct the formula.
Ronald
Perhaps that's due to that part of the formula
OFFSET($A4, -MONTH($A4)-2,7)For the sample file in previous post this part of formula for the Jan (cell B4) takes the value from the cell which is located on 3 ( -MONTH(A4)-2 = -1-2=-3) rows up from A4 and on 7 columns to the right. Other words value from the cell H1 which shall be 26 in the sample.
For the other data layout you shall adjust OFFSET accordingly.
- Yes Sir, your advice is correct. I applied it and the result is as desired.
Thanks for making my work life easy, you helped me save valuable time.
Thanks once again,
Ronald