Index row formula error on Google spreadsheet

1
votes

I have a formula in Google spreadsheet from cell B2 all teh way to B100 which is

B2=iferror(INDEX(DB!$B:$AC,SMALL(IF(DB!$B:$AC=$A$1,ROW(DB!$B:$AC)),ROW(1:1)),0), "") B3=iferror(INDEX(DB!$B:$AC,SMALL(IF(DB!$B:$AC=$A$1,ROW(DB!$B:$AC)),ROW(2:2)),0), "") B4=iferror(INDEX(DB!$B:$AC,SMALL(IF(DB!$B:$AC=$A$1,ROW(DB!$B:$AC)),ROW(3:3)),0), "") B5=iferror(INDEX(DB!$B:$AC,SMALL(IF(DB!$B:$AC=$A$1,ROW(DB!$B:$AC)),ROW(4:4)),0), "") .. ... ......

So it is pulling up values from a second tab which is named as "DB".

The Index row formula looks for the status "Completed" ( which is on cell A1) and return the details for all completed from the DB.

Google spreadsheet main spreadsheet

Database in tab 2 (DB) DB

The formula works fine , however i am getting duplicates of every cell which got the status "Completed "

Attached links to the screen shots for your reference. Don't know what i am missing. 0_o Thanks

3
google-sheetsexcel-formula
what exactly are you trying to pull from DB? what is the relation between your formula and the duplicates? explain more please. I tried to create the same sheets in my own excel and simulate the problem, but I don't know what exactly you are trying to do. - Marcel
Hi, There are rows in the db with status "Completed " , " in Queue" , "resolving issues" , " in progress" and some are blank. With this formula on tab1 it will only get details for rows for "completed" from the db . And A1 tab is where we enter the satus what we like to pull from db , so for example: If I enter A1 in tab one as "RESOLVING ISSUES" It will come back with row which have teh status "RESOLVING ISSUES" - Wish
Regarding duplicates : - the formula is returning the result twice for each row, which should not happen. :-| - Wish

3 Answers

0
votes

there is no need for formula here.

This is simply done by filtering:

Supposing you have a DB sheet like this
In your main sheet, put DB!A1 in cell A1, and drag and fill horizontally and vertically, to copy exactly your DB sheet into main sheet (you can do this selectively as well, there is no need to copy every column, just cpy those you want). Then click on FILTER, you can find it in SORT AND FILTER, then you will see a dropdown menu on all of the column headers. Simply by clicking on your status header, you can selected completed and press OK, it will ONLY show rows with completed on their status column.

And here is the link to download this example sheet

0
votes

I think you can use QUERY() for what you try to achieve. Have a look at this example sheet and check sheet 2 where this formula is used to filter the data from sheet 1: 

=query(Sheet1!A:H, "select * where B = '"&A1&"' ")

(where A1 is a drop down list with the values 'COMPLETED', 'in progress', 'resolving'). See if that helps ?

0
votes

your links don't work for me.

You should use the FILTER() function, that is designed exactly for this purpose:

in cell A2 on your second sheet use: =filter('DB'!B:AC,'DB'!B:B=$A$1)

As suggested I would use Data validation with dropdown list, so that only valid statuses may be chosen.