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.

=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 :D

[QUOTE]then use it in an if statement

=IF(Column C>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 message here')[/QUOTE]



And you kiss your mother with that mouth!?!?!?!?!?!

[QUOTE=Master D]then use it in an if statement

=IF(Column C>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 message here')



And you kiss your mother with that mouth!?!?!?!?!?![/QUOTE]

Haha well unless you have any suggestions? thats the best I can do without actually opening excel to test it. Note the second edit. That one might have more of a chance

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.

=IF(VLOOKUP(P12,F6:G12,2,FALSE)>0,VLOOKUP(P12,F6:G12,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 :D

Case closed

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.