I am having to use = sign (=A10 --) transfer the information to solution, is there a formula that I use instead. I have try $A10,A$10 to ancher the column?. ideal I need formula that read alternative columns for date, unit and sales- that I can copy down?
The below formula is giving the desired result and everything seems fine. Need if there is an alternative formula which can give same result in more refined and simplified manner. =K28+SUMIF($E$3:$E$24,$E$7,$D$3:$D$24)/(K23)-SUMIF($B$3:$B$24,$B$8,$D$3:$D$24)/(K23). The formula is based on the following data:
K28 = Last month balance Column E = Type of expenditure Column D = Values Column B = Payments K23 = Currency conversion rate
a) Column A: 900 serial nos of Work Orders. Each is unique.
b) Column B: Parties responsible for each Work Order no. Certain parties will have more than 1 serial no in Column A.
Note: Column A & B are the main reference.
c) Column C, D & E: List of parties from three different locations performing works base on serial nos in Column A.
I would like a list of serial nos performed by parties in Column C, D & E base on Column A. I can't use the autofilter or sort function on each individual parties as this is very time consuming and moreover the info keeps changing ever 2-3 days. What I require is a simple summary list where I just need to paste the info from Column C, D & E into a special table and it would list down the serial nos from Column A. I had tried the INDEX & MATCH functions but it won't work as I have some parties performing more than 1 serial number. Would it be possible to create a table to perform the said task. These data entry and retrieval has taken minimum 4 hours of my time everyday and it takes longer when certain lists are revised.
This formula works great and does everything I need it to do, BUT unfortunately I have since discovered that it is not compatible with several end applications that I am using with my spreadsheet.
Can somebody please suggest an alternative to the array formulae, as written in cells: O6, Q6, S6 & U6, while still giving me the same end result?
My problem is that the =SUMPRODUCT is slowing my sheet down considerably and I was wondering if any of you formula whizzes can convert it to a less processor hungry alternative such as DCOUNTA (if that's possible) or suggest a better method.
I'm trying to find total gross commission within a spreadsheet, where gross commission found in Deals column Q. Columns T & U indicate with an "X" whether the deal was associated with referrals and such.
I do hav a sheet in which i do hav some column;s n data
Worktype Metrics April May June
in work type few rows are hidden which i don't need what i need is i want to populate only comman metrics in other range named metrics i had build a macro for this and that's working fine but the problem is it also reads metrics from hidden rows too. i had tried a loop in which i had placed as hidden column property condition.
I have a situation where I have to curve fit data, this can lead to different formulas being used with varying constants.
Is it possible to pickup a TEXT based formula and related constants from other cells, and then place this into another cell as a functioning formula. For Example
Cell A1 contains the formula as a text string whether it be y=a+bx+cx^2, or y=a+b/x, etc Cells A2:A6 contain the individual constants, a, b, c, etc
I would then want the VBA to read the text based formula and put it into an output cell as a functioning excel formulae.g
In cell B10: =a+b*A10+c*A10^2
I understand picking the constants up and putting the formula should not be too much of an issue, however trying to insert the variable form of the curve fit is the part that I am struggling with, and am unsure if possible.
I have a userform with a bunch of textboxes that read from certain rows/columns/etc. However, when I hide certain columns (I need them hidden) the textboxes don't display the data (it only displays when the columns are not hidden).
I'm trying to open a file on a network drive...but I'm getting the following error message when it opens: "This file may be read-only, or you may be trying to access a read-only location. Or the server the document is stored on may not be responding." Now, the file itself has no rights restrictions and is not read only. It doesn't appear to be locked.
Now, there are other Excel files in the same directory which I could open fine; however, the Excel documents having the above problem all have a little black icon "appears to be a padlock" (image attached) at the bottom left hand side of the Excel file icon. I tried the following:
- Renaming - Converting to a different file format (didn't work, it won't let me) - Opening in notepad...etc doesn't work.
This file is dated back in 2004...do you think it's corrupt? Is there anything i can do to open or recover this?
I was wondering if it is possible to write a formula so that the below table can be read based on the input (in this case start month and cut-off month) and return the value from the table. I have also attached the excel with the data and some examples.
If I type the name Joe Bloggs How can I make whenever i type that name the computer knows to retreive a number or formula from a cell on another or same sheet.
I am using the following code to read a formula as a string. But sometimes the use r may forget to enter formula in the particular cell. In this case I want to check if the first character of the string is a Equal-to Sign (=). In case it is then macro proceeds further otherwise a message prompts user to enter a formula first. how to check if the first character of the string is a '=' sign?
In cell AD17, I have a formula that returns the number of days or draws(Skips), since the number in cell AD2 has been drawn. The following cells AD18.......... returns the next skip and continues untill all the hits( AD3), are accounted for. My question, Can the formulas be altered to read a "
All this is in one big table and I have around hundred columns. I wish to create one lookup table that will pull values out of this, on a separate worksheet. This is how my lookup table looks like:
Measure3 Measure8 Facility1 Facility2 Facility3
My raw labels will be complete and they should match with lookup table labels. I need to extract Measure3 and Measure8 for different facilities, depending if I have any data in there. The search criteria for column labels should be dynamic and if I change label for let's say Measure1 it would update for all facilities. What formula can read both raw labels and column labels and return value at the interesection of both these criteria.
I'm attempting to create a formula that will find the name associated with a value, and return that name on the same column as that value in a later equation.
OK OK to illustrate it a little better:
There are three people: Bill, Ted, and Andy. Each one is ranked in Points, so my table looks like this:
A B
1 Bill 10 2 Ted 20 3 Andy 30
Later on, I want to rank the individuals based on their score, using a formula. Right now, I can rank the scores based using LARGE(B1:B3,1), then LARGE(B1:B3,2), and lastly LARGE(B1:B3,3). That ranks the numbers in descending columns. However, I want the information to automatically populate the name associated with that particular point total. So, I want the system to know that B1 is Bill's score, and rank it, in descending order, later on in the spreadsheet, with Bill's name.
I've been looking for an alternative to SUMIFS as I have a sheet I need backwardly compatible with Excel 2003. I've looked at SUMPRODUCT which I understand is a good alternative to COUNTIFS but does not allow for summing. I've now moved on to looking at an array formula I picked up from an internet search, but it's not working properly for me. The crux of my Excel 2007 formula is:
I'm using sumif to get the total number of OT minutes/hours rendered by an employee. In a company with 1K employees, doing so proves to be tedious as I need to rename the criteria for each formula. Is there an easier way to do this? See attachment for more details. I'm using Excel 2003.
I have a lot of data that I need to have organized out but as far as I can tell an IF statement is the only thing that could work. I work at a casino and there are about 6,000 games that I need to be able to sort through. The games are classified by sections A - Q. Each section contains anywhere from 30 - 50 banks, and each bank contains anywhere from 4 - 20 games. This is what I am trying to accomplish.
Have a drop down that lists zones A - Q. If the user chooses A, for the next column to populate with a drop down of all of the banks in A. Then for the user to choose the bank they want and then have the next colum populate a drop down with the games in A>bank1. So basically...
Zone>Bank>Machine
then once you choose the machine, for the columns to the right to auto populate the information on that game such as themes ect.
Zone>Bank>Machine>Information on that machine
I have another sheet that contains the inventory of all of the games that I am using as my "database". I am able to get this working great with IF statements, but I obviously need to be able to use more than 8. Here is my current code...
I am trying to make an IF statement and need to enter 12 if's. Excel will only let me go to 7. Example, if Jan is in AE11 the copy the number from B96, if Feb is in AE11 then copy number from c96. Everything works up to July.