SOLVED

help for formulae

Copper Contributor

need help to make a report sheet from data entry sheet. please see the attached screen shots. the numbers in cell of data entry are manually added. i want the same numbers automatically updated in "due from owners"  sheetScreenshot (465).pngScreenshot (466).png

5 Replies
I assume you need XLOOKUP() function. Can you please attach a sample file so that we can work on that. Also explain little more which data do you want to show on "Dues from owners" sheet. Do you want only show data from data entry sheet or want to sum or count data?

@Harun24HRGOA_Accounts_2022-23_trial.xlsx

 

THIS FORMULA '=TRANSPOSE('DATA ENTRY'!H141,IFS('DATA ENTRY'!H141=APRIL-22,'DATA ENTRY'!I141=3E)   IS NOT WORKING

 

FOR YOUR PERUSAL PLEASE

DUE FROM OWNERS IS THE REPORT. IF THE CELL IS BLANK MEANS DUE. IF IT HAS NUMBER MEANS NO DUE.
best response confirmed by KOTTARATHIL (Copper Contributor)
Solution

@KOTTARATHIL In S4 on the Dues sheet, use this:

=FILTER(Data_Entry!$H:$H,(Data_Entry!$F:$F=$R4)*(Data_Entry!$I:$I=S$3),"-")

But you also need to use text headings on row 4 that exactly match the ones used in column I of the Data sheet. Lastly, one entry for 3E is in March 2022. The list item in the dropdown is a real date. change that to a text as well "Mar-22".

Made these changes in the attached file.

GREAT, THANK YOU VERY MUCH
1 best response

Accepted Solutions
best response confirmed by KOTTARATHIL (Copper Contributor)
Solution

@KOTTARATHIL In S4 on the Dues sheet, use this:

=FILTER(Data_Entry!$H:$H,(Data_Entry!$F:$F=$R4)*(Data_Entry!$I:$I=S$3),"-")

But you also need to use text headings on row 4 that exactly match the ones used in column I of the Data sheet. Lastly, one entry for 3E is in March 2022. The list item in the dropdown is a real date. change that to a text as well "Mar-22".

Made these changes in the attached file.

View solution in original post