Tech Support Guy banner
Status
Not open for further replies.
1 - 20 of 32 Posts

·
Registered
Joined
·
19 Posts
Discussion Starter · #1 ·
I run excell 2008 for mac and need assistance with an formula I cannot get right.
If say cell G9 on an invoice sheet ( which is a month like Jan, Feb...) = the corresponding months in say cells E35 through E46 (which are months Jan through Dec on a Revenue tracking sheet) Then put the dollar amount from say cell F24 from the invoice sheet to the Revenue tracking sheet in say cell F35 through F46 (which is next to the months in cells E35 through E46 to correspond with the correct months.
Also add any other invoice totals with the matching months to the correct cells on the Revenue tracking sheet to keep a running tally. I don't know if this is even possible, any help would be greatly appreciated.
 

·
Registered
Joined
·
402 Posts
I'm no expert only a previous dabbler in Excel.
It sounds like you have 2 seperate pages and want to synchronize them. Which can be done but I don't know how. You might even need a macro.
If you are only using one page, it's a lot easier.
I understand you don't want to give your customer a revenue report with invoice.
Maybe the help files have info about synchronizing.
Sorry I can't be more help but got you bumped up.
 

·
Registered
Joined
·
402 Posts
Out of curiosity, (a)is this for keeping track of invoices you give when someone makes a purchase from your company or (b)is this for keeping track of your expenses for your company?

If it's (b) it can be done on one page very easily.
 

·
Registered
Joined
·
19 Posts
Discussion Starter · #4 ·
I have one invoice sheet that i made a template out of. Buy using that same invoice template i would like to have some info put in my Revenue tracking sheet if the conditions of the dates match. If the date input on the invoice sheet equals one of the 12 months I have on the Revenue tracking sheet then put the quantity and total price paid next to the correct date. So ya 2 diff sheets.
I will see if i can find something on sync. I have never tried it. But on the Revenue tracking sheet i can track how much is made each quarter of the year such as product quantity and total Revenue also total tax taken in. But right now i have to put the info from the invoice template onto another sheet manually. That sucks. I hope i have explained this properly, If not i can try to explain another way. But WOW thanks for your help.
 

·
Registered
Joined
·
19 Posts
Discussion Starter · #5 ·
pcatrocity: Yes yes for both. The purchases are not from a store front. I will be on the road and will have a small printer and computer to fill out the invoice and print off. I would like that info to automatically get added to my Revenue tracking sheet. Then i can see at a glance how things are going.
 

·
Registered
Joined
·
402 Posts
I'll see if I can work anything out in MS Works 9 Spreadsheet but no promises as it is limited compared to Excel.

If you do a lot of copy/paste you may like this little clipboard that allows you to copy items before pasting them to a document or storing them on 1 of 20 pages.
You can do individual pastes or bulk paste. Thats bulk paste straight down in Wordpad, Notepad or similiar. OR Bulk paste in cells going down or going across in spreadsheets.
I've loved it for 2 yrs now. Pages 18-20 retain information I use frequently.
http://www.m8software.com/clipboards/freeclip/freeclip.htm
 

·
Registered
Joined
·
19 Posts
Discussion Starter · #8 ·
Propane Revenue Tracking Services Last Modified: 21-Feb-12 2012 - Overall Created 2/20 Gal. Sold Addtl. Equip. Totals Average Q1 60.50 $0.00 $423.50 $211.75 Q2 57.10 $0.00 $399.70 $199.85 Q3 61.60 $0.00 $431.20 $215.60 Q4 57.60 $0.00 $403.20 $201.60 Total 236.80 $0.00 $1,657.60 $828.80 Target 200.00 $50.00 $1,600.00 Difference 36.80 -$50.00 $57.60 Month Gal. sold Addtl. Equip. Totals January 20.00 $0.00 $140.00 February 21.10 $0.00 $147.70 March 19.40 $0.00 $135.80 April 19.70 $0.00 $137.90 May 20.70 $0.00 $144.90 June 16.70 $0.00 $116.90 July 20.20 $0.00 $141.40 August 20.20 $0.00 $141.40 September 21.20 $0.00 $148.40 October 19.60 $0.00 $137.20 November 19.00 $0.00 $133.00 December 19.00 $0.00 $133.00 Quantity Totals Jan Feb Mar Total Tax Apr Q1 $12.71 May Q2 $14.03 Jun Q3 $15.18 Jul Q4 $14.20 Aug Total $56.11 Sep Oct Nov Dec
 

·
Registered
Joined
·
402 Posts

·
Registered
Joined
·
19 Posts
Discussion Starter · #19 ·
Not going to work. Lets try this, IF G9 (the date, on invoice) = (one of the 12 months in cells) E35 to E46 (from the Revenue tracking sheet) then put the Quantity's in one of the cells from F35 to F46 and put the totals in one of the cells from G35 to G46.
Also every time i make an invoice ADD up the totals on my chart for revenue tracking ( the part in the orange color)
 

·
Registered
Joined
·
402 Posts
I'm not having luck here. Only have MS Works Spreadsheet and that's like a teaser for Excel. Only some features. I've been looking for good Excel groups & tutorials. Which version of Excel do you have? 2010.
D24 D21
You will need an Excel guru for sure. Because
1. I think you will be having many 'if' arguments in the formula for adding info from inventory page to revenue page. They may even need to be seperate workbooks.
ex: if page 1 G9 is Jan add page 1 C24 to page 2 C21 if page 1 G9 is Jan add page 1 D24 to page 2 D21

2. you'll need a formula, macro or something to prevent the totals on page 2 from resetting to 0 every time you begin a new invoice.

3. when you try setting simple formula using the same cell you are in, ex: your formula for page 2 cell C21 would be something like this, if page 1 G9 is Jan add page 1 C24 to page 2 C21 then you'll get this popout meaning cell C21 can't be part of equation. More simpler, for cell A1 we can't use =A1:A6
 

Attachments

1 - 20 of 32 Posts
Status
Not open for further replies.
Top