Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Total vlookup value Results

I have a formula that calculates a revenue per day by taking total revenue devided by total days, from that I have another cell doing a VLOOKUP on that to find what tier that value falls under almost like a grade book, however I need the division to be rounded up if greater than x.xx5 because when I do the VLOOKUP sometimes it drops the product to the lower tier when I needed it rounded up. example:

=IF(ISERROR(Data!H6/Data!E6),"",(Data!H6/Data!E6))

if the value of those were to actually equal 1.256 I want to be able to have the VLOOKUP return the tier for 1.26 not 1.25

Hello...

I am so happy to see that there is such a great forum here for excel. I don't know anything about excel and I need some help here. Hope to find a great person to help me to modify the code.

I wanna have a Vlookup Macro function in my spreadsheet. I perform a vlookup in the spreadsheet by using vlookup formula and I found out that it is waste of time to retrieve records from other file when I have a total of 55000 records in one spreadsheet. I plan to use macro functions to retrieve records instead of using vlookup formula. So is there any chance that I can create the macro based on my scenario? {i already attached my spreadsheet here, it is a dump data}

In the sample1.xls, I want to retrieve the records from the sample2.xls by using Material as the key field. When I run the Vlookup Macro, the system is able to help me lookup the value such as (Net Sales: January until Net Sales: March) and display the records in the sample1.xls based on the Material Key Field. So hope that my problems can be solve here. Thanks!

Hi All,

Got a slight problem, which I'm sure is easy (hopefully).

What I need is to find a max value within a range and then tell me what the row value in Column A is. Usually use VLOOKUP for this but this doesn't like minus numbers.

I think it has something to do with Match and Offset but can't get it to work properly.

I have attached an example, where the max of the total is 4,435 and belongs to Steve but how do I get this to do using formulas

Thank you very much!

Orangebee

hi all,

need some help. i'm trying to return a value thats offset but the position of the value may change each time data is imported. i've attached sheet displaying problem, as i can't explain clearly.

i need to lookup the value in column A (yellow), then find and return the total percentage value in column C (blue). its always 2 columns across but the problem is it could be anything from 2 to 5 rows down.

could anyone help me with a formula or is there a way excel can sort the info, so total percentage figure is next to the number in column A and i can simply use Vlookup.

thanks

I know there are a few vlookup post already but I cant get them to work with the data that I have, I usaly get a Value error or a NA error.

Basicly what I have is a spreed sheet with 3 tabs, Weekend (sat sun after 8pm befor 5 am) tab list a tech that is oncall for a specific area afterhours. Weekday tab for the tech that is oncall during the weekday, and a Contact tab that list there names and numbers.

What Im trying to do is have a lookup that first finds the correct date(what ever todays date and time happens to be when the look up is initated) then looks up the right tech for the area. Attached is an example workbook.

oh and on the weekday tab the A=place code B=tech name and C=region
total there is over 100 place codes with 6 regions... but i should be able to modify the formual if i can get it to work with just this little bit..

Thanks in advance for your help!

(I hope this conforms to all the rules)

Hi All,

A little help on this would be great:-

I have a vlookup that searches through a named range to find a account number and returns the value of an account payment.

The lookup works fine until a two or more payments have been made to the same account. Then the lookup only return the first value in the range.

Is it possible for the lookup to SUM all the payments and return the total for that account number?

Any ideas would be of great help!

Cheers

J

Hi all,

I have searched the site and not found an answer that I can apply to my issue.

I have created a workbook with the help of ascalese, and now I would like to get the total of items ordered and put them in a total worksheet.

Right now, when an ordered is entered, I create a copy of the worksheet between to sheets (start name "Orders_Start" and end name "Orders_End").

I am trying to do a SUMIF on all items ordered. Several items are always in a cell like A19 - A24, and then total them in my total sheet.

I tried the following:


	VB:
	
0",Orders_Start!A19:Orders_End!A19) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And I get "#Value!"

