I have tried to put together three simple formulas for a very simple problem. I wanted to string together three vlookup formulas which were as follows;
The first lookup would work but as soon as I added another it would result in #n/a. I tried adding the suffix "false" after column no. but that did not work.
On the second lookup I did mix text and numbers e.g the first row in the column had a "G" in it and the rest were numbered 1-10. The second two vlookups were on a seperate sheet but in the same workbook.
I have checked the named ranges and they work fine when done individually but it seems to be the combining which messes things up.
I have an Excel 2003 situation I cannot resolve. In one workbook, I have a table of multiplication factors based on a numerical general ledger code. On another sheet, I have a data extract including that code and a cost. In the latter sheet, I need to multiply cost by the appropriate factor based on the GL code - IF Code=X, then cost*factor.
Should be simple, but I have too many codes for a nested IF statement. I also tried a VLOOKUP, but since the codes are numerical, Excel interprets them to be in the thousands, while my factors are 1.xxxxxxx, and since the data being looked up thereby appears smaller than the keys, VLOOKUP will only return #N/A or ?NAME, depending on whether I try to nest it in an IF or not. I also have several thousand records per month to which this needs to be applied, so going manual is not an option.
The calculating is done in column E, the original cost is in column AB, the key (GL code) is in column R, and the GL code range with factors is on the !Factors sheet (column A=Code, B=Description, C=factor).
i'm building a spreadsheet where there is a list where users will input work shifts (those shift can be, for example, "M", "N", "T", "Me", "Ne", "Te"). i use two different columns to retrieve (using vlookup) values for M, N, T and Me, Ne, Te shifts. those values are in two different tables (one table for M, N, T shifts and another one for Me, Ne, Te)
for the M, N, T shifts column i use: VLOOKUP(A1;$values.$A$1:$D$3;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2)))
for the Me, Ne, Te shifts column i have: IF(ISNA(VLOOKUP(A1;$values.$A$67:$D$69;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2));0));0;VLOOKUP(A1;$values.$A$67:$D$69;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2));0))
this seems to works fine, but only for one shift per day. the problem is that workers may work more than one shift per day. is there a simple way of, in one column, vlooking up the values for every M, N, T shift, every day ignoring any Me, Ne, Te and the opposite in the other column?
I have a spreadsheet with two tables in it. Table 1 contains some data consisting of two columns both populated with data (where 1 = a, 2 = b, 3 = c and so on) and table 2 consists of two columns one column contains data and initially (numbers) column two is blank. Now I need a VLOOKUP that will look at the second column (red letters in the example) in table one, and where the same value (number) appears in table 2 populate the corresponding letter. I do not wish to use filters as the table is extremely large. I have attached an example
I have a few tables on a worksheet, and to search these tables i am using a VLOOKUP, however i want to be able to search for other than just the one field! but still pull up all of the data. If i were to do the same tables in access i would be able to query all fields and then recieve an output of those that match, is there any way to do this in excel
Note I am only into basic VB. Other question i have is, on a VLOOKUP you only return the one record, is there anyway of searching a table of data and returning all records that hold that value in a specific field, like applying a filter to them. or am i just being extrememly dumb and missing something simple?
Okay, so I have this project for my accounting computer applications class. I'm making it so much arder on myself than what it probably should be. But anyways, this project is a four year plan spreadsheet for the business/accounting division of the school. Inside the spreadsheet is a page that has 15 different tables, representing 5 years with 3 semesters each, fall spring and summer. On the requirements for the major chosen, I would like it to automatically recognize if the class has been placed in the semesters tables and recognize all of the information needed. The only problem is, a single vlookup only allows one table per cell but I need all 15.
I've heard of using the if statement along with these but I've only heard of it being done with a few tables and not 15, and I don't understand how exactly that works anyways.
I am wondering if there is a way of performing Vlookup across several tables? I have tried to create a formula myself based on lookup and nested vlookups, but am getting all tanlged up, especially as I need an If isna formula in there so that if there isn't a result, it displays a 0 rather than a #N/A.
See the attached. I do compeltely understand that it would be so much easier if all the data on the commission worksheet was in a simple 3 column table, but it has to be split as it is creating 3 tables. exactly what formula needs to go in cell D7 on the rates worksheet that I can drag down, which will check for those initials across all 3 tables? (The initials will only appear once on the table on the commission worksheet.
I have a spreadsheet that records the date, specific sporting activities and cash award for participation in a round robin sporting event that was held over 52 weekends. I'm trying to lookup the cash award value of each event the person participated in by using a date range within the vlookup formula.
Each participant has their own spreadsheet which they are able to access via server, and each spreadsheet looks a bit like this:
-Column A contains the date of an event in which the person participated -Column B contains a category number related to the type of event eg marathon = 12, hurdles = 15, relay = 18 etc. This info is manual input -Column C contains the amount the person will be awarded by simply participating in the event. This is a lookup value and varies depending on the date the person participated, so I'm trying to incorporate date ranges and 'if' formulas into the vlookup
I'm from Australia so my dates might be backwards...
Spreadsheet:
A B C 1 02/01/13 15 (supposed to be $10)
The formula should read a bit like this:
if (date at A1 is within date range 1st Jan 2013 and 31st March 2013, lookup B1 in table 1 column 2), if (date at A1 is within 1st Apr 2013 and 30th Jun 2013, lookup B1 in table 2 column 2)...etc for each quarter
i want to compare between these two table based on the condition writen in RED. IF there is a mismatch i want to highlight the values in the first table.
I am tired of doing the same amount of repetitive work, I would like to know if it is possible to acheive a "template" where I just drop a database pull and it negotiates where the data should fall.
When I pull data, I have the following values I need -
Part number Date - which is by day, I need grouped by Monday - Sunday to seperate amounts ordered by week. Branch plant - East and West Order Quanity which is placed by day QOH - Quanity of parts I have on hand
I need the days grouped by weeks, 7 days by date equal 1 week. I need summing part number amounts due within that 7 days into one total.
I would need 4 columns 1) Demand due 2) Supply due 3) QOH 4)Difference
I need each branch to have their own sheet, for a total of 4 sheets... Supply and Demand for both plants.
What it boils down to is I need to know what the demand is per week, and the supply I have to meet that demand, broken down like the attached sheet.
Seems my sheet is too large due to the amount of data for the site, but here is the link to the LARGE File...
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code: Sub Auto_Open() ' Dim sht As Worksheet If Range("A1") = 1 Then
I have been trying to multiply cells(6,3) of sheet 'Input2' with the 23rd row of next sheet 'Input' from 4th column in that row to last used column. Then I want this looping to go from 23rd row to the last used row in that sheet. But I am not successful. The code that I tried till now is:
Sub test() Sheets("Input").Activate x = Range("D23").End(xlToRight).Select For j = 4 To x m = Sheets("Input2").Cells(6, 3) * Sheets("Input").Cells(23, j) Sheets("worksheet").Activate Sheets("worksheet").Cells(j, 17) = m Next j End Sub
I have a total number of hours I want to subtract from. Example in cell e3 I will have 800 hours. In cell f3 I have the number 2 (to represent 2 workers at 40 hours, so that should total 80). I then want f3 to multiply by 40, then that total subtract from the 800 hours in cell e3. Basically I want to be able to put 2 workers in a cell, have that multiply by 40 and minus from the 800 hours in cell e3. I want that to carry on for cell g3, etc.
Basically I have 1 value on 1 sheet and and multiple values on another sheet and I would like to know if it is possoible to mutliply these togther i.e
Sheet 1 100
Sheet 2 2,5,9,15,20....
I would like to calculate (100*2)+(100*5)+(100*9)+(100*15)+(100*20).....
I know if sheet 2 only had a few numbers like above which I have used as an example I could link the sheets; however in my data, sheet 2 is about 75 numbers that change.
This is about my misformatted download again. It considers all the numbers to be text. Naturally, I need them to be numbers. I discovered a page on the Web that says if you multiply all the numbers by 1, suddenly Excel gets a clue and figures out they are numbers. I've tried this (as the page recommended) by using Paste Special, and it works. I know the command I need to use to get it to Paste Special:
I need to multiply the values of 10 rows by the values in 10 columns and sum then, akin to the sumproduct function, which only seems to work if the values are all in rows or all in columns.
In other words;
rows of 1 2 3.......10 & columns of
a b c . . j
Expressed as (1*a) + (2*b) + (3*c) + ....... (10*j)
I can do it long hand and I can do it by transposing the data as an intermediate step, but I was looking to do it all in one formula.
I want to merge some cells using a criteria. To be more specific, let's speak on the file attached.
Beginning with the P5 cell I want to merge to the right groups of 4 cells for each name from the column J. Therefore the first group of merged cells will be P5:S5, the next group will be T5:W5 and so on until reaches 11 groups of 4 cells. The last group will be BD5:BG5.
I tried a script like this but it says that there is a syntax error on the "Else" instruction:
However, when I dont have a value/number in A or C I get a #VALUE in the total cell. It will only calculate properly when I have numbers in all 4 cells.
Is there a way to set it up so it will calculate if cells A or C are empty? I dont want to have a zero in these cells - just leave empty if no value.
Also - when the total cell is empty I want it blank.
I am creating a spreadsheet for some deliveries. The pieces I have are all in feet and inches and multiplied by a certain quantity. I need a formula to run this and give me feet as an answer.
I have 4 cells, in A1 I have feet without the foot mark (20) and in cell B1 I have inches without the " (5). In cell C1 I have 20. How do I write a formula to do the following:
I have over 500 countries with unique shipping costs associated with each... each of them has the same mark-up price... 2.25. I am hoping to be able to insert a formula that will cover each country... i am currently entering each one indivdually (E10*2.25), then the next one (E11*2.25), etc.
I would like to map each item in the row with the column header and the price for that item for the customer, similar to the below one, go through both the work sheets, i want the output in the rowitem view.
I have to complete the following project in visual basic for Excel
direction quantity price b 400 10 b 100 20 s 900 15
So the above is a small sample space of what i have(over 3000 rows in total) they are stocks. if there is a "b" in the direction that denotes a negative (short) and if there is an "s" (long) that denotes positive.
Two parts: 1) Write a traditional macro (with button) that when pressed, will give the total Net value in a message box. 2) Secondly, write the code in 'class module' that give me the over all net value in a message box.
I understand what I need to do but just don't know how to write the code for this in visual basic.
So for the above example the net would be -400(10) -100(20) + 900(15) = +$7500
But how can I write a program to do this and display the final answer in a message box?
I need a formula for H2 to multiply the value if it is in any one of the cells in B2 - B6 by the appropriate value to calculate the annual total. Such as if the number is in weekly column it would multiply by 52, fortnightly 26, monthly 12, quarterly 4 and annually 1.
Weekly Fortnightly Monthly Quarterly Annually Ann Totals Mortgage 1000.00 Council Tax/Water/Sewerage 1000.00
I'd like automate consists of entering 56 numbers (up to 3 digits, 000-999) and finding the difference between today's numbers and yesterday's numbers. Then multiplying each difference by a set number (i.e. 1, 2, 3, 5, 10, 20, or 25). These numbers may, from time to time change (i.e. the 32nd entered number may be multiplied by 3 for 2 months, then we might swap out a product and then need to multiply by 5, etc). At the end, I need all 56 differences multiplied by their individual respective multipliers totaled. This needs to be done daily using previous days numbers and be able to change the multiplier without effective previous data.
I was trying to to this myself by Column 1 = multipliers, Column 2 = yesterdays' numbers and Column 3 = today's numbers. The calcualtions are done by excel and a total is summed. This I can do. However, each day i'd need to make a new file and enter yesterday's and today's numbers. This is time consuming. I'd like a system where I can just entere today's numbers and any changes to the multiplier and it will spit out the total.
If anyone can guide me or point me in any direction to accomplish this automation. I would greatly appreciate it. I'm pretty computer savvy, but just have not had the time to dwell into Excel.
For those interested in what this is for: I have a small business that sells instant lottery tickets. Each day we have to figure out the number of tickets sold the previous day. We have a notebook and manually write down numbers, do the substractions by a calculator and total. The table in the notebook is seperated by ticket values (i.e. $1 tickets, $5 tickets, etc). However, certain tickets run out or occasionally we need to move tickets to a different spot, thus the MULTIPLIER in the automated system would need to change.
I am trying to put together a basic sheet with what I think is basic math, however, the multiplication does not seem to be working properly.
Field 1 is land value, field 2 is total acres. The math I have in field 3 is to give me a value per acre. Here is that formula: =Sum(B3/C3) I have set the field to a number field with two decimal places.
Field 4 is Total Dry Acres field 4 is calculated to give total value for dry acres (here is where I am having problems) Here is the formula: =Sum(D3*E3)
Here are the numbers: Field 1 = 218702 Field 2: 1536.51 Field 3 (calculated): 142.34 Field 4: 1171.75 Field 5: 166791.54
It all looks good on the spreadsheet, however the field 5 number is not correct.
Break it out on a calculator: 218702 / 1536.51 = 142.34 (rounded) Now 142.34 * 1171.81 = 166795.44
so working on the calculator, there is a difference of 3.69 between the two.
I have a spreadsheet of measurements which i need to convert. In short, all of the numbers must be multiplied by 2.54. I can follow how to do 1 number at a time, but the sheer number of calculations makes this highly inefficient. I think my problem lies in settng up the formula properly. I am working with an iBookG4 with an excel program 11.2.3.
