Excel worksheet change or display list of data based on given cell text

[harinsh]

Hello Team,

I am planning to build one dashboard using work sheet change but not sure how to work with this option. My objective is to display list of fields based one cell value.

For example in A2 cell if you select "Fruit" then below automatically list of fruits should reflect and it should either refer some range or defined in the code. If I select vegetables then it should reflect only vegetables.

Which option to use to get this kind of requirement please suggest me.

Thank you,

 

[My Aswer Is This]

So your saying in sheet2 column A you have more data in this this column then just Fruits.
The script does not copy the entire column.
It copies the data from row 2 to the last row with data.
So if column A has 40 rows of data it copies those 40 rows only.
And even if it did copy the whole column if the rest of the rows are empty what would be the problem?

The problem with defining the exact range is if you have 20 pieces of fruit today and 25 pieces of fruit tomorrow you will have to modify the range.

 

[My Aswer Is This]

It you want just a defined range copied when you enter fruit in A1 then you need to tell me.
What is the exact range you want copied when you enter Fruit in A1
And what is the exact range you want copied when you enter Vegetables in Range A1?

 

[My Aswer Is This]

Hi ..its working perfect...I just want to know one more last thing ...if you could help ..so that I can customize the data for effect use of this solution....if same data in sheet2 instead of column ...if its in rows...means ....Sheet 2 data A column till 10 rows pertains to Fruits and from 12th row onwards vegetables ..how can I define the fixed range instead of last row.....

Thank you ...this might be last question for this tread ...your really superb....

After reading this question again it seems as if your saying Fruits and vegetables are all in the same column in Sheet2


If that's the case you must Name the range.

Select the range including column A for name and Column B for price

So for example select Range A3 to B20

Now look in the Name Range Box just above Range A1
And enter Fruit

Now that range is named Fruit
To be sure of yourself do this

On The ribbon choose Find and select and then choose Goto
In the Goto Box enter Fruit and select Ok and it should take you to and highlight the range.

Or use the short cut key

Control G and enter Fruit and click OK.

And I will have to write a new script. Is this what you want?

Now do you plan to constantly be adding more fruits?

If so then we will have to do even more work.

You will not want to keep modifying the named range every time you add more fruit.

 

[harinsh]

Good morning.....

Yes, I created the Name range as what you suggested then how should incorporate this name range within the earlier code ...

 

[harinsh]

I got the solution ...defined fixed range in the code...thank you for all your help

 

[My Aswer Is This]

So you have all you need now is that what your saying?
So what happened you received help from some other place?

 

[harinsh]

So you have all you need now is that what your saying?
So what happened you received help from some other place?

Looks like got solution but still I am struggling to get the same.....

Here is my explanation ......

1) Currently working some dashboard to get the result as I mentioned earlier ...like I have data Fruits and vegetables and other category too ...but I have sheet 2 with data structure like row by row ...for example in A2:B20 one set and B25:B45 another set ....all corresponding values are based on links from different sheet (reference formulas)

2) when I fixed the range its working but when its copy the formula not copying ..getting reference error in your code its just copy and does not perform paste special ...

3) I can't keep the data in column wise because corresponding columns I have more than 20 columns ...

4) I though will get help from you so, that I can customize the requirement accordingly because its all am finding solution whenever working on it and no fixed solution in my mind that's the reason I am asking multiple times ....

Now you tell me how can I go about this....if you can help code with copy and paste special it would help otherwise just copy and paste does not work.....

 

[My Aswer Is This]

When my script copies it does copy any formulas. We do not need Pastespecial.

When you are copying formulas from one place to another the formulas can become wrong because you have moved the data around.

If you do not want the formulas copied then you use Copy and then Pastespecial
This then paste only the values and not the formula.

And you never mentioned formulas in any of your previous posting.

Actually you said several times all is working great. But can you now do this.

It's hard to help when you only provide bits and pieces of what you need.

We all like helping here at Mr. Excel and it's good that your asking for little bits of what you want one piece at a time hoping you can figure out how to do the rest. But it can take a long time.

So maybe if you want you could explain what your ultimate goal is here.

I have just been doing exactly what you asked for and up to this point you have been saying all works great.

But I never know what the full ultimate goal is.

And my previously posted code copies the data where you want but then when you try to do it a second time the script clears out all the previous pasting because you said in your original post to paste into A2 and below. You did not say paste into next empty cell in column A of sheet 1.

 

[harinsh]

Yes, I understand that I could have explained my ultimate goal.....I will keep that in mind next time to post the end result instead of bits and pieces and as I said am finding out the possible way to get the output and the dashboard what I am working has lots other things as well so, thought of getting this through change request....

I agree your formula clear the format before paste the values/text.....in order to get the cell exact reference or paste special what should I do ...because...my intention is to get the quick view by products with all require columns...

its my bad again not given you the clear view....but I hope now you understand what exactly I am looking for ....you suggest me to get those reference formulas to bring to sheet1 including clearing the format before pasting and all should remain same....only need that data into sheet1 based on A1 cell selection....

 

[Shoaib79]

hello respected members,

im new to the team and don't know how to post a new thread. just have one request.

just wanna know the major differences between excel 2003, 2007,2010, 2013 and 2016

I WOULD APPRECIATE RESPONSE IN TABULAR/COLMNAR FORMAT PLEASE.

KIND REGARDS