Forum Discussion
best way of returning data from a table
Let's say Job Type is column A on a sheet named Materials List, and headers in A1:G1.
On the other sheet, enter the text "Job Type" in A1, and use B1 to enter the job type you want to display.
Copy A1:G1 from Materials List to A3:G3 on the other sheet.
Enter the following formula in A4:
=FILTER('Materials List'!A2:G1000, 'Materials List'!A2:A1000=B1, "")
Thanks Hans, I'll try that now.
Have come across another issue - an item might be applicable to more than one job type. Would it be possible to have more than one Job Type column and use IF function to search for the 'Job Type' in those columns?
For example: (5 columns for job type)
IF A1 contains JOB then return data
IF A1 is BLANK then look at column B1
IF B1 contains JOB then return data
IF B1 is BLANK then look at column C1
Thank you so much for your help.
You may try to the following formula-
=FILTER(F2:K9,BYROW(--(B2:E9=N1),SUM),"Not Found") =FILTER('Materials List'!F2:K9,BYROW(--('Materials List'!B2:E9=B1),SUM),"Not Found")Could you give us an idea of what the two sheets look like, by attaching screenshots, or a sanitized copy of the workbook without sensitive data?
Thank you Hans, I'll try that shortly
Just come across another issue in that an item might relate to more than one job type. If you were to have more than one Job Type column would you use an IF function?
For example; IF A1 contains JOB then return data - IF A1 is blank look to B1... and so on