I have several cells that are standard items, so these will be straight forward to total.

Then next 20 cells below them are Vlookup items from a validation list and I am not sure how to get all these items and add them to my total list.

Once I do, I can do another SUMIF on this list and get a final total.

I hope I am being clear enough on this.

Thanks as always,

Don

I have two sheets with related, but different, information. What I would like to do is to first check if cell value B2 in Workbook 1 is greater than zero (easy enough). If it is, then I want to lookup and display all rows in Workbook 2 whose index value in A2 match that of Workbook 1 cell A2. Then, proceed to check the rest of the sheet...B3, B4, B5, etc...and display the resulting data for each of those as well.

Does that make sense?

I have attached a generic example workbook to better describe what I need. Please note that although "Workbook1" and "Workbook2" are on different sheets, in reality they are totally separate workbooks.

I assume this will have to be done in VBA, as standard Excel functions (such as vlookup) will only return the first row of data it finds. However, if you know something I don't, and this CAN be done with standard Excel functions, please let me know.

Thanks in advance for your assistance!

OK,
I can normally crank out single sheets with little trouble, however I find myself confused on this one, here is my scenario:
I have to give a daily report based on sales figures from the previous day, and at the end of the month, be able to pinpoint where the sales were up/down. There are 15 different sales figures for each day, that are totalled from 100 different salespeople. I want to be able to create 1 single sheet that will lookup the header value and give me back the total of that item daily, for each day of the month.
Here is what I have so far:
MATCH(A2,'[Daily Sales Report August 1.xls]Main'!$A$1:$CE$1,0)
Where A2 is the item to look for, this at least gives me what column to find the data in but everytime I attempt to add an HLOOKUP or VLOOKUP to it to get the totals, I get an N/A error.

Anyone care to shed some light on this for me?

G'morning OzGrid!

I'm having troubles creating a formula to find a particular text value in a range of cells. The range varies in length and location, based on the value in cell P16, and the value I'm looking for is specified in cell A3. I'm trying to use this for conditional formatting: if the value in A3 appears in a list of cells elsewhere, I want to turn A3 blue.

I'm familiar with HLOOKUP (Well, VLOOKUP really, but same principle), but since any given element I'm looking for may be in a different position in the list depending on which list I'm searching, I'm not sure how that would work.

I'm grateful for your input. This forum totally rocks!

-Wulfie

Hi,

I have two different excels say EXCEL 1, EXCEL 2. In each of these excel sheets I would be receiving the total number of pieces that were sold along with the name of the item. For Example in the EXCEL 1, I would be receiving the data in the below format.
COLUMN A COLUMN B
PEN 100 BOOK 220 PEN CIL 150 PEN CIL 200 PEN DRIVE 150 PAD 200 PAD 200
In the similar fashion I would be getting the records processed for the EXCEL 2 . I need to add the values of all the similar type of things (EXAMPLE : PEN,PENDRIVE,ETC) and copy the values into another spreadsheet say EXCEL 3 which would be of the below format.

DATE PEN BOOK PEN CIL PEN DRIVE PAD 1st DEC 100 220 350 150 400 Using formula 600 220 350 150 400 It’s a ongoing process and I should manually enter the values in it on a daily basis. So I thought of doing it using EXCEL Functions and I have taken two sheets say EXCEL A and EXCEL B and the EXCEL B would be in the similar format has EXCEL 3. Then I would copy all the records from the EXCEL 1 and EXCEL 2 into the EXCEL A and I have used the VLOOKUP Function and DSUMFUCTION so that the values get populated into the EXCEL B.

