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

[ladyck3]

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=1821455

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[Andrzejek]

I do not know VBA or how to implement it.

Well, that would be a good time to learn

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:

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

---- Andy

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

 

[SkipVought]

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...
[pre]
=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])
)
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[ladyck3]

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

 

[Andrzejek]

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 [red]5,190[/red] then you will need to modify my code to:

...
For R = [red]5190[/red] To Sheet1.UsedRange.Rows.Count
...

---- Andy

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

 

[ladyck3]

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.[ul]
[li]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. [/li][/ul]

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

 

[ladyck3]

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

 

[Andrzejek]

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:

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.

---- Andy

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

 

[ladyck3]

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

 

[ladyck3]

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

 

[ladyck3]

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.

 

[ladyck3]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[ladyck3]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[ladyck3]

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

 

[ladyck3]

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!

 

[Andrzejek]

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.

---- Andy

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