×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)
2

thread68-1822614

Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

(OP)
A previous question on this workbook was SOLVED regarding =SUMPRODUCT() and you can find that information in this thread: https://www.tek-tips.com/viewthread.cfm?qid=182145...

PERFECT SOLUTION: =SUMPRODUCT() formula that I had no clue about (Thanks Skip Vought .. and all the others who helped). This formula was used on 2 worksheets, one that totals the purchase by the “Type” column and another by “Resource” and the formula I’m using is =SUMPRODUCT(('!_Library'!A:A=A4)*('!_Library'!G:G)) These 2 sheets are not included in the upload but trust me, it works.
=============================
Attached is a small sample of my “Library” worksheet with the data related to this inquiry. not the entire workbook.- for Privacy purposes.

NOTE: I do not know VBA or how to implement it. I do not feel it is fair for me to ask for a tutorial on how to implement such a thing, that’s why I hope this can be done via Formula/Function within Excel 365.

If not, I’m happy to learn but if its too involving, I’ll probably get lost.

============================
HYPERLINKS:

I have a column of Hyperlinks, (See Column M) have long links (from a data dump) that I’d like to simplify by using the word “Link”. I copied that column to outside of my prime data, and in its place entered the following formula:

=HYPERLINK(cellref,”Link”) and was ELATED that it worked! I then used the following a Copy > Paste Special > Values on the “Link”s (Column K) hoping to have the hyperlink show just the word Link then delete (Column M), OOPS… the links don’t work (which I understand the data is now gone)..makes sense. I just don’t understand why it won’t allow me to use Paste > Special >Values. What am I doing wrong?

This is very tedius work updating each link to reflect the word “Link” by manually editing them all through the Hyperlink or Link option within excel. My list of just over 6,500 records and ever growing. I have been doing this work manually but I still have 5,189 links yet to do I’m hoping this can be done a little easier. Currently when adding data, I fix them manually from the entry point but there are SO MANY to edit from the past.

============================
TOTALS SPENT BY CRITERIAL: Yearly & Quarterly

If you have a column of DATES, you could get these totals by YEAR or by QUARTER or by whatever other discriminatory category you might have in your data, that might help you give a clear interpretation of your data."

It was suggested there may be a way, if I use dates in my information to utilize, the “Date” I’d want to use is Column G: Date Added

These dates range from 1991 to Present. The purpose for this workbook is Inventory as well as over the last 51 years, how much I have spent on my craft (Crocheting). So this would be extremely helpful.

Too much to ask?
I appreciate any and all recommendations or assistance

Laurie

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

Here's your sample workbook back with a few modifications that will aid in analyzing your data.

1. Made your table a Structured Table via Insert > Tables > Table. This makes table references simpler to maintain. Your table now has the name Table2.

2. Inserted a row above your table for a SUM of the Cost column in column H. I used the SUBTOTAL() function with a Function Number of 109 which only sums Visible cells, if you filter your table, which is handy at times.

3. Added a sheet for Date Analysis. You did not specify what kind of questions you wanted to answer, so I posed three questions and the formulas to display the appropriate answers.

4. You're making hyperlinks too complicated. Simply paste the URL directly into a cell. For instance, I copied the URL for this Tek-Tip thread and pasted it into the last row of your table. Easy peasy.

Hope this works for you.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

Quote (ladyck3)

I do not know VBA or how to implement it.
Well, that would be a good time to learn smile

A few steps of how to start: Automate tasks with the Macro Recorder

And then, to place "Link" in your Hyperlinks in column M, run this code:

CODE

Sub ladyck3()
Dim R As Integer

For R = 3 To Sheet1.UsedRange.Rows.Count
    If Not IsEmpty(Range("M" & R).Value) Then
        Sheet1.Hyperlinks.Add Range("M" & R), Address:=Range("M" & R).Value, TextToDisplay:="Link"
    End If
Next R

End Sub

and modifying your 5,189 rows is just a couple of seconds away smile

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

The last question I posed is...
How much have I spent each year by quarter

This demonstrates multiple criteria, which is what happens when you ask more complex questions.

