• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Proper Formula

K

keanu

New Member
Hi,


I want to ask you what formula that i need to use if i have one blank worksheet and i tried to link it to another worksheet that contain database


what i have in mind is: if in Worksheet A, Garlic is blank and today's date is 1 Jul 12, then it will refer to worksheet B which has the same date 1-Jul-12 and show the value of "0"

or if Onion is blank on Worksheet A and today is 1-Jul-12 then the formula should refer to Worksheet B etc


I was trying to use HLOOKUP and VLOOKUP but did not work. Please share your idea.


Note: Im not quite sure about the windows version that currently use, but excel format is 2003


Thanks and regards,


Keanu
 
You mentioned several terms, but we have no idea where those items are at on your worksheets, or what the data table looks like. could you post a sample of what the data table looks like, and state what your inputs are and what you'd like to see returned by the formula?
 
@Luke M


Hi!


We already know that... (Hypothesis):

H/V-LOOKUP is... where?: within a formula

formula is... where?: within a cell

cell is... where?: within a worksheet

database in a worksheet is... where?: within a worksheet (thanks to tautology)

worksheet is... where?: within a workbook

workbook is... where?: withing a folder

folder is... where?: within a drive

drive is... where?: withing a PC (let say notebook)

blank is... where?: nowhere, as it's blank (as nothingness is the negation of entity)

date is... where?: in the calendar, where if not?


So and applying the complete induction principle I assume that... (Tesis):

garlic should be... where? at the grocery

onion should be... where? at the grocery


With which we can conclude that... (Demonstration):

keanu brought his notebook to the grocery and it slipped into the vegetable drawer


But.. be careful! The dangerous part is that... (Corollary):

he might be walking into something rare as the book contains highly critical and sensitive material

http://www.nytimes.com/2010/06/09/dining/09curious.html?pagewanted=all


Regards!


PS: Sorry but I couldn't resist the temptation :$
 
Hi, keanu!


Now seriously as always (well, nearly always), first of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


I agree with what Luke M wisely wrote, it'd be very useful to have your workbook structure description, the worksheets and its columns, the type of data, and what to you intend to do, with a manually written example.


You can post it here or upload a file as indicated in second green sticky post.


Regards!
 
Hi Luke and SirJB7,


thank you for responding my question and do apology as i'm not expert in explaining what i want. Let me try once again in a simple way: I have two worksheets, Book 1 is database that i generate from system on daily basis and it contains of Date, orange, Manggo, Tulip and Peanut


While Book2 contains: Code, Description and Date. I want to have a formula that can look up to Book 1, in example: if today is 1 Sep 12 then on column D3 will fill up with 16 Orange so that i will get result from, by looking up to Book1.


Hopefully, you can understand what i want to achieve. Below is the file share, so you will have a clear picture.


https://skydrive.live.com/?cid=759EAD333A18E1E5&id=759EAD333A18E1E5!111


Once again thank you for your friendly support.


Keanu
 
Hi, keanu!


Give a look at these files:

https://dl.dropbox.com/u/60558749/Proper%20Formula%20-%20Book1%20%28for%20keanu%20at%20chandoo.org%29.xlsx

https://dl.dropbox.com/u/60558749/Proper%20Formula%20-%20Book2%20%28for%20keanu%20at%20chandoo.org%29.xlsx


In Book1, 3 named ranges:

- DateList for column A (A4:A34)

- FruitList for row 3 (B3:E3)

- Monthly table for range B4:E34


In Book2, 1 formula (range D3:G6):

=SI.ERROR(INDICE('Proper Formula - Book1 (for keanu at chandoo.org).xlsx'!MonthlyTable;COINCIDIR(D$2;'Proper Formula - Book1 (for keanu at chandoo.org).xlsx'!DateList;0);COINCIDIR($B3;'Proper Formula - Book1 (for keanu at chandoo.org).xlsx'!FruitList;0));"") -----> in english: =IFERROR(INDEX('Proper Formula - Book1 (for keanu at chandoo.org).xlsx'!MonthlyTable,MATCH(D$2,'Proper Formula - Book1 (for keanu at chandoo.org).xlsx'!DateList,0),MATCH($B3,'Proper Formula - Book1 (for keanu at chandoo.org).xlsx'!FruitList,0)),"")


