|
|
LinkBack | Thread Tools | Display Modes |
#1
|
||||
|
||||
Excel
It's funny but any IT questions I have, I come here first before Whirlpool or Austech.. but us rex drivers are a renowned geek loving people.
Hey I'm looking for an Excel ninja or at least a two stripe black belt. I'm trying to get a spreadsheet to return some advanced shit via vlookups. EG.. lookup a company name in colum A in another workbook then if true (there is data) return the data from colum C (a date) if false (it's blank) then enter a set line of text. You get the idea.. that kinda deal.
__________________
Ho's gotta eat too |
#2
|
||||
|
||||
=vlookup(company name cell,the whole array on the other sheet that you want to choose from, the column number of this array that you want to look in,'false')
...I think. Excel help explains it pretty well then use it in an if statement =IF(vlookup(company name cell,the whole array on the other sheet that you want to choose from, the column number of this array that you want to look in,'false')>0,vlookup(company name cell,the whole array on the other sheet that you want to choose from, the column number of this array that you want to look in,'false'),'insert blank message here') Sorry couple of edits there, i think that might work though, you might need to tweak it a little Last edited by Chris; 03-12-2007 at 10:08 PM. |
#3
|
||||
|
||||
Quote:
And you kiss your mother with that mouth!?!?!?!?!?!
__________________
2016 Subaru Outback - Work Wagon 350NM of CVT Goodness Pit Crew, shit talker. Last edited by Master D; 03-12-2007 at 10:10 PM. |
#4
|
||||
|
||||
Quote:
By the way when i said "column C" i mean select that cell in column C. but then I realised that the whole statement probably wouldnt work so i changed it. Last edited by Chris; 03-12-2007 at 10:13 PM. |
#5
|
||||
|
||||
=IF(VLOOKUP(P12,F6:G12,2,FALSE)>0,VLOOKUP(P12,F6:G 12,2,FALSE),"No entry here")
P12 = the company name F6:G12 = the array that you are looking in 2 = the column number of that array that the dates are in False = means they dont have to be in order I just tried it in excel and it worked Case closed Last edited by Chris; 03-12-2007 at 10:28 PM. |
#6
|
||||
|
||||
Thanks heaps guys.. worked like a mofo charm Chris.
Congratulations!! you've made it to the bonus, super round. I have about 10 different spreadsheets all with different information that I'm trying to turn into a dashboard. 1) with the formular above how can I display a blank cell instead of the text? I tried leaving nothing in the "" but it also brings back a warning, which I could ignore but I'd rather get it right. Q) Can a result also be a hyperlink taking me to the workbook it found the information in? ------------------- Heres pretty tough one if someone wants a crack. Scenario The below show's deliveries in the coming months. A1 B1 C1 D1 E1 F1 G1 Customer name | JAN | FEB | MAR | APR | MAY | overdue ABC PTY 2 3 1 2 1 6 Goal To have three cells.. 1) Delivery's due this month (B+G) 2) Delivery's next month (C1) 3) The rest added up. Now the kicker is I need it to be dynamic, so when feb comes around it ignores the Jan data. I'm not sure where to start with that.. if it's too hard let me know and I'll think of something else.
__________________
Ho's gotta eat too |
Tags |
excel |
|
|