/docs/community?hl=en
This content is likely not relevant anymore. Try searching or browse recent questions.
how to filter data with hyperlink
0
HI,

in sheet p.o. no. 56 i want to make B1:AL1 clickable as hyperlink. i want that as we do any click on any particular month then it should auto filter the data in sheet purchase by column i.

for example- if i click on nov-2021 (AB1) then should automatically apply filter in sheet purchase.

or any other better solution.
Details
Pinned
Locked
Latest Update Latest Updates (0)
Relevant Answer Relevant Answers (0)
All Replies (11)
Relevant Answer
i need one more formula in sheet text. column A contains both numbers and text. but some numbers are entered mistakenly as text. i have highlighted them with color. so please provide an appropriate formula which converts highlighted text numbers in real numbers and which are already as text those remains same as text like 4191 A, 4191/4192/4193 etc..
marked this as an answer
Relevant Answer
Hi sanjeev kumar,
 
I have placed a possible solution for your text/number issue in the sheet called Help.  It uses the following:
=ARRAYFORMULA(IFERROR(VALUE(IMPORTRANGE("https://docs.google.com/redacted","PFM!DU10:DU")),IMPORTRANGE("https://docs.google.com/redacted","PFM!DU10:DU")))
 
This will import each piece of data, attempt to change the data into a number and if that causes an error, will import the data as text.
 
 
For your question about making row 1 clickable, it would probably be best to use a separate sheet/tab that allows you to use a drop-down to select what you want to view.  I have put together an example of this in the sheet/tab called Purchase Results.
 
I hope this helps!
 
Adam
Diamond PE Docs volunteer
Silver PE Classroom volunteer
Cert. Educator 1 & 2
Cert. Trainer
(not a Google employee)
 
When you've received a response that answers your question, please observe these forum courtesies:
 • If you had a Sheets problem, leave your demo sheet shared as View Only as part of this solution's archive,
 • Click Recommend on the post that best addressed your question, and
 • Post again soon!
marked this as an answer
Relevant Answer
first of all thanks for replying. and extremely sorry for late reply. and every time you have solved my problem very perfectly. 

but this time i forgot to explain that i don't want it to be applied on importrange. i was thinking somekind of formula which can reference drieclty to the column. because i have to used it in the same sheet. so there i will not use importrange. currently i was trying with this formula:- =UNIQUE(FLATTEN('purchase fms'!AR8:AR,yarn!B2:B,PFM!V10:V,'YARN DYEING FMS'!P8:P,'Copy of JOB WORK RECEIVED REGISTER'!J4:J))

so i want to adjust this formula or soome other if not possible with this.
marked this as an answer
Relevant Answer
I'm sorry.  I don't fully understand the problem.  Based on what you are currently describing and what I see in the sample file, I can't give you a definite answer.  I can say that we should be able to achieve what you need but I'm not able to do that with the information that I currently have available to me.
 
Adam
marked this as an answer
Relevant Answer
ok. i can understand. i am posting my original file link here and in sheet "PFM" in column DU, i want to get modify that formula so that it can extract unique values wheter it is text or numer.

marked this as an answer
Relevant Answer
The formula that is in cell DU1 [=UNIQUE(FLATTEN('purchase fms'!AR8:AR,yarn!B2:B,PFM!V10:V,'YARN DYEING FMS'!P8:P,'Copy of JOB WORK RECEIVED REGISTER'!J4:J))] will give you a one column list of unique items, both text and numbers, of all the cells listed inside the FLATTEN() function.  What is incorrect with the results that you are receiving?
marked this as an answer
Relevant Answer
that's what i am asking. i am saying that if some values are entered mistakenly. if any number is  entered as both text and numbers. then it should convert that appropriately. wheter it uses another column but finally want to have a list with unique values wheter it is number or text.
marked this as an answer
Relevant Answer
I understand that you want to have incorrect values changed to the correct type of data, but there needs to be a way for the spreadsheet to determine the type of data that is supposed to be used.  In column DU of the PFM sheet, all of the types of data are correct.  Basically, anything with a non-numeric character in the data is being considered TEXT, which is correct.  Are you experiencing a problem with this or are you just worried about it happening?
 
Adam
marked this as an answer
Relevant Answer
First of all thanks for reply. Some number values is being considered as text in the list. Because while typing numbers, it might have pressed space or etc at that time. If we apply is number formula beside of that column. It will show false while it is number. So i want to correct them. In the list instead of searching 5 files columns and this can happen in future also. So i want to solve this by some kind of formula which can correct those wrongly entered numbers and then I can have unique values.
marked this as an answer
Relevant Answer
I added a different formula to column DV that will TRIM and try to convert text to numbers.  This works except for the values that are coming from the 'Copy of JOB WORK RECEIVED...' sheet which are all P.O. numbers and probably shouldn't be considered numbers. 
 
If you are needing further help with this, I need to know what you are trying to accomplish with this formula.  Your original question stated, "i want to get modify that formula so that it can extract unique values wheter it is text or numer.", which is what this formula does.  At this point this feels like we are dealing with an XY problem and without more information, I'm afraid that I won't be able to help you. 
 
Adam
marked this as an answer
Relevant Answer
still it is showing some numbers as text. i have not checked that from which sheet they belongs because this will be a very lengthy process to check each number in all the sheets.

my purpose of getting unique value is to see total expense. in each sheet we add two columns " p.o. no. & amount". 

for example:- please see column DJ, DK in sheet pfm. so i want to have all po list and then will have total of amount for each po from each sheet. you can please see sheet 13 column A:i
marked this as an answer
This question is locked and replying has been disabled.
Discard post? You will lose what you have written so far.
Write a reply
10 characters required
Failed to attach file, click here to try again.
Discard post?
You will lose what you have written so far.
Personal information found

We found the following personal information in your message:

This information will be visible to anyone who visits or subscribes to notifications for this post. Are you sure you want to continue?

A problem occurred. Please try again.
Create Reply
Edit Reply
This will remove the reply from the Answers section.
Notifications are off
Your notifications are currently off and you won't receive subscription updates. To turn them on, go to Notifications preferences on your Profile page.
Report abuse
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Reported post for abuse
Unable to send report.
Report post
What type of post are you reporting?
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Reported post for abuse
Unable to send report.
This reply is no longer available.
/docs/threads
//accounts.google.com/ServiceLogin
You'll receive email notifications for new posts at
Unable to delete question.
Unable to update vote.
Unable to update subscription.
You have been unsubscribed
Deleted
Unable to delete reply.
Removed from Answers
Removed from Updates
Marked as Recommended Answer
Marked as Update
Removed recommendation
Undo
Unable to update reply.
Unable to update vote.
Thank you. Your response was recorded.
Unable to undo vote.
Thank you. This reply will now display in the answers section.
Link copied
Locked
Unlocked
Unable to lock
Unable to unlock
Pinned
Unpinned
Unable to pin
Unable to unpin
Marked
Unmarked
Unable to mark
Reported as off topic
Known Issue
Fixed
Marked Fixed
Unmarked Fixed
Unable to mark fixed
Unable to unmark fixed
/profile/0
false
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
35
false