# Lookup Invoice Numbers From A Raw Data File With ~5,000 Line Items On A Daily Basis

Apr 16, 2009

I have a spreadsheet, in which I need to lookup invoice numbers from a raw data file with ~5,000 line items on a daily basis. The lookup is based on two criteria searches (1) search product type (2) search product make. In this example, I have 4 product types:

1 – car
2 – truck
3 – boat
4 – motorcycle

For this example I want to search invoices; (1) first search for cars only (2) search for product make. In my attached example, the first item (cell E2) would return invoice number 7147875-FRD from the raw data file. The second item (cell E3) would return invoice number 7147877-NSN.

## Distribute Work On Daily Basis

Jun 16, 2014

Attached a work tracker here for your ref.

I this, We are distributing work on 6 hrs/day basis. What I need is, I need to automate the work distribution on daily basis and should show the hrs also. and the projected date of completion. (yellow highlighted cells are where the formulas are needed)

How can we do that? Excel Help-Updated.xlsx

## Average Formula On Daily Basis

Feb 5, 2010

How to go about fixing my spreadsheet so I am not having to manually update it each day..here is my forumla I am currently using...=(AVERAGE(\$D\$2:\$AH\$2)-C5)*AI5...basically i need the cell below in D2 to change as every day a new day rolls off..for example the following day I need this formula to be =(AVERAGE(\$E\$2:\$AH\$2)-C5)*AI5 ....so just that day changes.....do I need to use an If/then statement? if so how?

## Automating A Macro To Run On A Daily Basis?

Dec 5, 2008

Is there any way of automating a macro to run on a daily basis?

## Categorized Daily Expense To Monthly Basis

Jan 8, 2010

While I was working my daily expense I come up with this issue. I do eat outside while I am on work. Sometimes I go to Pizza, sometime I go to mexican etc etc. The common between them is word FOOD. I would like to modify the formula suggested by Ron Coderre

=SUMPRODUCT((TEXT(\$A\$2:\$A\$14,"mmm")=\$E3)*(\$B\$2:\$B\$14=F\$2)*\$C\$2:\$C\$14)

See the attached file to get more idea of my question. Then I would Like to Highlight Entire Rows which contains a Specific text.

## Inserting Lines Automatically On A Daily Basis

Apr 14, 2009

In cell b5 I have =today() which automatically updates the date on a daily basis. What I would like to happen is for each day there is a new line is automatically inserted with the date so:

Sat26/04/2008gone off sickFri25/04/2008Thu24/04/2008Wed23/04/2008Tue22/04/2008Mon21/04/2008Sun20/04/2008Sat19/04/2008Fri18/04/2008days holidayThu17/04/2008Wed16/04/2008Tue15/04/2008

I would also like any information on each of the line to move down when the new line is inserted. I am not really that good with macros or VBA but feel that is the only option to solving this problem. Also would everything still update even if the file wasn't opened for lets say the weekend. So it is constantly rolling.

## Track / Calculate Overtime Hours - Daily Basis?

Sep 14, 2006

I'm creating an log to track the total number of hours an employee works in one day and calculate regular and overtime hours worked based on the following criteria: overtime will be >8 hours in one day and >40 hours in one week.

Right now I have it 'mostly' figured out, but under certain conditions, it calculates more than 8 hours in one day for straight time. Is there a way to set a maximum value for the straight time cell and have the difference be added to the overtime cell?

## Building Sheet To Track Delivery Times Of Wagons On Daily Basis?

Mar 23, 2013

I am trying to build a sheet to track deliveries into the company I work for.

We book loads to come in at set times for production, I am wanting a sheet which I can enter the due time and the actual time of the the different companies and then produce a report in graph format to see which are the worst at late or early deliveries.

## SUMIFS - Record Profit Or Loss On Daily Basis / Display Total For Last 7 Days

Dec 24, 2011

I have a spreadsheet to record profit or loss on a daily basis. The figure for each day can therefore be positive, negative or zero.

I want to add a column to display the total for the last 7 days (NOT the last 7 calendar days), in which either a profit or a loss was recorded (so excluding any cell that is zero).

I would prefer to add (insert), the column for each day as it comes and the range would obviously vary if the new day's figure was not zero.

## Get Data From Cells In External File - File Name Changes Daily

Aug 1, 2014

I have a file that gives some statistical data to my co-workers every 15 minutes. A common question I get is "How does that compare to last week?" Then I have to open the file from 7 days ago, find the data from the same time interval, and subtract it from this week's number in my head. I'd like to have excel do this for me.

I know how to get data from an external file. The problem is, these files are named with a date on the end of the file name. So tomorrow, the static formula won't work anymore (or rather, it will give data for a file from 8 days ago, instead of 7). I'd like to excel to use today's date, find the file from 7 days ago, and get the data from that file to compare to the current file.

Here's what I've done so far:

Code:
ThisDate = Range("C1").Value 'the cell with todays date in it
ThisDateName = Format\$(ThisDate, "yyyy-mm-dd") 'now formatted the way I need it
ThisDate7 = Range("G1").Value 'the cell with the date 7 days ago
ThisDateName7 = Format\$(ThisDate, "yyyy-mm-dd") 'formatted correctly
ThisDate14 = Range("G2").Value 'the cell with the date 14 days ago
ThisDateName14 = Format\$(ThisDate, "yyyy-mm-dd") 'you know, in case of a holiday 7 days ago

I have no clue what to do next. I want Excel to: Use "ThisDateName7" to find the file with the name "pph_tracker_[ThisDateName7].xlsm"Get data from a cell in that file (say, C15)Subtract it from the data in the same cell (C15) in today's file (this week - last week)Give me the result in today's file (say in cell C20)Do that again for cells D15, E15, and so on (result in D20, E20, etc)

I assume I can figure out the rest from there. Can I use the variable names in an actual formula in cell C20? Something like:

=C15 - '[pph_tracker_{ThisDateName7}.xlsm]Sheet1'!C15
or even
=C15 - '[pph_tracker_{The Date in Cell \$G\$1}.xlsm]Sheet1'!C15
which would require no macros at all!

## Read Pdf Or Doc File Line By Line And Then Parse Data Into Proper Cells And Rows?

Feb 23, 2013

I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.

## Can Cell Keep Cumulative Of Values Typed In Adjacent Cell On Daily Basis?

May 31, 2014

See the attached excel table. I need the cell E4 to keep adding the values typed in the cell D4 on daily basis.The cumulative shouldn't be changed if there is no value in the cell D4. E4 should accumulate and keep the totals typed in D4...

Cumulative Input.xlsxâ€Ž

## Combine Lab Daily Assay Data Into Single Easily Manageable File

Feb 13, 2014

How to Collate and combine lab daily assay data into a single easily manageable file.

Currently i have a spreadsheet for every days assay results. Now this should be combined into monthly and yearly data.

## Tracking Daily Total Sales And Individual Tender With Data Extracted From .dbf File.

May 23, 2006

I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated.

Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details.

I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly?

Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file?

attached is book.xls and sales summary.xls for reference.

## How To See Percentage Of Items Received Daily / Weekly / Monthly

Apr 20, 2013

I'll use following as an example.

I work for a supermarket, I receive many fruits per day and I want to see what percentage of them are bananas, apples, oranges, watermelons etc.

Are there any templates of pie charts and bar graphs that I can use for this that will show the percentage of said fruits daily, weekly and monthly?

## Lookup Process For My Address Part Of My Invoice

Apr 6, 2007

I'm trying to create a lookup process for my address part of my invoice, I want it so when I enter the first address it automatically looks up and enters the rest of the information (Title, Name etc.) I've been trying to do this with vlookup and I can't seem to get it to work. I'm just getting #Ref! returned.

My address 'database' (list of addresses etc) are on a seperate sheet to the Invoice.

## Invoice Numbers

Apr 12, 2009

Been doing a lot of searching with no luck, I think this is a very easy fix I hope.

## Basis SUM Macro: Adds All The Numbers In A Column

Nov 15, 2008

I would like to have a macro which adds all the numbers in a column. The problem is that sometimes there are less sometimes there are more numbers. What I would like is, that the macro to sum the numbers right under the last cell which contains a number.

## Lookup Column Of Data Match Criteria From Another Sheet Display Items Vertically

Feb 5, 2014

(File is attached here)

I am trying to work on Sheet 2(Details per person). I want to be able to display all items in a row that matches the 2 criteria (Skype ID and Date) and the items are based from Master Raw file which is in another sheet. I would like to just use index and match.

## Refresh Invoice Numbers

Apr 21, 2009

I have a invoice that I use, thanks to royUK is working pretty good now. I'm not sure if there is a code I can add too refresh my invoice counter. When I clear the page it moves up one number,now that I have two invoices in the same book it does not refresh the invoice number in the other invoice. royUk worked on this a lot yesterday and had some great ideas in cleaning it up. On my one day invoice he was able too give me a warning when I ran out of cells to input my marcos, but not sure how too do it in my two day because it has two pages. Their are so many helpful people here, I wish I could take them all too dinner.

## Incremental Invoice Numbers

Nov 30, 2009

Is there a way to create incremental invoice numbers via a formula in excel every time it opens up?

I would like it to appear in a specific box "e5" on the spread sheet I have read a lot of info but I am lost I have no idea what vb is or how about editing it.

## Creating A Yearly Database File Using Information In Daily File

Feb 7, 2008

I would like to automatically update a 'yearly' database file with info from a file that is changed on a daily basis.

The daily file that i use has info like date, truck number, delivery stops, weight.

the database file has the similar headings.

at the end of each day this daily file is saved. I would like to have the info that is entered into the daily file automatically plugged into the yearly database file into the next available group of cells with respect to the salesperson.

This is kind of a generalization but i'm hoping to just get pointed in the right direction. If something like this involves vba then it will be beyond my ability and i'll have to do it manually, which is fine

## Text To Numbers :: Invoice From A Web Page

Jun 11, 2007

