So I have a sheet - say for example, like this:
Is it possible to have a table of formulas, which will show me all the values in the second column of each country (e.g. Fiji)?
Please Login or Register to view this content.
The end result will be like this - I'm hoping to be able to do it so I don't need to manually filter and copy rows each month.
Please Login or Register to view this content.
In my head I'm thinking it might be possible to do this with an initial lookup, and then subsequent rows with an index that starts at Motor Vechicle Toyota Hilux for team leader in North to continue with a second lookup. Clunky, but is there a better way for this?
See this example:
http://www.excelforum.com/excel-form...ell-value.html
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks Tony, I don't understand with your example, the use of column E- for me I want to return the text in column B if column A = "Fiji".Originally Posted by Tony Valko
Try this...
Data Range
This array formula** entered in E2 and copied down until you get blanks:
=IFERROR(INDEX(B:B,SMALL(IF(A$2:A$20=D$2,ROW(A$2:A$20)),ROWS(E$2:E2))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
I'm not sure if we're on the same page... I want to extract the rows which are Fiji (or Am Samoa, or PNG, doesn't really matter) into a table, the Fiji label will be a lookup (say D2) so I want the formula, which will be in cells E1-E20, to populate with whatever is in country D2.
Hmmm...
That's what the formula is doing except that it's not repeating Fiji (or whatever) for each row of data.
We already know the data is related to Fiji so why repeat it for each row of data?
Can't get it working
FWIW - columns D and E in your example are on another worksheet.
For me, D1 is a fixed cell for the worksheet, and column E is what I am wanting the array/formula to generate for me. There's a sheet for each location (Am Samoa, Cooks, PNG etc).
Can you post a SMALL file that shows how/where your data is and what result you expect?
A SMALL file is about 20 rows worth of data. We just need an accurate representative sample of what you have and what you want. Files with rows/columns full of irrelevant data just make understanding the problem more difficult.
Was just prepping the file
So in the "Am Samoa" sheet, I want the formula to populate in cells C31:C38, from "A - Data" columns A and C.
Asiapac.xlsx
Last edited by tangcla; 11-11-2013 at 09:56 PM.
Sorry, but I can't download files >50kb.
File above updated.
Had to remove all formatting to get it to be under 50KB
Try this...
On the Am Samoa sheet...
Enter this array formula** in C31:
=IFERROR(INDEX('A - Data'!C:C,SMALL(IF('A - Data'!A$3:A$26=C$1,ROW('A - Data'!A$3:A$26)),ROWS(C$31:C31))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down to C38.
Thanks! Perfect, + rep.
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks