Return Results Based On Referenced Cell
Nov 21, 2006
I am looking at developing a table that has three possible options for different categories, being Yes, No and N/A. A yes score a certain percentage depending on what column it is, and the use of N/A changes the percentage of other scores. (i.e. A yes in column 5.1 = a score of 5, a yes in 5.2 = 5, a yes in 5.3 = 10 and a yes in 5.4 = 15. However if 5.4 is N/A then this amount is diveded amongst the remaining 3, i.e. 5.1 = 10, etc.)
View 3 Replies
ADVERTISEMENT
Feb 1, 2012
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
View 3 Replies
View Related
Oct 6, 2008
I am attempting to create a formula that will, return text results based on 2 criterion.
This is what my sheet looks like
Name Job Full Time / Partime
Bob Admin Full Time
Barry Accounts Part Time
Chris Claims Full Time
Emma Claims Full Time
The formula I have for this is {=INDEX(A1:A4,MATCH(1,(((B1:B4)="Claims")*((C1:C4)="Full Time")),0))}
Which is fine and returns the name Chris, however as you can see from above, Emma also meets the criteria given however, I cannot get Emma to appear also. Is it possible to display more than one entry in a cell/s based on this formula, or am I missing something.
I would use a pivot table to do this, however I need the sheet to be completely automated.
View 8 Replies
View Related
Apr 19, 2012
I have a report that shows when our company has shipments covered by inventory (although you are not seeing the conditional formatting in the table). Columns K:Q represent shipment dates and shipment amounts. Columns D:I represent inventory with Column J being the total inventory. The conditional formatting works to highlight the cells in K:O of when shipments are covered by inventory. I need to be able to return the date (Columns K:Q) for an item (Column B) when a shipment is not covered by inventory.
For example Row 154: all shipments are covered by the amount of inventory. A shipment of 3 on 04/24 is more than covered the 105 pieces of inventory. But for Row 170, the shipment for 480 pieces on 04/23/12 does not have enough inventory to cover.
The result I need to return for row 170 is that 480 pieces are due on 04/23/12 that are not covered.
What formula would I used to return that value?
Excel 2010ABCDEFGHIJKLMNOPQ1LineITEMNEXT CUSTOMER PART WIP INVENTORYSTAGINGFINISHEDINSPECTDCYTRAILERTOTALPast4/18/20124/19/20124/20/20124/23/20124/24/20124/25/2012154Tabber105290325AA05290325AA-DC000010501053155Tabber1070025AC-EXP070025AC-
[Code]....
View 2 Replies
View Related
Jul 5, 2013
1) I have an invoice form, and need to create a formula that will allow me to calculate a discount IF an item number begins with "C" or "CE".
Example: Item # is in cell F12. (may or may not begin with "C" or "CE")
Item price is in cell J12.
Extended price (qty x price) is in cell K12.
In L12, I need to calculate a discount (from % in fixed cell L9) on the figure in K12 - based on whether or not the Item # in F12 begins with a C or CE.
If it does not begin with C or CE, then L12 needs to equal 100% of K12.
(The 2 parameters I referred to in the title were:
C, followed by a number
CE followed by a number )
2) The biggest part of the dilemma is that the person using this spreadsheet is totally unfamiliar with Excel formulas, spreadsheets in general, and has to send this finished product to a client each week. So I need this to be as simple as possible - which seems to me to be a formula (that can be copied to insert rows, etc. if necessary).
View 2 Replies
View Related
Dec 1, 2006
Please see the attached sheets.
Here's the issue:
On sheet "master" I have a list of goals in column A.
Each has been assigned to at least one person. Each person will have their own sheet that will capture the goals assigned to them. As an example, see Al's sheet.
I am trying to get Excel to look for all of the goals assigned to AL on the Master sheet and list them nicely onto AL's sheet without out blank rows. (and for each, respectively on their own sheet). I could do this with Pivot Tables if the data were displayed differently, but I have been told that I have to display the goal assignments as shown.
I could use "x"s instead of their names to mark the assignment and I could combine the "goal-achieved expectations" and "goal #s" columns if it helps. I am not supposed to use filters either.
Note: Of course, a goal or an assignment could change on the master sheet, but the assignment is more likely to change.
how this done for AL, I can go ahead and create the other sheets.
View 9 Replies
View Related
Nov 4, 2008
I want vlookup to return results based on the *** and age of the person from tables in a separate sheet. For eg. if the person is "male" or "female" and if the person is "<=15", "16", or "17". I have been practicising with a formula that looks like this:
View 6 Replies
View Related
Jul 13, 2007
My task is that I have several documents exported in excel. The requirements in them were mapped to another document. The IDs were populated in a certain column but are now no longer needed. I have to convert those old Ids to the current ones. In my example I have populated Sheet one with the lookup table. In column A are the old IDs. In Column B are the new ones they map to. The old ones can map to several which is why there are more than one ID in some of the cells. In column D of Sheet 2 I have populated it with the old Ids. In Column A I'd like to have the lookup return the appropriate maps to the new Ids. For example for the first map in cell A2 it should return:
abc_18
abc_43
abc_6
abc_4
all in one cell.
Vlookup can return the first hit (abc_18 and abc_43 for cell D2), but ignores the other two IDs in cell D2. I was able to work around this combining the find, mid, and vlookup to return all values. The problem with that is that the formula gets monstrously big after just having it return 4. In the actual files it can be up to 30 IDs which would make that formula literally pages long since. I have pretty much no experience with macros, but it seems like the only solution since I have to send this off to various people so they can use it.
View 3 Replies
View Related
Oct 22, 2009
I have attached the file I am working on. I am attempting to create a link to an external file based on the value of cells in column A. Then I would like to simply copy the formula down, lets say in Column B, the rows and as I do the external file reference will change depending on the value within the cell in Column A. I hope that I am making sense.
I am using Windows Vista with Excel 2007. The files will all be within the same file folder, however, there are hundereds of files so I won't be able to open them all for the indirect to work.
View 9 Replies
View Related
Feb 17, 2014
Sort Example.xlsx
I have data in Sheet "Schedule" in range A8:C160 (including column headers), but note the length (rows) of the range is dynamic.
A8 = Customer
B8 = Load Time
C8 = Delivery Time
Columns B & C are formatted as h:mm:ss AM/PM, but Column B also includes text..."PRELOAD"
I would like a macro to sort the data based on column B, but the sort must follow a specific order, which is listed in Sheet "TimeSort", range A1:A50 (including header).
The custom order basically has "PRELOAD" sorting at the top, then sorting everything else chronologically starting at 6:00am.
The desired outcome would sort the range based on column B as follows...PRELOAD, 6:00:00 AM, 8:00:00 AM, 12:00:00 PM, 5:30:00 AM
Example is attached (desired sort is reflected).
View 14 Replies
View Related
Dec 12, 2013
I'm trying to create a list that references an existing data set where I have staff listed month by month and based on today's date, imports only if there is data in that column. I have figured out how to check if the cell is blank or not, but what I want to do now is change the cell that is referenced in the formula based on the date. Here is the format of the spreadsheet I'm working with:
A
B
C
D
E
F
G
H
[code].....
In this case, the "Team 1" and "Team 2" and "Team 3" references what team they are on that month. If it is blank, they aren't with the company any longer. The formula I am using is intended to import this data elsewhere, and is formatted like this:
=IF(ISBLANK(B2), "", A2) - My understanding is that this checks to see if B2 has data, and if it does, it inputs the employee name (A3) in that cell.
My ultimate goal is to be able to change the column referenced after the "isblank" calculation based on the date. So if today is April 2013, I want it to check B2, but if it's December 2013, I want the formula to check J2. Is there a way to do this? I don't mind if it's two steps (like if I have to put the date somewhere in the spreadsheet in order to run the calculation), but ultimately it would be the type of thing I could do that would leverage the existing data set so that I don't have to maintain two different spreadsheets of information.
View 2 Replies
View Related
May 6, 2009
I have a cell which I want to colour green if C6>9, yellow if O6>60 and red if BOTH C6 and O6 are above their respective numbers. The first two parts of this are obviously straight forward, but I cant work out how to get the third part of it to work.
View 2 Replies
View Related
Jul 29, 2009
I am building a workbook. The data we are tracking is all in one xml file, which i have mapped to 3 different worksheets(customers, invoices, inventory). On the customers and inventory sheet I have an interface for creating a new customer/product/invoice. I used the macro recorder to make the macro's to do these three things, but could use help on a couple of other functions as I don't actually know VBA. I need to be able to automate editing of existing records by having a button to press on the each sheet that will open an input box that asks the user which invoice number, customer number or product number he would like to edit, then copy that record to the interface for editing, then another macro to replace the existing data with the newly edited data. It would also be wonderful if when creating or editing an invoice it could create a copy of the interface worksheet and rename it as the invoice number. The first row of each datasheet is blank, and each of the interfaces have formula's for importing the information copied to row 1 from the data tables. the second row of each datasheet contains formulas for importing data from the interface. Any help would be terrific...I know you guys are excel gods and I will forever be in your debt if you can help me out....thanks in advance, I'll be studying my butt off until I figure this out
View 11 Replies
View Related
Aug 25, 2014
how best to do this (bare with me I'm not quite a pro at excel yet!). I'll attach my doc (JMP Excel Forum) so you know what I'm taking about. I've set up the data to work in a similar way to this, as you can see I've a table set up (Appliance Sheet) which feeds information to the Main Sheet which contains the drop down lists.
What I need it to do is autofill the wattage column (Main!F8 onwards) with the wattage for the device as described in Appliance!N3 or N4 for example. For the wattage column to equal 28 (equivalent to Appliance!N3) then the following would show in the dropdown selections Type>Computers SubCategory>Monitors Device>BENQ G2222HDL Status>On
Is there a way to do this with the set up I have?
I did have a previous version of the whole worksheet that each Device had its own table and I looked up the wattage using and If formula to lookup a info on the device and knowing where to look it up based on the subcategory
=IF(ISERROR(VLOOKUP(D7,IF('Main UI'!C7="Heating_Cooling",Appliances!$A$7:$B$14, IF('Main UI'!C7="Kitchen_Bathroom",Appliances!$A$16:$B$27,IF('Main UI'!C7="Monitors",Appliances!$A$52:$B$62,IF(C7="Computers",Appliances!$A$64:$B$68,........
And I got it to work but it was getting to cumbersome to add in new devices and update the formulas. If attached that doc (OLD) so you know what I'm talking about. The last two sheets are the same sheets that make up the new document (JMP Excel Forum)OLD.xlsm
I really prefer the new way I've got it set up. Its easier to add new information or options to the dropdown list
View 2 Replies
View Related
Apr 9, 2009
Basically a cell might have a number with 3 decimal places
For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell.
Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items.
I included an example of what i mean in this excel file which should clear up any confusion.
View 2 Replies
View Related
Feb 15, 2009
In cell A1 I have a number representing a column and in cell A2 I have a number representing a row. In A3 I wish to obtain the value from the cell referenced by these two numbers.
ie
A1 = 3
A2 = 4
A3 = value from cell C4 (column 3, row 4). How do I write a formula in A3 to look this value up?? I know about the range function in visual basic but I just want to be able to do this using a spreadsheet formula.
View 3 Replies
View Related
Oct 2, 2009
I'm trying to do a formula that references a cell and returns a different result dependant on the number in the cell being referenced.
For example I've said if A1 has a 3 in it then put the word TEST as the result, plus if it has a 4 put the word RESULT.
What I wrote as my formula is as follows-
=IF(A1=3,"TEST")+IF(A1=4,"RESULT")
It works fine when I only use one result but goes wrong when I add two. If I change the words I want to show to numbers it comes up fine but with words it just returns a Value error.
View 2 Replies
View Related
Dec 28, 2013
If C5 refers to the value in cell C100, C6 refers to the value in C110 ten rows below. C7 refers to C120, ten rows below that and so on. Is there a formula that you use within excel (not a macro) that allows me to copy the C7 formula to C8 so that C8 will reference C130?
I have tried using INDIRECT and OFFSET formulas but cannot do it without an absolute reference to a fixed cell, which defeats the purpose, since I can go into C8 and manually change it to =C130, C9 to =C140 etc.
Currently using Excel 2007 at work and Mac Pro at home. Thought about R1C1 reference, but don't even know how to change to that style on my Mac.
View 9 Replies
View Related
Mar 27, 2013
I have 15 files in a folder and need vba code which would open every file from the specified folder and add that file name in the row starting cell "D1" in the "Master File". Below is the code which opens the file from the folder, but need the updated code which will add the opened file name. Also can u pls update this code to not open the "Master file" which is in the same folder when it loops..
VB:
Sub Login_summary()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String
Dim Fnum As Long
[Code] ....
View 5 Replies
View Related
Jul 10, 2008
In my excel sheet I have a list of where they go, which are basically excel cells.. i.e. A1 is the top left corner.
What I am trying to figure out how to do is, is there a way that I can get excel to see "A1" and then put it in A1 of another sheet on the bottom. Thus I get the same grid I have on the wall, making a map of my things.
Also, I could just cut and paste, but the problem is I'm working on organizing the stuff on the wall, and if I do it the way I want to, when I move things, they should automatically be moved on the chart.
So basically I need it to look at a cell for the reference, choose text from another cell (which will be concaternated(sp) text), and then place it in the referenced cell in the chart sheet.
View 13 Replies
View Related
Nov 11, 2009
I have the following two formulas in a spreadsheet, which sum and count, respectively loan information within a certain date range. Both of these are working correctly.
My problem is in substituting the dates with cell references for dates located in a worksheet with a table of dates. The dates from this table work with simpler formulas, but I cannot get them to work here, regardless of how many () "", etc that I try....
View 9 Replies
View Related
Jan 2, 2008
I have a basic blank file which takes production volumes of a product as an input and breaks the figure down to give individual component usage as an output. It then uses this figure to tell the user the best configuration to hold stock.
This file is 'save as' each month as the month, year and filename (ie: 11.2007MPS.xls, 01.2008MPS.xls) and then the monthly production figures are entered.
Within this file there are 'comparison' worksheets, cells on this page look into the previous months file and tell the user where changes have occurred; showing the physical changes that should be made. This lookup is referenced to a cell that gives the previous month ('MPS INPUT'$D$13), this is filled in by the user.
At current I am using the Vlookup(Indirect formula:
=(VLOOKUP(B4,INDIRECT("'I:SCIPMPS["&'MPS INPUT'!$D$13&"MPSCOPY.xls]Row 2'!$A$2:$N$100"),5,FALSE))
Yet, this requires all the previous months’ files to be open which isn't really feasible in the long run. Solving this issue using custom 'add-ins' (as i believe have) is not feasible either since the file will be used by many users who are not amazingly I.T literate.
View 6 Replies
View Related
Feb 25, 2014
I need to create a cell reference which will allow me to specify a cell in a table by value (using indirect) and pull the value from a cell immediately one row over for use in a formula. Say I have a table Containing column A and B; I want to reference the value of a cell in column A (e.g. "Large Widget") and receive (in the formula) the value in column B (e.g. "41 cm^2").
View 4 Replies
View Related
Jul 10, 2014
Warhammer Quick Reference Desktop.xlsx
Its on book 2.
I'm trying to reference the Cryptek total that I got in the previous area, and put that result into the grand total army point formula. When I reference the solution cell, or even copy and paste the very same equation into it, and then proceed to filter the referenced cells change...
The obvious solution that i thought would work is write =D45 next to Cryptek in the point value column, then if I filter it SHOULD maintain the information from D45 except it doesn't.
View 11 Replies
View Related
Feb 9, 2009
I want to display the contents of cell B5 in A7 by using cell C7 to show the location of the cell to be displayed. Cell C7 has a formula that shows the cell location of information (text) that needs to be displayed. How can I show in A7 the contents of cell reference shown in C7? In figure attached I show an example to better understand. Want in A7 to show the contents of B5 by reading what is in C7. So A7 cell should show word Road.
View 3 Replies
View Related
Apr 18, 2014
ATTACHMENT: Order Form.xlsx
I am creating an Order form for my job in Excel. On this form, it displays the Top 100 items for sale. All the basic functionality of the form is working fine, my real issue is with the order summary tab.
On this tab, I would like for any Items with a Quantity on the first Sheet to transfer to a summary on the Second sheet.
So far, I am able to get the data onto the second spreadsheet. My final question is, can make the items on the summary sheet go to the top available row? Basically if I have items in row 3, row 7 and row 9 - I would like for the items to automatically fill in rows 3,4,5 respectively. (on the second worksheet)
I have seen people make this happen (on this board even) but for some reason, when I try using those formulas I do not get the results I need. I have attached the file to this post.
On the Tab entitled "Adv Piano" this the product list. On that tab, customers can put a Quantity in column G for the items they want to order and that item's details, price, and quantity will automatically appear on the 2nd tab, "Order Details" in columns I - L in the corresponding row (this is already done)
I need the information in the columns I - L on the "Order Details" sheet to appear in columns C - G on the same sheet, in the same order but to fill in towards the top with the corresponding quantities, item details and price remaining in the same row.
Order Form.xlsx
View 10 Replies
View Related
Jan 16, 2009
I have the following formula in a cell that gives me a sum of items matching certain criteria.
View 3 Replies
View Related
Apr 6, 2014
Referenced cells returning zeros: can these be made blank or string data?
But why I still get the value 0 instead of blank cell when I press ctrl+shift+enter?
My formula = IF(U15="Bank", PROPER(E15),"")
and E15 = VLOOKUP(B15,'Customer Data'!$D$6:$K$100,3,FALSE)
View 5 Replies
View Related
Dec 11, 2009
I want a dialog box to have a label box reference a cell within a work sheet. This referenced cell contains a formula linked to other cells so it will change value.
View 14 Replies
View Related
Mar 14, 2014
Trying to get a problem solved to have a % discount & 'flat' $ discount apply to cell(s) referenced. Product A, B & C are available in 3 different materials. Objective is to allow each material's Max Discount column to apply discount(s) referenced in cell J3 & K3, but only in the order of applying the percentage discount BEFORE the 'flat' $ discount.
Formula in cell C3 works IF the cell "Range to apply discount" I3 = "B3". Works just fine if I allow only one cell to be displayed in I3, but ideally, I'd want this to allow the said discount(s) in cell J3 & K3 to apply to any cells mentioned (for instance, for Product A Material 1, Product B Material 2).
I had tried a drop-down menu using Data Validation, which in my actual project allows me to select ONE cell at a time (out of my list of options), which is okay, but it would be extra useful to have a checkbox option to select which ones to apply the same discount to, instead of creating a massive embedded IF function to have each product in each material find whether it's cell is referenced in 20 different places (if I just copy the already functioning single cell reference tool).
The scale in which I'd like to use this in would be to apply specific discounts for one product, but a different discount for another product or material, and allow the input cell I3 to include a checkbox drop-down option (like the filter/sort), but not remove the data in the table (Range A2:G5 in this example), so I can hide any unnecessary columns and print, showing certain products & materials having a 10% discount, while others having a 20% discount, with an additional $5 off, etc.
[URL] ........
View 2 Replies
View Related
