Extracting multiple lists by category

Hi. I have a master sheet where each row is allocated one of four categories. I would like to extract this data into another sheet, into different columns dependent on the category (i.e. four columns for name, one per category, four columns for date, one per category).

All the functions I have tried (vlookup, match/index etc.) only bring up the first result for each one. I know I can create reports to show the data, but this wouldn't work as I need the data to build a dashboard.

Can anyone help?

Find more posts tagged with

Formulas

Dashboards

Cell linking

Accepted answers

All comments

Andrée Starå

Hi @sharonmr

I hope you're well and safe!

Can you share some more information (or/and a screenshot) on how the data is structured?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SharonMR

Thanks for your reply.

Its a fairly simple document, where we store information about all the reports that have to be done (weekly, monthly, quarterly, yearly). We usually have it in calendar view so we can see what is due and ensure the responsible person is preparing to submit.

What I am trying to do is to have a separate document with headings that divide the reports up, such as by type and by frequency, so I can create dashboard metrics

I can use some if formula to do this quite easily within the master document, but would rather keep it separate. The only way I can see of doing is to just pull all the information through as a copy. This has problems of its own - linking cells directly isn't future proof, as reports are being added all the time and I would have to keep going in to update links.

What I really need is something that will, for example, add in the details of a new NHSE report as soon as it is added to the master.

I considered a copy row rule, so that all new reports would be added, but that wouldn't work if a report was removed from the master document.

I did find a formula once, that was one formula on row one and then a different formula on subsequent rows. Unfortunately at that time I didn't use it and cant now find it!

One that could be made a column formula, so I didn't have to copy down the formula and could be sure all reports would be included no matter how many were added, would be ideal, but I don't think that's possible.

Any help would be much appreciated.

Thank you

Sharon

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.