• 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.

Hyperlink to copy data from one tab to another

M

msharma864512

Member
Hi,

I want to create a funcationality/macro, which will enable me to put the Excel IDs from this tab to tab named "Data"

If I click on any company, then it will automatically copy the excel company ID of the respective company and paste the same in tab named "data" in cell A2

The moment the ID is copied to the data tab, a pop up will come to pull the Ids of the company we have clicked previously

Ids of competitors will be pulled on the basis of sub-sector

For e.g. if we have selected ABB Ltd. Whose sub-sector is Electrical Equipement, then it will pop up the names of all the companies within Electrical Eqipment Segment, and then we can select the companies whom we want to include in tab named "Data"

Regards,
 

Attachments

  • Book1.xlsx
    19.8 KB · Views: 5
Belleke said:
Something like this?
Click to expand...
Hi Belleke,

It looks great, and thank you very much for ur help.

However, it would be great if it has the functionality to filter the companies by sub-sector

For. e.g, if i click on show form button, and then if i select any company then it should automatically filter all the companies which are of the same sub-sector of the company selected.

For e.g. if I select the company name "5N Plus" then all the companies from chemicals sub-sector should show in the form, then from that list i can select any company i want.

Additionally, although the one click button looks fine, but will it be possible to open the form by clicking on the companies within cell range "A2:A304".

For e.g. if I click on A6, (which happens to be Electrical Equipment company) then the form should open with only Electrical Companies in the list.

Regards,
Manish
 
This one does what you asked for.
Just click somewhere in the table.
see attached.
 

Attachments

  • Filter IDs(v2).xlsb
    37.3 KB · Views: 3
Belleke,

This is great, but only problem here is, its not letting me scroll through the table.
In particular I dont want to apply hyperlink to entire table, I was just looking to add hyperlink to company name only, and that too be with CTRL button.

For e.g if i have to click on any company name, it should be possible only with CTRL button along with selecting the required company name.

Also, is it possible to have this macro without a table, because the data set i m working on doesnt contain any table and it will not be possible for me to include the data as table.

Also,
it would be helpful if you could also guide me how can i apply the same macro in different spreadsheet, as the data set in that file be a bit different.
 
Last edited by a moderator:
Th
Belleke said:
Next version
Click to expand...
This is really great Belleke,

I was just looking for one small edits,

Right now, if click on clear data button, it delete all the data below row 2,

so is it possible to just delete the data from the column where data has been copied, in this case, all the data to be deleted below cell A2, and it should not delete any other data
 
T
Belleke said:
Version 4
Click to expand...
Thanls Bellek,

There is just one small update I am looking for.

In the dashboard tab, if i am trying to add blank rows and columns, then the filters are not working on sub-sector.

For e.g. if I start my data from Cell C17 (Company name heading)
and then if i try clicking on any company, then ideally the form should give the company name of the relevant sub-sector only, but its not giving the detals, and it is showing the whole company list.

It would be great if you could add the functionality to do the same
 
msharma864512 said:
T

Thanls Bellek,

There is just one small update I am looking for.

In the dashboard tab, if i am trying to add blank rows and columns, then the filters are not working on sub-sector.

For e.g. if I start my data from Cell C17 (Company name heading)
and then if i try clicking on any company, then ideally the form should give the company name of the relevant sub-sector only, but its not giving the detals, and it is showing the whole company list.

It would be great if you could add the functionality to do the same
Click to expand...
Thanks Belleke,

I was able to do it myself
 
Belleke,

I am facing just one issue,

My companies list is pulled from some other sheet and there are multiple conditions to pull the list, For e.g. if I click on A&D buttons, then column C only reflects the A&D companies. Hence in this case, if I try typing Ctrt +Q it showing an error,
 
If I see the pictures,I guess there is an issue in the UserForm_Initialize code.
An did you assign Crtl +q to your macro?
 
B
Belleke said:
If I see the pictures,I guess there is an issue in the UserForm_Initialize code.
An did you assign Crtl +q to your macro?
Click to expand...
Belleke, yes i did assign CTRL + Q to macro, and its working fine in normal situation, but as and when i click on any my filter buttons, macro is not working
 
Did you went trough the UserForm_Initialize code?
Can you post a relevant example without sensitive input?
I can't help you with only pictures.
 
Belleke said:
Did you went trough the UserForm_Initialize code?
Can you post a relevant example without sensitive input?
I can't help you with only pictures.
Click to expand...
Hi,

I have attached the spreadsheet, when u use the dropdown in the yellow highlighted cell in dashboard tab, Form will open if u select all, but it wont work if u select, core, focus, or G360
 

Attachments

  • Filter IDs(v4).xlsb
    71.9 KB · Views: 1
Belleke said:
Like that?
Click to expand...

Not Exactly, as there are multiple dropdowns in my main worksheet, and defining the name for each entities would be difficult, and it would also be difficult to write codes for each.

There are close to 40 entries with 5 dropdowns.

So, I need a different option
 
msharma864512 said:
Not Exactly, as there are multiple dropdowns in my main worksheet, and defining the name for each entities would be difficult, and it would also be difficult to write codes for each.

There are close to 40 entries with 5 dropdowns.

So, I need a different option
Click to expand...
Hi Belleke,

Did u get a chance to look at this?
 
You must log in or register to reply here.
Back
Top