Looks complicated because of workbook names. Do this:

- download both

- rename them as you want

- open second book

- choose update external links

- modify/update links

- change source

- browse and select first book

- accept

- open first book

- save both files


Just advise if any issue.


Regards!
 
Hi SirJB7,


Thanks for the formula given to me. Having followed your instruction, i found that the formula come to "Name" when i try to do it by myself. is there any relation, because my excel is 2003? below you will see what i have tried with your formula.


https://skydrive.live.com/?cid=759ead333a18e1e5#!/?cid=759EAD333A18E1E5&id=759EAD333A18E1E5!111


Please advice what step that i miss


Thanks and regards,


Keanu
 
Hi, keanu!


I didn't read that you used a 2003 version. And yes, you have to IFERROR by ISERROR function.

=SI(ESERROR(INDICE('Book1.xlsx'!DateList;0);COINCIDIR($B3;'Book1.xlsx'!FruitList;0)));"";INDICE('Book1.xlsx'!MonthlyTable;COINCIDIR(D$2;'Book1.xlsx'!DateList;0);COINCIDIR($B3;'Book1.xlsx'!FruitList;0))) -----> in english: =IF(ISERROR(INDEX('Book1.xlsx'!DateList,0),MATCH($B3,'Book1.xlsx'!FruitList,0))),"",INDEX('Book1.xlsx'!MonthlyTable,MATCH(D$2,'Book1.xlsx'!DateList,0),MATCH($B3,'Book1.xlsx'!FruitList,0)))


Sorry for the inconvenience.


Regards!
 
Hi SirJB7,


Thank you for the prompt reply, i had tried the formula many times but excel kept informed me that the there are too many argument and thus this can not give me result. I recheck my step and i didn't miss anything. To be honest, i'm not really super in this excel :( but i wanna learn as much as i can :). I also tried to check any questions in this forum to see if there's anything related to my subject, but again still not gain anything. Could you please advice again my friend.


note: the same link, perhaps you wanna use it again

https://skydrive.live.com/?cid=759ead333a18e1e5#cid=759EAD333A18E1E5&id=759EAD333A18E1E5!111


Thanking you in advance...


Keanu
 
Hi, keanu!


I'm coming back to my earlier posts.


I wrote that I created in Book1 (now Data), 3 named ranges:

- DateList for column A (A4:A34)

- FruitList for row 3 (B3:E3)

- Monthly table for range B4:E34

which are used in Book2 (now "Worksheet A") in the formulas of cells D3:G6.


I think that you copied and pasted (with a subtle error that I'll comment later) the formulas in my uploaded Book2 and used your own Book1, without creating the named ranges. That's the first error.

The second one is that one of the formula in cell D3, you're intending to use the function SI.ERROR (in english IF.ERROR) as in my wrong upload since it's available only for 2007 and 2010 versions, not for 2003. So you should use the formula with the function ESERROR (in english ISERROR) which works for 2003 and 2007/2010 too.


I'll type again here the new formula for workbook "Worksheet A2" to be typed in D3 cell and copied to D3:G6 range. These are the steps:

- open your Book1 (Data)

- open your Book2 (Worksheet A)

- go to 1st book

- define the 3 named ranges: DateList for column A (A4:A34), FruitList for row 3 (B3:E3), Monthly table for range B4:E34... you can do this from Formula tab, Named Ranges group, Create/Assign Name icon

- go to 2nd book

- in D3 cell type and copy thru D3:G6:

=IF(ISERROR(INDEX('Data.xlsx'!DateList,0),MATCH($B3,'Data.xlsx'!FruitList,0))),"",INDEX('Data.xlsx'!MonthlyTable,MATCH(D$2,'Data.xlsx'!DateList,0),MATCH($B3,'Data.xlsx'!FruitList,0)))


Check that figures are correctly displayed and that doesn't appear any error of Name, Value, or other. Save both workbooks, et voilá!


Regards!


Sorry for the inconvenience.
 
Hi SirJB7,


Until i sent this email, i still tried to use the formula but i dont know why it's always say that "too many argument". Now i'm not sure if it is because my excel 2003, to let you know that i really like this formula. Perhaps you still have magic formula for my reference...


