Indirect with unique values

[Malhotra Rahul]

Hello everyone, i have a situation where i do expect the support. Please find here attached workbook in the provided link. The output in column B in Report tab is not giving the correct result. if the Sales person is getting repeat in the second worksheet then i do expect the search result will be from the second worksheet and if the sales person is getting repeat the third worksheet as well then the output search results will be from the last worksheet tab.

Your support would be appreciated.

Rahul

https://drive.google.com/file/d/1VUVWj2YonboXQgszT2zA8RurwfwR-idD/view?usp=sharing

 

[Anthony47]

Hello everyone, i have a situation where i do expect the support. Please find here attached workbook in the provided link. The output in column B in Report tab is not giving the correct result. if the Sales person is getting repeat in the second worksheet then i do expect the search result will be from the second worksheet and if the sales person is getting repeat the third worksheet as well then the output search results will be from the last worksheet tab.

Your support would be appreciated.

Rahul

https://drive.google.com/file/d/1VUVWj2YonboXQgszT2zA8RurwfwR-idD/view?usp=sharing

I am not sure about which are the inputs and which the outputs; but you might try, in B2

=EOMONTH(MAX(IF(COUNTIF(INDIRECT("'"&mysheets&"'!C1:C100"),C2)>0,--LEFT(mysheets,8),"")),0)

To be confirmed using Contr-Shift-Enter; then copy downward

Since my Excel is not the english version, the above formula has been translated to the best of my knowledge.
Copy of your workbook that uses that formula can be downloaded from here: https://www.dropbox.com/s/pvofoj7xp...R_Indirect with unique value_B80318.xlsm?dl=0
I set my formulas in column E, so you can compare them against your ones in column B

Bye

 

[Malhotra Rahul]

Dear Anthony47, In the Report tab Column B i do expecting the results if the sales person name appearing more than one tab then the results will be separately for each tab. As i have also mentioned the expected results in the provided workbook.

The provided solution providing the results only for a tab. Please do look into the same.

Rahul

 

[Anthony47]

Pardon me, but I am more confused...

Working on the sheet that you published, on sheet Report, on line 7 (Sales Person B) and 8-9 (Sales Person C) which information would you like to receive?
-value for the information
-in which cell(s)
-why that value

Bye

 

[Malhotra Rahul]

the reason is, if the sales person name is getting repeat then the search result will be from next worksheet. The logic behind to get this, is based on the monthwise sales persons data, we want to gather the data in the report tab.

 

[Anthony47]

Please try responding the questions I posed in message #5 ; your answers (probably) will help me to understand the question.

 

[Malhotra Rahul]

Dear Anthony47, if you look into the tab for Feb and Mar then you will find there are also line items for Sales person B and C but the formula in the report tab is giving output only from the Jan tab.

 

[Anthony47]

I don't understand why you dont answer the questions:

Working on the sheet that you published, on sheet Report, on line 7 (Sales Person B) and 8-9 (Sales Person C) which information would you like to receive?
-value for the information
-in which cell(s)
-why that value

And, believe me, I am only trying to understand your problem.