Excel 365 - Auto-Populating a cell from date in the next cell. 2

[ladyck3]

NOTE: I submitted this one with a file attachment and hit Submit, but it didn’t show up. This is a resend. I hope it doesn’t become a duplicate.

I wish I knew more and didn't have to bother y'all but here I am again.
I have attached a sample spreadsheet. What I am ATTEMPTING to do, is this....
NOTE: The filenames Crochet Pattern.pdf files. Because they look strange, I thought I would explain.

In Column E are the Titles.
Some will have (Vid) meaning there is a corresponding Video to go with the written instructions.
Others will have (Vid) within the filename also Only indicating the information is in Video form ONLY.
Some will not contain either signifier.

The function I am seeking:
When pasting new titles in the spreadsheet, (I truly have been attempting this for about a week, and I’m lost.
I wish to have, I guess I should say the Vid - ColumnD “READ” the new data in the Titles- ColumnE recognize if either or both of the signifiers are present and automatically populate Column D Vid or VO if they appear in Colmn E - "Title"

See Lines 2-16 and Line 19 (I manually entered) V.
See Line 17 showing VO (again, manually entered again)

I am hoping upon hope that the only way to accomplish this is not a VBA thing, it scares me. Maybe a Vlookup? I used to use Vlookup ALL the time, 10 years ago. But as they say… if you don’t use it, you DO LOSE IT. It’s lost, but I’ll keep playing until I hear back.

Y'all do know how much I appreciate you and I do learn from your help each time. The problem with the previous assistance on this ever-growing project (4 years now) is that it’s all been statictical. This is new territory (or maybe old and in the deepest recesses of my brain) I just don’t know anymore so thanks for helping.

Thanks...
Laurie

 

[combo]

You can convert column 'Vid' to calculated column with formulas with structured references. If I follow required logic, formula to use:
[tt]=IF(COUNTIFS([@Title],"*(Vid)_Only*")>0,"VO",IF(COUNTIFS([@Title],"*(Vid)*")>0,"V",""))[/tt]

Copy it to D2 and copy down. New entries in 'Title' column should automatically expand table and fill adjacent 'Vid' cell with the same formula.



combo

 

[Andrzejek]

VBA thing, it scares me

Why? It is FUN!

Look at this little, cute, nice Function in VBA with your name:

Public Function [blue]ladyck3[/blue](ByRef strIn As String) As String

If InStr(strIn, "(Vid)_Only") Then
    [blue]ladyck3[/blue] = "VO"
ElseIf InStr(strIn, "(Vid)") Then
    [blue]ladyck3[/blue] = "V"
End If

End Function

And now you can use it as your own Formula in Excel like any other Formula:
[pre]
D E
... ... ...
16 V SWEATER_River_Stone_Coatigan_[blue](Vid)[/blue]_XS-5X
17 VO AFGHAN_Layer-Look_Granny Squares_[blue](Vid)_Only[/blue]_0
18 V SQUARE Shapeshifter Square [blue](Vid)[/blue] 12
[/pre]
Just because in cell D16 you have a Formula: [tt]=ladyck3(E16)[/tt]

You can keep this little VBA in your thread707-1594938

---- Andy

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

 

[ladyck3]

combo!

I've been trying to write to you but the "y" on my keyboard started the finicky dance... its ok now. Ever try to type without a "Y"... ick.

Thanks, that's prefect... I just have to change my structure a little.
I removed the _ within the fomula changing it (Vid)_Only*") to read (Vid) Only*") because I the "_" do not exist when I clean up the list.

Otherwise, its great. I just have to make sure the two are together, as I put the Only in difference places... I'll stay static on that. (The letter on the keyboard is now not typing the letter between the N and , LOL

Note the titles are not all cleaned up as of yet, I run a Find/Replace to remove the underscores when the section is complete.

Thanks, issue solved!
Laurie

 

[ladyck3]

Ok keyboArd is messing up ...AgAin

Andy... it just frightens me, I dont remember how to Access / use them. If they get messed up I cAnnot fix them. My blood pressure goes up And I get flush. no kidding... no joke...

HOWEVER, I will keep it & put it in my vAult... & check the threAd you provided...

I completely AppreciAte you though & keep trying, mAybe some dAy I'll just get it??? LOL

 

[Andrzejek]

It is like riding a bike. It is scary at first, but once you get it - you cannot get enough of it
> I dont remember how to Access / use them
To access VBA, simple shortcut to remember: [tt]Alt-F11[/tt]

You may not realize, but you can step thru the VBA code line-by-line at your own speed and see exactly what's going on in the code. It is pretty neat.

---- Andy

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

 

[ladyck3]

Andy,

Please read this, its long, I know, but I have a true need to try to explain where I'm at....

