Forum Discussion
JOGINDER SINGH
Feb 03, 2023Copper Contributor
Filling a form with excel function
Hello everyone. I need help of your's. Can you Please? I have attached an excel sheet for your reference. So please open that and help to resolve my problem mentioned therein
mtarler
Feb 03, 2023Silver Contributor
JOGINDER SINGH It isn't "pretty" but I believe this does what you need:
=LET(Data,A6:B12, mains, J6:L6, Flavours, I7:I14,
_m, TAKE(Data,,1), _s, TAKE(Data,,2),
MAKEARRAY(ROWS(Flavours),COLUMNS(mains),LAMBDA(r,c,SUM(IFERROR(--(TEXTBEFORE(TEXTSPLIT(","&TEXTJOIN(",",1,FILTER(_s,_m=INDEX(mains,c),"")),INDEX(Flavours,r)&"/",,1,1,""),",",,1,1,"")),0)))))
JOGINDER SINGH
Feb 04, 2023Copper Contributor
Thanks for giving your valuable time, But can you tell me that in which cell address this function to be apply?
- clynn8452Feb 06, 2023Copper Contributor
JOGINDER SINGH Hello. i need to extract data from and excel spreadsheet to fill out a form that i can customize. i used to do this years ago but cannot recall how i did it. i remember being able to set up the title fields of the date where i wanted them to go on the form. i also need to be able to print the forms.
- JOGINDER SINGHFeb 09, 2023Copper Contributorthanks
- mtarlerFeb 04, 2023Silver Contributordid you open the attachment? I re-created the headers (left and top) and then that formula is in the upper left corner of the grid. The "Data" variable is set to A6:B12 where the original data set is and then the "mains" variable points to J6:L6 where I copied the header row and "Falvours" points to I7:I14 where I copied the left column. "point" those 3 variable to the correct corresponding parts and the formula will do the rest.
You could also use UNIQUE and such to create the header and left column dynamically but was sure you needed/wanted that part.- JOGINDER SINGHFeb 05, 2023Copper ContributorDear, Thanks a lot for making efforts for me, but I am still unable to calculate that. Because I am beginner in MS Excel and still not able to use complex formulas. So please calculate that at your end and please send me calculated sheet. I will try to learn from the formulas you will fix in my sheet. Please
- peiyezhuFeb 05, 2023Bronze Contributor