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

 

[Andrzejek]

Its hotter than [blue]blue blazes[/blue] here [red](110°F heat index)[/red]

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


*with only 2 other countries using F

---- Andy

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

 

[SkipVought]

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,
[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]

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


---- Andy

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

 

[ladyck3]

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 LOL

Hearts all around!

Thanks Guys!!!
Laurie

 

[ladyck3]

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