The formula that I have used in the EXCEL B Cell is IF((VLOOKUP(A4,[EXCELA.xls]Sheet1'!$A:$B,2,0))"",(DSUM(' [EXCELA.xls]Sheet1'!$A1:$B200,2,A3:A4)),""). I am populating the things correctly but the problem is that the above formula would be similar kind of item in Column A i.e Using the above formula if it is looking for PEN in the column A then the items which are similar to that of PEN like PENCIL, PEN DRIVE values are also getting added and the added value is being displayed in the output file. The output for the PEN would be 100+150+200+150 = 600. I want to modify the above formula so that only the exact match can be searched for and corresponding value gets populated. Please help me out to find the solution for thisJJJ. Thanks in advance……….

PS : I have also tried using the EXACT, SUMIF Functions also.

I have a workbook with 2 sheets

Sheet 1 = Machine Sep

Sheet 2 = Attendance Sep

I need a way to tally the time-in of the operator (from attendance sep) with the corresponding time worked on machine (in Machine Sep)

Sheet 1
Column B = Dates
Column C = Start (start time of particular work)
Column D = End (end time of particular work)
Column E = Total Time (total time = end time - start time)
Column F = operator Name (name of the operator)
Column G = (the time-in of operator according to time-sheet formula =VLOOKUP(B3, Data, MATCH(F3, 'Attendance Sep'!$A$2:$BJ$2, 0), FALSE)
Column H = (the time-out of operator according to time-sheet formula =?????

I am unable to find a formula for Column H timeout to be returned from time-sheet, because the name (lookup value in time sheet is formatted to centre across c selection without merging-using asap utilities) so the lookup value (name of the operator is the heading only for time-in) there is no lookup value over timeout

How do i return value for timeout

Good morning,
Could you help me with the following problem ?
In cell A2 of sheet "TOTALS" I want to add up the results of a vlookup function across multiple worksheets.
Thus A2 = vlookup(A1;sheetB!$C:$H;3;false)+
vlookup(A1;sheetC!$C:$H;3;false) + vlookup(A1;sheetD!$C:$H;3;false)
+....... + vlookup(A1;sheetX!$C:$H;3;false).
X is variable, meaning that sheets may be added or deleted.
Note that the arguments remain constant except for the sheets where the values are to be looked up.
I don't think this can be handled with any combination of worksheet
functions. Is that right ?
Can anyone help me out here with the most efficient code ?

Thank you very much in advance.
Herman

I'm a NOOB and just learning here so please don't hurt me too bad!

I am working on a class project and I am having issues with the nested IF command and VLOOKUP command. See the attached example... A customer buys two (A6) of item one (B6) the rest of the fields will propagate from my "PARTS" table... BUT everyday a new item will be discounted 10%... The item # goes in the table named "Sale" at cell J24, the discounted savings needs to show in column F and adjust the price in column E.

If a nested IF command is limited to 7 as I have been told, how would you make it apply to 26 rows? I am so totally confused here.

bulldog89801

Hi,

The purpose of the attached spreadsheet is to record whether or not my company has stock of each item, on every day.

It currently works by doing a VLOOKUP in each cell if the date matches todays date which is generated by the formula on the TOTALS tab, cell I2. The current days stock levels are refreshed by a database query on the DATA tab and then totaled up on the TOTALS tab. The VLOOKUP formula returns the stock level and if we are out of stock then we are manually formatting the relevant cell on the REPORT tab red.

It has been suggested to me that there might be a way to do this via conditional formatting although I cannot work out how as once the current day changes to tomorrow then the formula in the previous cell no longer returns a value and the conditional formatting would return teh cell colour to white.

Is it possible to automate this process using VBA so that whenever the sheet is opened (daily) then it will copy and paste special the relevant stock level for that day against the correct product ? This way the value in the cell for that day would become permanent and the conditional formatting would stick.

Thanks

Hi all,

I am trying to create a run balance sheet (see attached sheet).

Column C has a list of job numbers, with column D showing the reqd quantities.
Once the job is run, the qty is entered in column F and either a balance or the word complete returns in column H.
My problem is, is that, when a job with a balance attached to it is re-run, that balance should be returned in Column D.

Ie 574361 has a total of 707 of which 320.4 was run, leaving a balance of 386.6.
When I type in 574361 again, I need column D (in the cell next to the job number)to automatically locate the last reported balance and return its value.
I have tried VLOOKUP and INDEX, but cannot get the thing to return.

Any help would be greatly appreciated

In advance
Thanks
Cheers
Jeff

Hi all,

Trying to make a spreadhseet that can keep track of the number of certain
welds done by particular staff in the company i work for.

WHen new welders are added, currently the QA inserts a number of rows and pastes copied cells in these rows, and it messes the total up down the bottom.

I was thinking of a vlookup but it just finds the first instance of the type of weld and that's it... I'll attach a sample of the spreadsheet so you can have a visual of what they want to do...

There will eventually be a list of around 100 employees.... The setup of the spreadsheet is probably not the greatest, but that's what i was handed to work with today :/

Not sure on using relative references and so forth, what would i need, to do what i need to do?

If you need more info or I haven't explained myself well, I'll answer any questions asap! :D

Mike

I have a template MS Word Document that has approximately 65 embedded Excel worksheets. Each worksheet performs a VLOOKUP to evaluate specific conditions based on a risk and threat matrix. Each worksheet is a unique sheet that is relative only to the proceeding paragraph. The last visible field calculates a composite score based on the average of all VLOOKUP results.

I would like to be able to have a final worksheet at the end of the document that calculates the total number of each grading criteria as a summary. I am having great difficulty in referencing values from one embedded worksheet to another.

Specifically – If an embedded worksheet contains a cell value, how is it possible to reference that cell value in another embedded worksheet in the same document?

I had hoped not to have a Word Document and an Excel document, but simply a Word document. This is because this report will go through multiple “Hands” during its critique and revision. I need the functionality of sending only a single “All inclusive document”

If I have chosen an improper method to accomplish this process, please inform me of that as well.

Many thanks,

Jared

Hi all, long time troller, first time poster.

First up im totally inept when it comes to the VBA side of Excel, and after spending many hours trolling through here to find a solution to my problem (and finding many but not fully grasping what is needed to be done) i am now posting. Please if this has already been covered (in laymens terms) and i have missed it, a link to where it is located would be great. However, the problem at hand ....

******** ******************** ************************************************************************>Microsoft Excel - Workout file.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH16=
ABCDEFGH1RegionTownKeyInfo1Info2Info3Info4Info52State1Town11aabacadaea3State1Town22abbbcbdbeb4State1Town33acbcccdcec5State2Town14adbdcddded6State2Town25aebecedefe7State3Town16afbfcfdfef8State3Town27agbgcgdgeg9State3Town38ahbhchdheh10State3Town49aibicidiei11********12*Info1*Info2**Output*13********14*Info3*Info4****15********16**Info5*****Workout*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The above worksheet contains specific information about several towns. I want to display that information in the yellow boxes, based on choosing the region from a listbox1, and then choosing the town from listbox2, based on the selection in listbox1.

This table is a VERY cut-down version of what i am working with currently, the actual table is roughly 80 rows, and around 90 columns. Currently I am having users enter a 'Key' value in a cell and then using vlookups to display results in appropriate yellow boxes. This is the only way i can think to display this information, i'm familiar but not proficient with arrays, but if there is a more efficient method to achieve the same results, i am definately open to suggestions, bearing in mind i have no exp with VBA

Any assistance you can offer would be hugely appreciated at this point.

I am trying to use the VLOOKUP feature to calculate a total column. Here is roughly what i have:

A B C D E F G
- 1 2 3 4 5 6
a 2 3 4 5 6 7
a 3 4 5 6 7 8
a 4 5 6 7 8 9
- 5 6 7 8 9 10

a

basically i want to add up a column (eg E) only when the value has an "a" beside it. Can you please help me with the formula please?


No luck finding an answer? You could always try Google.