Poor analogy for me personally... in 1969 I was on my bicycle when at the end of our street, some bozo turned into the street to park the wrong way (facing me) on the wrong side of the street.... he ended up wedged between 2 trees on the opposite curb and I had a gashed leg from the bolt holding on the handle bars, the damage was bad, the bold bore a hole in my inner thigh as I flew over the handles and today, I still have the scares... concusion, from my head putting a dent in the hood of his car (a station wagon). All of this, the very day after having my braces taken off... after wearing them for 3 years... Oh, and a bottom tooth popped out, landed on my pants pocket, they picked it up, stuck it back in the socket. (a couple of years, being dead, it turned black and had to be pulled).. and my 2 middle top teeth were shoved up and back (the reason for my braces in the first place, over bite) causing a need to have braces on AGAIN for another full year. And also a week in the hospital. I NEVER GOT ON A BIKE AGAIN... WE ARE TALKING 54 YEARS AGO. Sorry for the personal story on a technical site but BAD ANALOGY. (no fault to you, it is a common expression, and no worries, its just that THIS one... hits me right in the gut/teeth. I have had problems from ths point for the rest of my life. One thing after another.. even dreaming of losing a tooth and within a week, it would happen. ARGH... we're good though, no worries.

I don't know how else to explain, that I may be able to look at a VBA script and kinda understand it, maybe... but could never understand it enough to be able to write it for myself. That ALT-F11 is easy for you to remember, its your THANG! See what I mean?

Last night at .. this moring, at 3:10am my husband who worked in the Precision Measurements Equipment Laboratory field (Calibrations) in his years in the Air Force and his career thereafter used the metric system, it was his second language, so to speak. Well for you, Andy, my mind equates that to VBA, "Its so easy, ...ALL YA GOTTA DO... SIGH!

We were up until almost 5:00 am, he was trying to explain to me how easy the Metric System is over standard/imperial measurements. If you want to catch a star you HAVE to know metric to pinpoint it, standard US math will not pinpoint a thing... yadda yadda yadda... PLEASE, after almost 70 years on this planet, I just don't get it, it blows my mind ... its over my absorption rate... brain full, no room and quite frankly... I have never and will never want to pinpoint a star. VBA is the same sort of thing... just pooooffffftt over my head.

With Excel, I never took any classes, when I started using it, I learned on my own, and sometimes got help from Tek-Tips and experts here who know so much more than I do... I started computing in 1988, on a Commodore 64, there was no Excel, or Office, it did have a word processor that I didn't understand. I don't know the functions in Excel as in taught and know all that it can do, I get help, I know the places to go, and have resources that help, like Skip, combo, you and a few others... is there an "xlxt" expert .. he or she has helped as well. I just don't know the entirety of it, all what it does... formulas are kind of the same, if I know what function is available and how to use it, given a formula, I at least have the capability in most cases... to tweak it if Need that capability in a different location or topic.

This large story not short is to try to politely as possible, explain... VBA SCARES ME. Think of "Old Dog/New Tricks"
That said.. I am NOT trying to discourage you, because I see VBA brings you joy and I do not minimize that for you, and appreciate seeing it .. and do encourage the assistance just in the minute case that it might catch... but ya just can't infuse it into me... My head does just not work that way EVERY and ALL RESPECT completely intended.

(((( Hugs )))

BTW, when he felt successful in teaching me the basics of the metric system so I might get a grasp, I said... OK... its your turn, I'm teaching YOU to CROCHET. OH NO.. I have no need to know that, I won't use it, its not happening.... DITTO BUCKO! NOW you get it. LOL We all have our lanes, ya know? I've re-read this several times, I keep getting pulled away from my thoughts so if this incoherent, my apologies.. I have to stop. I have rambled enough. I do applaud your enthusiasm... your VBA is my CROCHET to me. Wanna learn to do a single crochet (sc) or double crochet (dc) or Treble (tr)? those are the US English terms, they are different in the UK and other countries... see.. that is MY LANE Ramble /off

 

[ladyck3]

Appending VBA discussion

Andy,
I have saved your response and it sorta kinda makes sense. That link the the last response looks quite interesting as well. I apologize for rambling... {BUTTON WAS PUSHED} is all, no harm no foul

sweet and simple to me, was the formula from combo and its working perfectly. I'll make a copy of my inventory as soon as I'm caught up with my data entry on same... and truly give this a honest shot at trying to understand. Promise! You are in my OneNote as well as combo's response.

Thanks for the help....

 

[Andrzejek]

Point taken
In respect to your husband's 'metric' - born and raised on metric system. After 30 years of 'imperial' life in US, it is still weird and confusing to me.

---- Andy

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

 

[ladyck3]

Mad respect Andy… mad respect!!