• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to display cell comments based on 2 conditions

V

vishwas

New Member
Hi ,

I have set up a file which has 3 sheets; output, assumptions, & comments. objective of the file is to compare the selected month profit & loss for each contract against the prior month. Comments against the variance need to stored separately so that it could be displayed to the audience who will review the report. I need help to display the comments for each selection. Would appreciate a way to record the comments first and than display it upon selection. File attached


Please review the requirement in conjunction with the attached file link

http://www.2shared.com/file/u35OtJR0/TEST_FILE.html
 
Vishwas


Firstly, Welcome to the Chandoo.org forums.


You can have the comments on the Output sheet extracted from a table of comments from the 'comments for each contract' sheet based on some criteria which will define the row and column


You can then use a simple Index() function to extract the comments as required.


If the rows are the same on the two sheets you can use a simple relationship between the two,

What defines what is the various Columns of each set of comments?
 
Hi Hui


Thanks for your reply. All I need is a macro which will display a range of contents ( I will store information on variance for a particluar month in differenent sheets and the contract comments in different columns within each sheet)


The macro should based on a selection criteria( in my case it is month & contract ) identify the correct sheet and column and than display all the range contents. Rows on both the sheets will be identical
 
Vishwas


Your job doesn't require a macro, just a formula

I have added a formula to your file in Output!H10:H16 in Yellow

Code: 
=INDEX('comments for each contract'!$B$8:$M$40,ROWS($H$10:H10),MATCH(Output!$D$8,'comments for each contract'!B2:M2))


https://www.dropbox.com/s/s3fwp3xeprwsz1n/TEST%20FILE_Hui.xlsx


I have also fixed the dates on the Assumptions page

I have put dates across the top of the Comments
 
Hi Hui,

Thanks a lot for extending help, appreciate it. I have added an additional sheet which provides values for each of the elements in the output sheet. sheet 1 is a p&L summary for each contract for each month. I have tried to use the formula provided by you to get the result. However, failed to get the desired result. Refer to cell C10 in output sheet. Can you again help me on this please? I will than copy it across to D10 . YTD column is the sum total of all the months for the contract i.e for the period Jan -May


file link attached


http://www.2shared.com/file/wEKxq52t/TEST_FILE_Hui.html


Thank you again
 
Hi, vishwas!


I was looking at your Output sheet C10 cell formula:

=INDEX(Sheet1!$C$6:$P$21,ROWS($C10:C10),MATCH(C_Contract&$C$8&$B10,Sheet1!$C$5:$P$5&Sheet1!$C$4:$P$4&Sheet1!$B$6:$B$21,0))

The problem is with the MATCH function:

MATCH(C_Contract&$C$8&$B10,Sheet1!$C$5:$P$5&Sheet1!$C$4:$P$4&Sheet1!$B$6:$B$21,0)


You're looking for an exact matching value (last parameter 0), the Sheet1 sheet row 5 has dates with day 12 not 1, and your search argument is with day 1 (Output sheet C8 cell).


So I did three tests:

a) changed exact match (0) for aproximated match (1 or omitted, and -1), but then you must have the array ordered respectively in ascendent and descendent mode, which no occurs

b) same as a but shortening range from $B$6:$B$21 to $B$6:$B$6, getting a sorted array, but it didn't worked neither

c) changed the whole dates to 01/mm/yyyy keeping the exact match, and it didn't worked neither


I'm running out of time to evaluate in detail each part of each formula, but you can do it from the edit formula bar, with F2, and then selecting the expression and pressing F9 to check the intermediate value. Wish you succeed.


Regards!
 
Hi Hui.. is it possible to help me on this?

thanks SirJB7 for your efforts


Is there an alternate approach for my request?


thanks
 
Hi All Excel Ninja's .. Can't crack the error on my formula .. I need to submit the report today and was wondering if I could get help. Thank you
 
Vishwas


I have never liked trying to do joined Index/Match formulas and in this case would prefer to use a Sumproduct

Refer: https://www.dropbox.com/s/9ekkb0flk1fto3n/TEST%20FILE_Hui2.xlsx
 
Hi Hui


Thanks again. Though it solves majority of the problem, I still have errors in output sheet if B10 & B15 are interchanged. The output sheet elements in column B will change and it should always search for the exact match in column B of sheet1.

In short all 3 conditions should match only than the relevent contract element amount should get displayed . If the conditions donot match it should be blank ("-")
 
Hi, vishwas!


Looking at your yellow shaded H column range in Output sheet, I think you have at least these problems:

a) last parameter of MATCH function should be row fixed: B$2:M$2 instead of B2:M2, with this you avoid the N/A error

b) either row offset calculated by function ROWS($H$10:H10) or your 'comment for each contract sheet' are wrong

c) this last one has blanks lines between entries and different number of blank lines indeed: a table or least shouldn't have embedded blank lines between entries, use row height if you need formatting

d) even with those corrections, your entries for April month column are empty after first item...


In my opinion you need to be a bit more rigorous about data design and storage, and with the files you upload to describe your issues.


Regards!


PS: Regarding your last post, it would be helpful if you read again the third green sticky post at this forums main page, specially the 13h paragraph. If didn't read none of the three which better opportunity than now?
 
Hi SirJB7


Thanks I have taken note of your advice. However was wondering if I could get help from Hui as we were very near to get a solution to my problem.


I could as well post it all over again explaining my request with an example


Regards
 
Hi, vishwas!


If I were you I wouldn't post it all over again, instead I'd reformulate what necessary in this same topic, because the original question is the same, isn'it?.


If you didn't yet, perhaps you'd want to read the three green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on). Take special care on 15th and 16th (additionally to previous and guess read 13th) paragraphs of 3rd green sticky post.


Regards!
 
Vishwas


Firstly please note that all help is rendered here on a voluntary basis

Being pushy or rejecting help from others isn't a great idea


In regards the question:

Output!C10:
Code: 
=INDEX(Sheet1!$C$6:$P$21,MATCH(Output!$B10,Sheet1!$B$6:$B$21,0),SUMPRODUCT((Sheet1!$C$4:$P$4=C$8)*(Sheet1!$C$5:$P$5=C_Contract)*(COLUMN(Sheet1!$C$1:$P$1)-2)))


This formula can be copied anywhere within Column C & D and will pickup data from Sheet1 based on the Dates and Row Headings in Column B
 
Hi Hui,


Thanks a lot, it works exactly as I wanted.

My intention was never to reject any help. All I was trying to do is to save time of other experts in the forum.Apologies for any inconvenience caused as a result of my post.


Hope I will get help in the future.


Thanks once again

Regards
 
Hi Hui


In the formula provided by you


Output!C10: =INDEX(Sheet1!$C$6:$P$21,MATCH(Output!$B10,Sheet1!$B$6:$B$21,0),SUMPRODUCT((Sheet1!$C$4:$P$4=C$8)*(Sheet1!$C$5:$P$5=C_Contract)*(COLUMN(Sheet1!$C$1:$P$1)-2)))


Can you please explain why "-2" is included in column function.


Thanks
 
You must log in or register to reply here.
Back
Top