Indexing the entire column from another sheet

We have a situation where we have a few archives of a database. Unfortunately, I need to look across them all. I currently have a CountIfs formula that looks at three separate columns in all the sheets. Each sheet as it own CountIfs that is concatenated with a +. Needless to say it can be a bit messy when I have to add and remove reference sheets. I would like to make it a bit more dynamic. My thinking is to create separate indexes of the sheets and columns and then use something to pull them all together.

My first obstacle is being able to index the sheets and columns. Trying the AI I got the following formula, =INDEX({Other Sheet Column}, 1:COUNT({Other Sheet Column})) but this generates an #UNPARSEABLE error.

Any thoughts?

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

You will need to continue with the separate COUNTIFS. The best you will be able to do is create some extra archive sheets that you can go ahead and reference and then leave them blank until they are needed.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.