New Excel Formula needed using IF to populate cells if "*" is added to a column

Hi All,

I was wondering if anyone can help me. I have 2 tabs on a workbook INPUT and UK. I am looking to build a formula to insert data on the UK sheet if * is entered on column Q from the INPUT tab. Basically:-


COLUMNS

L (Amount) M(Rate) N(Inv) O(Descrip) P(Reason) Q(*)

3,200.09 I 0.200 Ven.Inv. automatic T AP, GRIR 1
12.28 I 0.200 Ven.Inv. automatic T AP, GRIR 1 For IT *
1,631.82 I 0.200 Ven.Inv. automatic T AP, GRIR 1 For BE *
6,426.14 I 0.200 Ven.Inv. automatic T AP, GRIR 1

The above data is on the INPUT tab and on the UK tab:

I would like the same data to populate below if * is added to a row in the INPUT tab

COLUMNS
(L) (O) (M)


Many Thanks in advance for any input

Please can you post a small sample file.

Hi, Please see file attached:

The UK tab needs populating automatically when the * is added so that you don't have to manually add it, these are only 2 , but there are sometimes 100's of lines to add..


Thanks very much..

Enter in A6

=IFERROR(INDEX(Input!$J$3:$J$100,SMALL(IF(Input!$O$3:$O$100="*",ROW($A$3:$A$100)-ROW($A$3)+1,""),ROWS($A$3:A3))),"")

Enter with Ctrl+Shift+Enter

Copy across and change range in RED to appropriate range in input

Copy down.

HI, thanks very much, you are a genius, however what if I need to change the * to @ , i've tried it and it doesnt work...

@ has a special meaning (reserved character) in Excel so can not be used.

I got it was the ctrl+shift +enter that i forgot --- THANKS SO MUCH ---- EXCELLENT FORMULA!!!!!!!!

Glad it is OK. Could you please mark thread as SOLVED ("Thread tools" at top of first post). Thank you.

Thanks again... Can you explain the formula, so that i know how to customise it, eg if I need to insert another condition (if column with Doc type is RE, KR and KA, then do the above formula, but if not still do formula but paste below.

Why do we use SMALL and IF together and why are doing the -ROW($A$3)+1,""),ROWS($A$3:A3) --- just so I can understand and use in other sheets if I have to as I think this is a brilliant formula....

Many Thanks

How this works ...

=IFERROR(INDEX(Input!$J$3:$J$100,SMALL(IF(Input!$O$3:$O$100="*",ROW($A$3:$A$100)-ROW($A$3)+1,""),ROWS($A$3:A3))),"")

It is an "array formula" and as the name suggests Excel stores an array of results.

The IF statement simply tests the criterion for selection: if it is TRUE, it stores the row number (that 's the ROW($A$A$100)-ROW($A$3)+1) and if FALSE it is blank so we end up with an array like 1,2," "," ",5," " etc. So rows 1,2 and 5 match, 3,4 do not.

Using SMALL with the ROWS($A$3:A3) as the k smallest value (so this goes 1,2 3 as we drag the formula down) we select row 1,2,5 etc from the INDEX range. note that row is relative to the start of the selected range so row=1 is J3 in this example

You can see this if you click on a cell containing the formula then click on SMALL in the formula bar then "fx".

You cannot put more than one formula in the same column.

If you need to extend your selections (as it appears to be multiple choice) , re-post.

If you simply need to extended it to include another column with same selection criterion ("*") then just change the J range to whatever.

Hope this helps.

Thanks very much,

Why are we subtracting this then adding +1 on the below


-ROW($A$3)+1

Thanks very much for your explanation, very thorough indeed.

Just a technique to ensure we always start at 1: the ROW( .....+1 is a "standard" approach using INDEX/SMALL formulae. Note that is advised to keep the ranges consistent as well as we much less likely to get errors.

I was just thinking about adding a comment about Tony and his knowledge of Excel.

His reply here is typical of what he (in particular) brings to this forum: little pearls of wisdom to help us all use Excel more effectively and efficiently.

So (again) I have just learned something new!

Thanks very much Tony and John for your excellent expertise.. I have a change in the scenario in the same formula. I have added another tab called output and so I would need it to do the exact same thing in the formula above, but also conditioning Output!, so I need the formula to look in to 2 sheets instead of just the one input! see attached.


Thanks very much..

Try this

=IF(COUNTA($A$3:$A3)<=COUNTIF(Input!$O$3:$O$100,"@"),IFERROR(INDEX(Input!$J$3:$J$100,SMALL(IF(Input!$O$3:$O$100="@",ROW($A$3:$A$100)-ROW($A$3)+1,""),ROWS($A$3:$A3))),""),IFERROR(INDEX(Output!$J$3:$J$100,SMALL(IF(Output!$O$3:$O$100="@",ROW($A$3:$A$100)-ROW($A$3)+1,""),ROWS(INDIRECT("$A$3:$A"& ROW()-COUNTIF(Input!$O$3:$O$100,"@")-1)))),""))

As usual, enter with Ctrl+Shift+Enter

Sample in attached.

Thanks John, brilliant, only last thing is what if I need the data populated under a another subtitle on the same sheet called Non WNS , so if Output tab has "@" to put the data under non-WNS and if input then past on top as normal above formula under WNS (See highlight in green (see attached).

Thanks very much once again..

Why did you not state this initially as I could simple have copied the separate formulas rather than try and combine them. The issue you will have is if the WNS list is greater than the rows allowed. The simplest answer would be to have 2 tabs. why make life complicated?

See the attached.

Thanks very much John,,, makes complete sense, don't know why I didn't think of that

Thanks so much for all your help...Brilliant formulas!

Hi John,

Another interesting crop has come up, using the same formula with ISNUMBER and SEARCH--- as what i want it to do is if the input tab in the Comment column if it has the words "Reversal or Payment", I want to use the same formula but copy it at the bottom. I',m not worries about the list being greater as it is normally only 2 or 3 lines...

Thanks for any suggestion to this