I would like to be able to copy an invoice from a web page, paste it into Excel and then run a macro on it that will strip away all unwanted lines. That much I have gotten fine. Obviously, part of this invoice is numbers (Qty, Unit Cost, Total Cost, etc.) but when my macro is complete these numbers are all stored as text.

What steps would I need to add to convert these numbers to numbers? I would like for this to all happen within the macro. This completed spreadsheet would then be linked to a table in Access where calculations will need to be performed.

## Incrementing Invoice Numbers Using Macros

Feb 25, 2009

I currently have a project i am working on based on a invoice system and in my case it is to do with Van rentals. what i am looking to do is create a macro so that when the user clicks the order van button, it brings them to the order page and automatically creates an invoice number which is automatically added to the invoice database along with the customers details and the date of the invoice in a different sheet.

i have been browsing various forums and tutorial and have come across an a piece of code that is incrementing numbers each time the button is clicked. I can add the first set of details to the invoice database however i am unable to create a new row which copies the formulas and just change the invoice number as it is incremented - it just copies the same number

Now i am a complete novice in using VB and i have knowledge to an extent using excel. Is it possible to do that?

Sub test()
'
' test Macro
Sheets("Invoice List").Visible = True
ActiveWindow.SelectedSheets.Visible = False
Sheets("Customer Van Order Form").Visible = True
ActiveWindow.SmallScroll Down:=-12
Sheets("Invoice List").Select
ActiveCell.FormulaR1C1 = "='Customer Van Order Form'!R[7]C[6]:R[7]C[7]"
ActiveCell.FormulaR1C1 = "='Customer Van Order Form'!R[7]C[6]"
Range("B2").Select.............

## Getting Row Count From Other Excel File On Basis Of Multiple Columns

Jun 18, 2014

If I have 2 excel files. One with data about electronic equipment and one with more general counting data, i will explain..

For example the electronic equipment excel file contains data about notebooks, desktops, epads etcetera and also prices and how old they are etcetera. The excel file also has a column for serial number, so for example a notebook serial number looks like this: 23N34ERT3 and an epad SN looks like 25OKE445EE. IF i filter the SN on text and begins with: ??N then it will show me only notebook data(because the N stands for notebook). Same if I only want the Epad I just filter on ??OK, so i get all the epad data.

now for the general counting data file, in this file i actually want to put data which i get from the other electronic equipment file. For example i want this data to be retrieved:

a. total number of rows of notebooks from the electronic equipment file
b. total number of rows of epads from the electronic equipment file
c. how many rows there are for notebook that are 0 - 1 years old(in electronic equipment file there will be a column called product_Years so in this column you have data like: 1,4,12,3) + that are from model: A from the electronic equipment file
d.how many rows there are for notebook that are 2-3 years old + are from model:A from the electronic equipment file and then going on for 3-4 years model:SD etc......
e. in the electronic equipment file there are prices for each model, i also want to calculate the prices for each rows which i get here in the list above.. These rows must be calculated with prices from electronic equipment file

Is there any easier way than constant filtering and copy pasting the data?

## Reformatting Data - Subtotaling Brings Numbers Down To One Line

Apr 2, 2013

I have several thousands lines of data....much of the data is the same, 2-5 rows per person, but at the last two columns is different numbers/totals....I'd like to get those all into their own column so that each person has one row with all the data...I USUALLY would sit and sort by each total, shift them over to the right into their own columns, then sit and shift them all up to one line...but thats a carpal tunnel project and i know there must be an easier way....subtotalling brings the numbers down to one line once I have shifted each one over, but not the rest of the data....

Pic Attached: Excel problem pic.JPG

## Macros To Identify Duplicate Invoice Numbers

Aug 18, 2014

My boss gave me a project and need to be done as soon as possible. Basically, i just need to join all of database and then identify duplicates and put them in another sheet in order to investigate if there are any double payments.

Duplicates can be:

- invoices with same vendor name having same or comparable amounts
- invoices with Invoice No. which match or closely match (in case we're mis-keying)

I've tried so many ways to find duplicates that fulfill the 2 conditions above, but still cannot make it done.

## Print Invoice Numbers Numerically With Dash In Front?

Feb 21, 2014

I have a macro already that prints my blank invoices sequentially. What I would now like to do, is insert a dash. SO instead of just the invoice number, I would like to have a 1- in front of each number;

1-1
1-2
1-3
1-4 ... etc.

I have uploaded the 'invoice' that I am currently using.

## Add All Items Of Line Number One

Jan 4, 2008

My problem is I have a sheet thats structured like this:

Purchase # Item AMT
3630130685 10 20,503.04
3630130685 20 12,814.40

I need to add all items of a po to line#1.. is there a easy way to do this??

## Employee Schedule Lookup Time Value And Return Name For Daily Tasks

Oct 10, 2013

I'm trying to auto input names from my schedule into dailytasks for my servers. Sometimes the amount of servers on a specific day changes(i.e Mondays 3 to 5 servers and Friday - Sunday there is 4-6 servers on) and times could change also as of now it works with

=VLOOKUP("6:00",AC\$25:AJ\$46,8,FALSE)

Not sure how to set up lookup value and return the name