Looking for Support to display Corresponding values based on a specific cell value

[suneeshpillai]

Hi,

I am in the development of a worksheet, where I have to get the corresponding values in the rows based on the specific cell value. Attached my worksheet with my tryyyy. I need support from experienced hands to resolve the issue.

Regards

Suneesh

[Glenn Kennedy]

This is broken from before the start. Explain EXACTLY what you want in P3 and P4.

[suneeshpillai]

Dear All,

Sorry, I attached a wrong file. Attached the updated file.

Regards,

Suneesh

[suneeshpillai]

Hi Glen

Sorry, I have attached a wrong file. Now I have attached the correct one.

As you have asked in your reply :

Forget about P3 field
Based on P4, the amount spent on Specific months will be changed.
I wanted to display the values in Date & Remarks rows too.

Hope you understood the requirement.

Regards,

Suneesh

[Glenn Kennedy]

If there are multiple matching DIFFERENT dates, which one do you want returned?

is the use of a UDF (.xlsm format) acceptable for the solution to some of the cells?

[suneeshpillai]

If there are multiple matching DIFFERENT dates, which one do you want returned?

is the use of a UDF (.xlsm format) acceptable for the solution to some of the cells?

Hi,

Either I should get both dates or at least the latest one.

Rgds

[Glenn Kennedy]

Please answer my second question!!!!!

[suneeshpillai]

Please answer my second question!!!!!

I am not looking macro-enabled one.

[Glenn Kennedy]

1. You can choose between latest date (as now) or "multiple" dates. Which do you want? If you want multiple dates, what is the MAXIMUM number per month. Before answering this, read point 2.

=IFERROR(CHOOSE(MATCH($P$3,Head,0),LOOKUP(2,1/(($I$3:$I$1000=P$6)*($K$3:$K$1000=$P$4)),$J$3:$J$1000),LOOKUP(2,1/(($B$3:$B$1000=P$6)*($D$3:$D$1000=$P$4)),$C$3:$C$1000)),"")

2. How many different "remarks" do you want returned (maximum). To avoid duplicates (Car, Car should be the result for vehicle/petrol in April) there is a fairly long formula in place. the more remarks you want, the longer it gets. Much easier with a macro enabled file!!

3. To keep the formula human-readable I created 3 named ranges, (Rem_1, 2 &3). I see you are familiar with these, so no need to explain.

=IF($P$3="Vehicle",Rem_1&IF(Rem_2=CHAR(10)&Rem_1,"",Rem_2)&IF(OR(Rem_3=CHAR(10)&Rem_1,Rem_3=Rem_2),"",Rem_3),"")

4. Ensure you have text wrapping enabled in the results section

5. I created a Named Range for Employee/Vehicle to enable me to create an INDEX number for CHOOSE to use.

6. Formulae all run to row 1000. Is that enough/too much?

[suneeshpillai]

Hi,

Thanks for your response. Please find below my response to your questions.

1. If possible multiple dates to be shown. Maximum 2-3 entry will be in a month.
2. Remarks will be the same as dates (2-3 entry). Even duplicate, no issue. (eg. Car, Car)
6. Maximum Entry will be in 1500 rows.

The rest all ok.

Rgds

Suneesh

[Glenn Kennedy]

OK. I will finish it shortly.

[Glenn Kennedy]

Try it now...

Up to 3 dates and 3 remarks.
Duplicates will show (date and remark match with each other - i.e. same row)
6 more Named Ranges
Rows maximum 1500.

[Glenn Kennedy]

I have just noticed your profile. It says Excel 2007. Is that still the case? If so the formulae will need to be modified. I am unsure, as you did not state after your first look at my solution that the remarks were not showing. It's a quick fix, if needed!!

[suneeshpillai]

I have just noticed your profile. It says Excel 2007. Is that still the case? If so the formulae will need to be modified. I am unsure, as you did not state after your first look at my solution that the remarks were not showing. It's a quick fix, if needed!!

Hi,

Yes, I am still using Excel 2007.

Regards,

Suneesh

[Glenn Kennedy]

Then you must not have looked at my sheet posted at #9!!!!!!!!! What a waste of time...

i will make it Excel 2007-compatible shortly.

[suneeshpillai]

Then you must not have looked at my sheet posted at #9!!!!!!!!! What a waste of time...

i will make it Excel 2007-compatible shortly.

No, I have seen the issue, but I thought I have done a wrong entry something.

[Glenn Kennedy]

This time TEST it and REPORT back. Dio not copy it into your real sheet. Test it here.

You are using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[suneeshpillai]

Hi,

It's working perfectly. Appreciate your effort and thanks for the support.

Rgds

Suneesh

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

[suneeshpillai]

Hi Glen,

I have modified the datasheet in my file and I have attached for your review.

1. I have added one more column in the main sheet and merged both tables in one table. After merging I am not getting the results as I want.
2. I have added some more items in the "HEAD" . At Cell Ref. $L$3 is having a drop-down menu.

Can you please look into it and advise me where I got mistakes.

Rgds

Sune

[suneeshpillai]

Attached file

[Glenn Kennedy]

Hi. please don't quote entire posts, especially when it is CLEAR who you're talking to. It's just clutter.

Here is your sheet back again for testing.

[suneeshpillai]

Hi. please don't quote entire posts, especially when it is CLEAR who you're talking to. It's just clutter.

Here is your sheet back again for testing.

Hi,

It's working perfectly.

thanks bro.

Rgds

Suneesh

[Glenn Kennedy]

You're welome. Did you read what I said about unnecessay quoting of posts???? I have deleted your unnecessay qutotations TWICE, asked you SPECIFICALLY to stop and you do it again!!!

[suneeshpillai]

Sorry, bro. By accidentally it's happened.

Rgds

Sun

[Glenn Kennedy]

Use reply, from the foot of a post, not reply with quote.