The formula for this question...
=SUMPRODUCT((YEAR(Table2[Date added])=$A15)*(MONTH(Table2[Date added])>=(B$14-1)*3+1)*(MONTH(Table2[Date added])<(B$14-1)*3+4)*(Table2[Cost]))

...or to display the formula for each criterion...
=SUMPRODUCT
  (
    (YEAR(Table2[Date added])=$A15)*
    (MONTH(Table2[Date added])>=(B$14-1)*3+1)*
    (MONTH(Table2[Date added])<(B$14-1)*3+4)*
    (Table2[Cost])
  )

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

(OP)
Skip, Andrzejek,

Thanks for your responses. I have a lot to digest so I'll be a bit before I formally respond. However, you help is so appreciaed and I can't wait to learn all of this :) I just never know how to go about even research, unsure of proper criteria wording in my Googler, so thanks!!!

Laurie

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

The code I gave you assumes you start processing the rows starting at row 3, but if you already 'fixed' Hyperlinks in all rows down to row 5,189 and you need to fix the rest of them starting in row 5,190 then you will need to modify my code to:

CODE

...
For R = 5190 To Sheet1.UsedRange.Rows.Count
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

(OP)
Skip,

1. I created the table as recommended (fearful that my colors would be deleted but to my joy, they were not.) and got the SUBTOTAL function worked after some tweaking/understanding.
2. My table (from the Subtotal function is titled "Table1". How does one verify the "Table Name" before entering any formulas or functions? Just curious.
3. I copied your "Data Analysis" into my master list, then made a copy called "Analysis" for me to work with and not mess up your example.
  • Then I changed the sheet name in your "A3" cell and changed the name of the worksheet to match my actual workbook title and the table from Table2, to Table1. I then get a VALUE error.
What am I doing wrong? (I know coding with one tiny thing out of place will create havoc. :(

============================
Andrzejek, et al

Thank you for the code (I don't understand, of course) I have recorded a macro once, a couple of life-times ago. I may be wrongly assuming but I believe you are stating the code provided will do this action in seconds. I went into the DEVELOPER tab, and I see in the upper left, either Visual Basic or Macros. Is there a way I am able to take the code provided and paste it somewhere in there to be able to just run that code? Or do I have to actually create a new macro?

Skip, et al

Regarding a copy/Paste of a URL is understood, in fact the export from "Ravelry" of my "Library" was the start of this venture and all of the links are provided for each item (from Ravelry), and I did paste them into the initial sheet. I am wanting to simply (I thought) was to show the word "Link" instead of seeing the entire URL. There are other resources or purchase points, that I have to delve into the internet to obtain which I am now adding manually to the word Link so its done and dusted for those immediately upon entry. Looking at the sample you can see what I'm talking about.

Right now, with the way it is, those 5,189 verbose URLs that I want to change to show the word "Link", I am right clicking on the URL, choosing "Link" in that menu. "Cutting" the URL, type the word Link at the top and then Paste the URL in the bottom field of that screen. MAN-U-ALLY... I was hoping for a formula, which I see may need VBA or a Macro to do this automatically. Using the =Hyperlink function requires the field with the full URL cannot be deleted ... Easy Peasy I have, but I'm wanting (hoping for) it CRAPPY DIFFICULY <GRIN> :)

I got 2 things accomplished :)

You mentioned about what "questions" I was asking but was not elaborate enough. Well, after changing my sheet to a "Table" has totally blown up what I was so relying on with totaling my data on separate sheets called

!_TYPE Totals (which is Column B) and
!_RESOURCE Totals (which is Column A) as well as the ones you provided that I'm still trying to understand.

SO there's that as well.
Its the VIRGO in me... (don't shoot me) :)

Laurie

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

(OP)
Andrzejek,

I just saw your second response. At this time, there are 5,189 that need to be fixed. But as I said, the list is ever growing, and I have other data to yet import a I'm working on my resouces individually. There are several sheets of data I still need to add, so the list really will be never ending as some are long, and I had not started editing them at the time I started. SO, when added to the master, will also have regular links that need to be converted.

I hope that's not confusing... Like I said, this is an every growing list (LIVE and BREATHING, Growing venture)

:)
Laurie

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

Some assumptions:
Let's say your Hyperlinks are on Sheet1 in column M
Data starts in row 2 (there is a Header row)
Some Hyperlinks already have a word 'Link' to click on

In Excel, hit Alt-F11 (that opens a VBA editor)


That will open a place where you can enter this modified code:

CODE

Sub ladyck3()
Dim R As Integer

With Sheet1
    For R = 2 To .UsedRange.Rows.Count
        If Not IsEmpty(.Range("M" & R).Value) Then
            If UCase(Trim(.Range("M" & R).Value)) <> "LINK" Then
                .Hyperlinks.Add Range("M" & R), Address:=Range("M" & R).Value, TextToDisplay:="Link"
            End If
        End If
    Next R
End With

End Sub

and it should look like this:


Place the cursor anywhere in the word 'ladyck3' and hit F5 to run it. thumbsup2

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

(OP)
YOUR SIR... ANDY....

Are a GENIUS!!!!!!!!!! it worked perfectly. I did copy and paste the "Link" items into my COLUMN K, and removed Column M. I realize that in order to work in the future, I will probably have to paste the list of URLS into Column M, for it to work but I'm JAZZED! Thank you so eternally!!!

If I could give you a 1,000 *STARS* I would :)

It was perfectly executed... VOILA!

Laurie

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

(OP)
Skip, buddy O'Mine :)

I of course give you a bazillionth star, but I still have to work on the data analysis info. Learning is a good thing, once one's brain engages and the mud clears to pristine crystal clear water. I'm still trying to purify :)

Laurie

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

(OP)
FRUSTRATION...

What am I doing Wrong???????????
This is what I was using and it WAS working prior to changing my data into a "Table"
This is on the TYPE calculations mentioned above...

=SUMPRODUCT((Library!B:B=A7)*(Library!H:H))
This was working flawlessly.
I had running totals on my TYPE column with each "type" in Column A on that data worksheet.
Now I get a #VALUE! return. I suspect because the data is now in a table format.

I have another worksheet for the RESOURCES column that's broken as well...

I've tried so many iterations to get this to work but nope....
I tried the =SUBTOTAL function in SO MANY WAYS as well, I'm lost!
Its hotter than blue blazes here (110°F heat index)
I'm trying so hard to do this on my own but UNCLE
(Skip if you are where I think you are, it's probably MUCH worse... and I'm sorry for ya.

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

(OP)
I've put the "TABLE" workbook to bed for now. All of my information is scrambled.
I can go back to it for more details and adjustments but right now, not even my totals on the "H" column are working.
I moved the calculation to above the "Designer's" column and it was fine, then it wasn't.

My sense of humor over it at THIS moment is NOPE!

Sorry... but I do appreciate the help thus far. I just have to work, I hope. For now its stopping me from being productive and I have so much to add.

Love ya both for your help!
Laurie

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

I can't see what's going on.

But I see that this is what you had...
=SUMPRODUCT((Library!B:B=A7)*(Library!H:H))

And I understand that your table on the Library sheet is now Table1.

So, that formula should become...
=SUMPRODUCT((Table1[Type]=A7)*(Table1[Cost]))

One problem at a time.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

(OP)
Skip,

I was hoping it was a Syntax error but had no clue how to implement it. I entered this into the TYPE worksheet and VOILA!

I then copied it into the RESOURCE worksheet, changing "Type" to "Resource" and VOILA again.

Hero comes to mind. I'm sorry I'm so ignorant but I don't know til I ask, now I have this as a reference using a TABLE.
Thanks so much..
Laurie.

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

One problem at a time.

When you enter a formula, you eventually get to a cell or range reference.

In most cases you can point your cursor to the cell or range and the appropriate reference will appear in you formula without needing to key in the reference.

When referencing a full column range in a Structured Table, you need only point your cursor to the Table Header for the appropriate column.

Which is more meaningful?

=SUMPRODUCT((Library!B:B=A7)*(Library!H:H))

...or...

=SUMPRODUCT((Table1[Type]=A7)*(Table1[Cost]))

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

Here's another sheet with Type & Resource summarization examples.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

(OP)
Thanks Skip,

Correct me if I'm wrong, but my dates go out much further... so as long as I reference 2001, 2002 (say in B2 and B3) and I simply change the formula to reflect TABLE1 instead of TABLE2, this will work?

I know what they say about the word "ASSume" but I'll play with that, recreating in my workbook. I have MANY more Types and Resources than you see in the SAMPLE file I uploaded and far more years... so I've got some work to do.
I'm not tearing my hair out (yet) LOL but I was REAL CLOSE about 5-6 hrs ago...

Thanks, its PLAY TIME :)

Laurie

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

(OP)
SKIP YOU ARE A ROCK STAR!!!

Thank you OH SO VERY MUCH!! I figured out the formulas provided. Can't say I'd be able to write one but I understood them and was able to create my OWN INFO... I'm keeping the TABLE FORMAT! I owe you on that HUGELY!!

ANDY YOU ARE A ROCK STAR TOO!!!

Your VBA code for the Hyperlinks works fantstically!
I had to save the workbook as a Macro Enabled Workbook (.xlsm).

My question regarding this is:
If I save the file as an .XLSX file I'm sure the Macro won't work but
will that mess up the rest of the data?

The Macro file I will keep save if I need it in the future but I it does scare me and would prefer continuing as an XLSX file.

That's all I need for now and Goodness Gracious.. I'm SO THANKFUL and HAPPY :) (and I have all my hair yet) hehehehe

