At the moment there’s a *live* document with a number of Vlookups and sumifs that need to correct as they drive some important formulae. However one of the vlookups comes from a different document the name of which changes every day. At the moment someone has to go in and change the vlookup slightly every morning to reflect the new file for the day.
Is there any way of automating this? The new file every day has a standard naming convention, the only part which changes is the date which is in UK date format and all the files are saved in the same place. Is there a way of telling the v lookup to change the file it is looking up from depending on what today’s date is?
will fill a cell with today's date. if the lookup into the file name contains a cell that references the date (or the date minus 1 or whatever) that you just put in a hidden tab then that might do the job. unsure though, going to try and test something now
which is some sort of downloadable addon to excel. it apparently can look up similar text strings. something could probably be built with that. i, however, due to my shitty work laptop (on which i can't even change the time - and it's wrong) can't download it to have a play with it. i really want to
I've made a spreadsheet that scores every woman in the business, and its horrible, like really low down dirty shut, so I want to be able to code it so only I can reveal the true text. But I just can't do it. I can convert everything into Thai currency but not into anything useful
converting "2 / 2" (for example) into "2 Feb". Note: I try formatting the cell differently (general, number, text, or whatever all the options are) and it still does it.
Did I ask the fucking thing to do anything to the text I put in the cells? No, I fucking did not!
What if I say, "fuck you, you dumb arse piece of shit. Stop being so fucking counter-intuitive and just work the way a normal intelligent person would expect you to work!"
i have a column with about 30 of the following data:
4841 Jones Keith
14065 Crawford James
14067 Ewart Peter
The numbers to the left are ID numbers that can be anywhere from 3 to 5 digits long. I want a formula (I guess 'MID' would be the base of it) that will pick out the name only with no space/figure to the left.
but I'd normally split this sort of thing into a couple of columns: makes it easier to identify errors and stops you unnecessarily repeating FIND calls. This also allows for the inevitable situations when there are more or fewer spaces than you expect.
Assuming your data starts in A1, then:
B1: =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
- Get the number of spaces in A1; use this as a control to prevent errors in later finds (not included)
C1: =FIND(" ",A1)
- Get position of first space.
D1: =FIND("|",SUBSTITUTE(A1," ","|",B1))
- Get position of last space.
E1: =RIGHT(A1,LEN(A1)-D1)&MID(A1,C1,D1-C1)
- Take all characters following the last space then concatenate all characters between the first and last spaces.
but it's harder to debug and more ineffecient as, in something like this, you'd be making the same call repeatedly. Particularly if you want to wrap the FINDs in an ISERROR or something to stop everything fucking up when there aren't enough spaces.
Ive got column N, which has data formatted hh:mm ddd.
In column M I need N minus 30 mins.
I have this formula =TEXT((LEFT(N2,5)-TIME(0,30,0)),"hh:mm") & " " & RIGHT(N2,3) which works well until there is a negative time value.
eg if N2 = '00:01 Sat' M2 should equal '11:31 Fri'
any takers?
I've found this bit which will do the time bit
=MOD(N2-TIME(0,30,0),1), but how about going from Sat to Fri?
Based on the formulae you're using I assume the data is being entered manually in that format. If so, the sat/fri part is meaningless in terms of trying to manipulate it normally. You could come up with a convoluted IF formula but a slightly better way is to use a basic lookup table.
On a separate worksheet to the one with the data on it, enter Sun, Mon, Tue, Wed, Thu, Fri, Sat, Sun into cells A1:A8. The repetition of Sun is important.
Then you can use this to return the prior day if necessary (change the name of Sheet2 as required):
=IF(LEFT(N2,5)/1<TIME(0,30,0),INDEX(Sheet2!$A$1:$A$7,MATCH(RIGHT(N2,3),Sheet2!$A$2:$A$8,0)),RIGHT(N2,3))
The alternate way of doing it would be if you can change the data in column N so you're actually entering the full date and time, e.g. 20/03/13 10:50. You can then set the format of the cell/column to display as hh:mm ddd and the only calculation you need in column M is:
=N2-TIME(0,30,0)
I came up with your lookup solution yesterday afternoon.
dunno which berk thought of the format but its a right pain, but the data is imported that way from a csv file
Ok I actually do.
At the moment there’s a *live* document with a number of Vlookups and sumifs that need to correct as they drive some important formulae. However one of the vlookups comes from a different document the name of which changes every day. At the moment someone has to go in and change the vlookup slightly every morning to reflect the new file for the day.
Is there any way of automating this? The new file every day has a standard naming convention, the only part which changes is the date which is in UK date format and all the files are saved in the same place. Is there a way of telling the v lookup to change the file it is looking up from depending on what today’s date is?
i've never had to do anything like that but i suspect this....
=today()
will fill a cell with today's date. if the lookup into the file name contains a cell that references the date (or the date minus 1 or whatever) that you just put in a hidden tab then that might do the job. unsure though, going to try and test something now
not sure this works
It doesn't. First thing he tried apparently
I did pass your suggestion on though.
write some vba code
that'll do it.
I won't be doing anything
I just feel sorry for the person who has to waste his time every morning before I get into the office.
woah!!! i thought i was helping you! not some schmo you work with!
Ola isn't a shmo! He's a fine man.
And I feel sorry for him, so you're indirectly helping me.
ok, that's fine
this feels like a small thing that should be doable without vba. will get back to you shortly
what's the format of the file name?
i think you can just do it using INDIRECT
you can.
See below.
there we go!
Get a junior to update the lookups every morning
That's what happens at the moment, the b/o guy does it
but it is a bit of a waste of his time, he's a busy man.
http://www.excelforum.com/excel-formulas-and-functions/677285-changing-file-names-within-worksheets.html
Thanks, shall forward on to Ola.
hmmm
I think INDIRECT might work.
This kind of works:
=LOOKUP(1,INDIRECT("'C:\Users\Phil\Desktop\Datafiles\[" & TEXT(TODAY(),"yyyy-mm-dd") & ".xlsx]Sheet1'!$A$1:$A$5"),INDIRECT("'C:\Users\Phil\Desktop\Datafiles\[" & TEXT(TODAY(),"yyyy-mm-dd") & ".xlsx]Sheet1'!$B$1:$B$5"))
However, for some reason it will only work when the lookup file is open.
If you don't mind vba the formula to do this is really simple:
Sub UpdateLookup()
Range("A6") = "=LOOKUP(1,'C:\Users\Phil\Desktop\Datafiles\[" & Format(Now(), "yyyy-mm-dd") & ".xlsx]Sheet1'!$A$1:$A$5,'C:\Users\Phil\Desktop\Datafiles\[" & Format(Now(), "yyyy-mm-dd") & ".xlsx]Sheet1'!$B$1:$B$5)"
End Sub
If you change the sub name of the above it will automatically run when you open the workbook:
Private Sub Workbook_Open()
Range("A6") = "=LOOKUP(1,'C:\Users\Phil\Desktop\Datafiles\[" & Format(Now(), "yyyy-mm-dd") & ".xlsx]Sheet1'!$A$1:$A$5,'C:\Users\Phil\Desktop\Datafiles\[" & Format(Now(), "yyyy-mm-dd") & ".xlsx]Sheet1'!$B$1:$B$5)"
End Sub
You will obviously need to change the paths and date format etc if you want it to work with your files.
How about this?
Two lists of street names. In columns. Whole streetnames in one cell.
Can I check if a part of a street name that appears in one column appears another?
E.g. There might be a Bedwetter Avenue in one column, and a Bedwetter Crescent in another, and I wanna flag that up somehow.
Even if it is doable, I can envisage the results being cack cos of the duplication of generic parts of streetnames like Avenue and Crescent etc.
Probs best just to copy the two sheets into one so that all the street names are in one single column, and just sort all by name, right?
so you'r trying to find any two that have the same first bit? but it doesn't matter what it is?
Correct.
how big and how varied are the lists you have?
less than a five hundred rows.
not that varied. i.e. I'd expect less than ten percent of the entries in either column to return a match in the other column.
*ah, that makes them fairly varied, i guess.
there is apparently a thing which i did not know about called a fuzzylookup
which is some sort of downloadable addon to excel. it apparently can look up similar text strings. something could probably be built with that. i, however, due to my shitty work laptop (on which i can't even change the time - and it's wrong) can't download it to have a play with it. i really want to
sounds interesting. cheers for looking.
but no chance of installing an add-on. my works pc is more locked down than a very locked down thing.
Ok, imagine one set are in column A and one in column B
in column C row 1 put this, and then drag and drop:
=LEFT(A1,FIND(" ",A1)-1)
It should return "Bedwetter"
In column D (drag and drop):
=LEFT(B1,FIND(" ",B1)-1)
Finally in column E (drag and drop):
=IFERROR(LOOKUP(C1,D:D,B:B),"")
If there is a match the value in column E should be the value from Column B. If there is no match it will be blank.
Hopefully that is what you were after :-)
i'll have a play with ^this tomorrow.
thanks.
Yes on XC2003
I want to turn one set of words into another. BAsically to code them and make a document super secret.
Tell me how.
Go on
TELL ME
are you making a joke?
no
I've made a spreadsheet that scores every woman in the business, and its horrible, like really low down dirty shut, so I want to be able to code it so only I can reveal the true text. But I just can't do it. I can convert everything into Thai currency but not into anything useful
sample formula:
=((((SUM(C7:F7)/4)+(SUM(C7:F7)*G7))/5)+(IF(H7="yes",(((SUM(C7:F7)/4)+(SUM(C7:F7)*G7))/5)*0.05)))+((IF(I7="yes",(((SUM(C7:F7)/4)+(SUM(C7:F7)*G7))/5)*0.05)))-IF(H7="Ugh",(((SUM(C7:F7)/4)+(SUM(C7:F7)*G7))/5)*0.05)
ahhaha
=(ahhaha)?
this doesnt work
come on dude
this thread has not delivered
NOT DELIVERED
wtf
well where;s my code help
I think this could start a row
*column
Ho do I stop this stupid cunting piece of shit from
converting "2 / 2" (for example) into "2 Feb". Note: I try formatting the cell differently (general, number, text, or whatever all the options are) and it still does it.
Did I ask the fucking thing to do anything to the text I put in the cells? No, I fucking did not!
put an apostrophe at the start
What if I say "no"?
What if I say, "fuck you, you dumb arse piece of shit. Stop being so fucking counter-intuitive and just work the way a normal intelligent person would expect you to work!"
What then, hey?
then it will put 2 Feb in for you
What if I take a screwdriver out of my desk drawer
and drive the fucker right through the middle of the offending cell and then piss on its smoking ruins?
You will have a broken monitor and within the workings of your computer the cell will contain
2 Feb
Ha! That's where your wrong!
I have a iMac and the hard drive, etc, is behind the screen. There's no way that cell will be stupidly showing "2 Feb".
*you're wrong
Goddamn fucking fuck.
You'd need to set the cell format on the column to text before entering anything
It's too late after it's converted it to a date as it's changed the actual number in the cell by then to 41307.
Nah, I delete what I've entered
then try to set the cell format, and it still converts. I only try setting the format for the cell, though, not for the entire column.
Cat_race
i have a column with about 30 of the following data:
4841 Jones Keith
14065 Crawford James
14067 Ewart Peter
The numbers to the left are ID numbers that can be anywhere from 3 to 5 digits long. I want a formula (I guess 'MID' would be the base of it) that will pick out the name only with no space/figure to the left.
my brain hurts today, so here is an AWFUL way to do it
in the next column, use this
=SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A1,"1",""),"2",""), "3",""),"4",""),"5",""), "6",""),"7",""),"8","")
in the column after that use this
=SUBSTITUTE( SUBSTITUTE(B1,"9",""),"0","")
you might end up with some spaces. you can use =TRIM() to tidy that up
SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A1,"Me","Him"),"Coke","Gin"), "You","my mum")
if there is always a space after the numbers and it's the first space, this should do it:
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
that is nice
beautiful
now to switch the two names around?
=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1, FIND(" ",A1))),FIND(" ",A1, FIND(" ",A1)))&" "&RIGHT(A1,LEN(A1)-FIND(" ",A1, FIND(" ",A1)+1))
almost there...
wait it doesn't work
ooops i put it together the rwong way round
=RIGHT(A2,LEN(A2)-FIND(" ",A2, FIND(" ",A2)+1))&" "&LEFT(RIGHT(A2,LEN(A2)-FIND(" ",A2, FIND(" ",A2))),FIND(" ",A2, FIND(" ",A2)))
Only 30?
Just go through and pick them out manually, you lazy fuck!
or save it in a text file and import it using space delimited
text to columns
yeah, or that
i always forget about that, that would have saved at least half of this thread happening!
Dont want to step on anyone's toes
but I'd normally split this sort of thing into a couple of columns: makes it easier to identify errors and stops you unnecessarily repeating FIND calls. This also allows for the inevitable situations when there are more or fewer spaces than you expect.
Assuming your data starts in A1, then:
B1: =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
- Get the number of spaces in A1; use this as a control to prevent errors in later finds (not included)
C1: =FIND(" ",A1)
- Get position of first space.
D1: =FIND("|",SUBSTITUTE(A1," ","|",B1))
- Get position of last space.
E1: =RIGHT(A1,LEN(A1)-D1)&MID(A1,C1,D1-C1)
- Take all characters following the last space then concatenate all characters between the first and last spaces.
alright billy five columns
You can fit most things into one formula
but it's harder to debug and more ineffecient as, in something like this, you'd be making the same call repeatedly. Particularly if you want to wrap the FINDs in an ISERROR or something to stop everything fucking up when there aren't enough spaces.
here goes hope you follow.
Ive got column N, which has data formatted hh:mm ddd.
In column M I need N minus 30 mins.
I have this formula =TEXT((LEFT(N2,5)-TIME(0,30,0)),"hh:mm") & " " & RIGHT(N2,3) which works well until there is a negative time value.
eg if N2 = '00:01 Sat' M2 should equal '11:31 Fri'
any takers?
I've found this bit which will do the time bit
=MOD(N2-TIME(0,30,0),1), but how about going from Sat to Fri?
Correction - eg if N2 = '00:01 Sat' M2 should equal '23:31 Fri'
aw not today, i think i'm going to be sick
Depends what you mean about the data being formatted a certain way
Based on the formulae you're using I assume the data is being entered manually in that format. If so, the sat/fri part is meaningless in terms of trying to manipulate it normally. You could come up with a convoluted IF formula but a slightly better way is to use a basic lookup table.
On a separate worksheet to the one with the data on it, enter Sun, Mon, Tue, Wed, Thu, Fri, Sat, Sun into cells A1:A8. The repetition of Sun is important.
Then you can use this to return the prior day if necessary (change the name of Sheet2 as required):
=IF(LEFT(N2,5)/1<TIME(0,30,0),INDEX(Sheet2!$A$1:$A$7,MATCH(RIGHT(N2,3),Sheet2!$A$2:$A$8,0)),RIGHT(N2,3))
The alternate way of doing it would be if you can change the data in column N so you're actually entering the full date and time, e.g. 20/03/13 10:50. You can then set the format of the cell/column to display as hh:mm ddd and the only calculation you need in column M is:
=N2-TIME(0,30,0)
thanks for helping man
I came up with your lookup solution yesterday afternoon.
dunno which berk thought of the format but its a right pain, but the data is imported that way from a csv file
how do i be good at it like nerds?
srs.
use it and keep using it
i do but is simple things
want get good c_r.