Thanks and regards,


Keanu
 
Hi, keanu!


Fixed. I used your file names, created the missing named ranges, copied 2003 formula, saved workbooks, compressed them, and here's the link:

https://dl.dropbox.com/u/60558749/Proper%20Formula%20%28for%20keanu%20at%20chandoo.org%29.rar


Just download in a folder, open first Data.xlsx, then 'Worksheet A.xlsx', if it prompts for updating external links, accept and change source, and nothing more.


I hope it works.


Regards!
 
Hi SirJB7,


how's your weekend over there!


i tried to download the file, but when opened it then showed symbol that i did not understand. I think the file can't be download from my PC, therefor could you please send it to my email: excel6155@hotmail.com. Looking forward to hearing from you again.


Many thanks,


Rico
 
Hi, keanu!

Just checked the link and it works, I suggest you to try again. Besides I sent it by mail minutes ago.

Regards!
 
Hi SirJB7,


I had respond your email and also provide you with a print screen so you know what is really happened when i download the file.


Thanks and regards,


Keanu
 
Hi, keanu!


The file I sent you and that I uploaded is not an Excel file, in fact is a .rar file, a compressed file with the standard utility program WinRar, and which contains the 2 Excel files we were talking about:

- Data.xlsx

- Worksheet A.xlsx


You have to:

a) open the uploaded .rar file with the associated program (WinRar, or WinZip, or other alike)

b) extract the 2 contained Excel files .xlsx to any folder

c) then process to play with them with Excel.


Regards!


PS: answered and sent the two separated files yet
 
Hi SirJB7,


I did follow your step but my PC is weird eventhough i tried to open it using winzip and still not work. The only possibility is sending the excel file as normal attachment. I'm sorry for the back n forth email like this.


Best regards,


Keanu
 
Hi SirJB7,


I received your email and thanks finally i can open the file, at least there's still a hope for myself :)


Many thanks


Keanu
 
Hi, keanu!

Glad you succeeded, thanks for your feedback and welcome back whenever needed or wanted.

Regards!
 
Hi SirJB7,


i test the formula on my worksheet however the cell is showing blank, no values as a result. there is no indication "NA" or "Formula Error" it just show blank on the cell that i type the formula. What would be the possibilities caused?


Regards,


Keanu
 
Hi, keanu!


Three questions:

a) you tested both workbooks with my uploaded files or applying the formulas there to you own workbooks? if not with my uploaded files, then check those firstly

b) if checked with mines, did you pass for the process to updating linked formulas as described upwards? if not, then do it now (http://chandoo.org/forums/topic/proper-formula#post-35390)

c) if already done, could you please upload again those two files? thank you


Regards!
 
Hi SirJB7,


How are you today? In regards to your question, please find the answer below:


Three questions:

a) you tested both workbooks with my uploaded files or applying the formulas there to you own workbooks? if not with my uploaded files, then check those firstly ==> I tested first with the file you sent to me and worked properly, then i apply to my own workbooks and found the result is blank


b) if checked with mines, did you pass for the process to updating linked formulas as described upwards? if not, then do it now (http://chandoo.org/forums/topic/proper-formula#post-35390)==> yes, as i mentioned above the formula works well with your file


c) if already done, could you please upload again those two files? thank you==> Please find below link:


https://skydrive.live.com/?cid=759EAD333A18E1E5&id=759EAD333A18E1E5!111


Kindly advice what should i do from here.


Thanks and regards,


Keanu
 
Hi, keanu!

I downloaded your two files to the desktop, opened them with Excel and worked properly. Could you try the same with my previous files to see what happens? If succeeded then check with both yous at other machine, if available.

Check the screen image:

https://dl.dropbox.com/u/60558749/Proper%20Formula%20-%20Worksheet%20A%20%28for%20keanu%20at%20chandoo.org%29.png

Regards!
 
Hi SirJB7,


I exercised a lot to understand the formula and finally i can use it, perhaps my excel a little bit old fashioned and need to try more than three times to apply the formula. I really appreciate your assistant and patient. I start to enjoy the word of "automate" by excel :). Talk to you again later.


Big Thanks,


Keanu
 
You must log in or register to reply here.
Back
Top