any EXCEL programmers/scripters? I need a question answered

zynizen

zynizen

Registered
I am running OSX 10.4.9, Office 2004, with Excel 2004, version 11.3.3

Problem 1:

I have a template I created for making invoices. in the 3/4 lines to the right of the heading "Bill To: " there is space for an address. very simple ok.

As a template, I don't want to keep typing in the address EVERY time i make an invoice for my 5 clients I have right now.

How do I insert into the 3 or 4 rows a selection drop down or just fill in the address if i type the first few characters of the company name and make it format correctly. Much like a label actually.. I know this can be done fairly simply but, I just cant figure it out..

I've tried:

Lists (cant really figure out the referencing part)
Form (dont know where the data is being stored..)
Query (doesn't work cause I dont have ODBC installed.. dont know how to do that)



ex:

Bill To: CompanyHere
Address Line 1,
City, Prov, Postal Code

would like cell "CompanyHere" to AUTOFILL all three lines if I type "C" if the only company name I had in my list started with C. (same goes for any other letter, or name for that matter)

THANKS IN ADVANCE! I've also checked office online for template references, and help tutorials and still no luck.

--------------------------------------------------------

Problem 2:

At the top I have Date: =TODAY(). (for invoice date of the day I start making the invoice, very basic yes..

At the bottom, another cell i have is DUE DATE:

How do I write a function/formula that calculates exactly 30 days past the initial Date field? I think im on the right track, so far I have:

=DATEDIF(B8,?,"MD")

Thanks again for any help!

Found out that this does it perfectly for problem 2!:
http://www.ozgrid.com/Excel/add-month.htm
 
well i am not an excel expert by any means but what i would do for company info is to make one cell in at the top for company name address etc and set the cell property to wordwrap so it can go on multiple lines. Now in the same column, way down below your printable area, one each row in that column enter the company info you want to fill in that box. Now, when you put your cursor into the invoice and type the first letter of the company, auto fill will give you the closest match. not super tech but would work forsure.

As for the second problem. I had the date in A1 and then in the cell for due date I put =SUM(A1+30). it seemed to work for me?
 
yeh I could do that! :) but what if I had a list of over 30 companies? and yes, for the date, I got it to add 30 days no problem just like you had stated.

thanks.
 
even if you had 30 companies, just just enter their info into the cell on each row. you should just be able to cut and paste from somewhere if you have it typed already, it isnt going to print out and once you have it all entered, you dont have to reenter it again. if you have multiple sheets that you need it to appear on, you could have a seperate sheet with just address and then on each invoice or statement sheet, just throw in a code into the cells below that copy from that address page. so if my address were on sheet 1 and my invoice was on sheet 2, i would enter this code below the invoice in the cell that i want the address to show =(Sheet1!A1) where A1 is the address i want. and then cut and paste that code into the 30 cells or whatever below that one. you can copy that code onto as many sheets as you want in that workbook and then you only have to change the address page once and it will auto update
 
yeh i think i'll do that, thanks.

actually, the mac version doesn't allow me to "autofill". I did exactly that, and put the data at cells B80 to B85 to sample it, and tried typing part of the text and nothing comes up.

Is there a way to autofill in excel for mac?
 
I'm not quite sure if this is what you are after, but I quickly drafted something for you to look at. I created an Excel spreadsheet which uses the "MATCH," "ADDRESS," and "INDIRECT" functions. I've created the spreadsheet in such a way so that you can see what it is doing at each step.

On the second sheet, named "Database," you give each company a short name/code and then put their details into the relevant columns. On the first sheet, you type the associated code of the desired company in the yellow cell at the top. Further down the page, the name of the company and its address should automatically fill in...

Using the method I adopted here, you can update a database of companies on Sheet 2 (i.e. the one named Database) as you go along. I've set the spreadsheet to search the database for nearly one thousand rows (companies), so that should keep you going for a bit. :)

Hope this might be of use!
 

Attachments

  • Invoice_Address_new.xls.zip
    3.5 KB · Views: 2
actually this works well, but How do i get rid of the middle text you have? I just want the top and bottom cells you have to work with the database on the second sheet. otherwise yes this is exactly what I already have, except it uses a cell verification formula i did.

thanks! I could possibly send you what i already have and see if you can just modify it for me, but I like knowing how things are done.

thanks
 
zynizen said:
actually this works well, but How do i get rid of the middle text you have? I just want the top and bottom cells you have to work with the database on the second sheet.
Click to expand...
That's simple enough to do. One can just nest the formulae inside one another. I had deliberately shown how the spreadsheet works step-by-step, but I can just combine the formulae to hide most of that.


zynizen said:
thanks! I could possibly send you what i already have and see if you can just modify it for me, but I like knowing how things are done.

thanks
Click to expand...
No problem. Have a look at this latest version, though, and then we can take it from there. The attachment is a version where you only see the company code and the address on the main page, and then the second page is the database as before. All the illustrative stages have been combined to give the result alone.
 

Attachments

  • Invoice_Address_new2.xls.zip
    3.1 KB · Views: 1
looks great! thanks! so onto the next question... anyway to grab this information if I create a file maker database? or create invoices using filemaker pro? perhaps having a excel sheet that grabs database information externally or is that just too hard to do?
 
zynizen said:
looks great! thanks!
Click to expand...
Excellent. :)

zynizen said:
so onto the next question... anyway to grab this information if I create a file maker database? or create invoices using filemaker pro? perhaps having a excel sheet that grabs database information externally or is that just too hard to do?
Click to expand...
Hmm, FileMaker is not something I've ever used, to be honest. From a quick search of the Help files within Excel, it says it can import FileMaker data. I presume, though, that you are talking about having Excel grab data on the fly, and not importing FileMaker documents on a regular basis (?).

After a quick Google search, I also found this PDF about using FileMaker Pro with Excel. Perhaps it is some use to you?
 
You must log in or register to reply here.
Back
Top