SO, til I get another wild idea in my head, rummage around on my own to try and figure it out and then am unable to, STAY SAFE, KEEP COOL and you are both my HEROS! :)

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

Quote (Laurie)

If I save the file as an .XLSX file I'm sure the Macro won't work but
will that mess up the rest of the data?

No, your data will be just fine, but... if you want to have your macros available to you any time in any workbook (and save any file as an .XLSX file), you may want to investigate saving your macros in 'Personal Macro Workbook'



BTW, after you will solve all your current issues, we can work on setting this code of mine to address any sheet and any column with the hyperlinks, if you want to. smile

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

Quote (Laurie)

Its hotter than blue blazes here (110°F heat index)

Reset your thermometer to C (like the rest of the world*), it will look at lot cooler with 43.3°C
roll1

*with only 2 other countries using F

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

In 1979, my son's 6th grade math teacher swore to me that the USA was going metric in a year. So I sticking to Fahrenheit. It's 85 DF headed for 104 DF here in North Texas. 😅

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

Skip, say: "Thank you" to Senator from Iowa Charles Grassley for killing the “foreign system of measurement”
banghead

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

(OP)
I'm ALSO in North Texas!!! (Dallas suburb) :)

Andy, I'm with Skip...

HOWEVER... hubby was a calibration technician (PMEL in the Air Force back in the 70's) and carried on with that field prior to our retirement and did all things in Metric. He gets SO UPSET with me when we watch TV and they say X--something and I say, what is that in REAL numbers? He says, WHY DON'T YOU LEARN METRIC, its SO MUCH easier!!!

I say, NOPE... gawd with all of this stuff in my head, I'll fall back on "Old Dog/New Tricks" and stick to learning one of MY passion... Excel! and my other Passions... Crochet and my Doggy... (my first @ 60 yrs old.. my spirit animal) but we almost lost him in January) he's old but then he got sick, we still don't know what was the cause or what it was, but the meds... (Dachshund mix) is losing ALL of his hair... :( Love him! Well it goes without saying.. hubby... OF COURSE!

My bandwith is diminishing every moment... I have, since 30, feared Alzheimer's... and my mom, who will be 90 tomorrow, has it... it freaks me out. Oh well, this took a personal turn, not technical.

REAL NUMBERS RULE!! Metric... hmmmpppffff ponder thumbsdown bigsmile LOL

Hearts all around!

Thanks Guys!!!
Laurie

RE: Excel 365 – (Formula/Function for Hyperlinks) and (Totals Yearly & Quartly)

(OP)
Andy,

Thanks for the offer on the Macro use in any sheet, any column. For now, I'm way behind ... but, I may just take you up on that offer.

I appreciate you :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close