Specifying Row Number In Formula
Jan 6, 2010
I want to do as the following:
In sheet 1 I have all the data needed for sheet 2, organizing in row, which means for a particular cargo, its details will be all having same row number, different column alphabet.
Cargo A data 1 data 2 data 3 ....... and so on
Cargo B data 1 data 2 data 3 ....... and so on
In sheet 2, I have a form in which I wish to have it auto fill in the cargo particular by inserting in just the row number for the cargo in sheet 1.
Cargo A
data 1 =Sheet1!A#
data 2 =Sheet1!B#
data 3 =Sheet1!C#
"Cargo A is row 2, then # should be substitute with 2"
View 9 Replies
ADVERTISEMENT
Mar 14, 2014
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
View 3 Replies
View Related
Apr 27, 2014
I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.
View 11 Replies
View Related
Aug 25, 2006
Designated Cell = 7
1 10 .034
15 25 .072
35 45 .089
Output Cell = ???
I am trying to find a formula for the output cell. Suppose the designated cell = 7 due to formulas above it. So that number is always given but can change. I then need a formula for the ouput cell that says if the designated cell is greater than or equal to 1, and less than 10, then the output cell should equal .034. So suppose the designated cell = 18.2. Then I would want the output cell to be .072 since it is greater than or equal to 15, and less than 25. The template I am using has 23 rows of numbers like above. I didn't know how to do it for 23 rows though. If/Then doesn't work because it is 23 statements long (well over the seven limit) and the vlookup only works when the cell is equal to a number and not a range.
View 9 Replies
View Related
Sep 28, 2006
I'm trying to use the formula vlookup, but running into trouble. I'd like to use =VLOOKUP(3000&A1,E:F,2,0) but this isn't working correctly because it doesn't recognize "3000&A1" as a number? Is there a way to do this? This will be used in a VBA code, so I prefer the solution in VBA, but either way is fine. See attached for more info.
View 4 Replies
View Related
Jul 22, 2014
Is it possible to write a formula that skips x number of cells and have the formula autoincrement as usual. For an examples lets say I want to skip 3 cells and I have entered in the cells listed:
A1= "1"
A2= "2"
A3= "3"
Then I type in the simple formula in the cell
B1= "=A1"
and then drag down but it skips 3 cells so then
B5= "=A2"
B9= "=A3"
If it didn't skip then
B1= "=A1"
B2= "=A2"
B3= "=A3"
this is just an example as I will be writing more complex formulas and need some thing that I can write into the formula so that it skips.
Something similar to this B1 "=A1(skip3cells)"
View 4 Replies
View Related
Apr 16, 2014
I have list of item numbers which some of them contain letter "C" or "S" at the end. For example,
8002
8002C
8002S
85555C
825S
Is there a way to select number only?
View 2 Replies
View Related
Dec 14, 2013
For instance I have a formula such as =sum(A1:A9). But imagine that the range is not always till row 9 but depends on the total number of rows that are in the table. How can I change that 9 so that it takes a number equal to the total rows in the table?
The final formula I want is not that simple one. It is actually
=INDEX($F$1:$F$1047,MATCH(1,INDEX(($G$1:$G$1047=G2)*($L$1:$L$1047<>"47200")*($L$1:$L$1047<>"47700"),0),0)).
I want to change 1047 by the relevant number of rows. Also, I do know how to do it in VBA, but I have a problem with the length of the formula there (not that one, but other bigger than that)
View 5 Replies
View Related
Mar 23, 2014
The formula below works fine except for,E3,F3,G3,H3,I3. They work great if I was on ROW 3 but A2 could take me to ROW6.
=IF(ISERROR(VLOOKUP(A2,Players!B:B,1,FALSE)),"", SUM(IF(Players!E3="y",Players!$T$5,"0")+(IF(Players!F3="y",Players!$U$5,"0"))+(IF(Players!G3="y",Players!$V$5,"0"))+(IF(Players!H3="y",Players!$W$5,"0"))+(IF(Players!I3="y",Players!$X$5,"0"))))
View 5 Replies
View Related
Feb 18, 2007
formula to put in microsoft excel 2003 to increase a number by 10%, so for example, 20 by 10%.
View 10 Replies
View Related
Jul 3, 2007
=IF(OR(Q136<O132,Q136>O132),Q136-O132,O132-Q136)
This does not seem to be working for me. I need to check one number against another and if the one cell is larger then the other subtract it or just the reverse.
View 12 Replies
View Related
Oct 14, 2008
I need to do a vlookup with the array starting on a row determined in another cell, so I've tried concatenating the number into a formula but this doesn't seem to work. Is there something else that I can do?
E2 is 134
=CONCATENATE("=VLOOKUP($B2,$C$", E2+1, ":$F$2571,3,FALSE)")
returns: =VLOOKUP($B2,!$C$135:$F$2571,3,FALSE) but not as a formula, as text and I need the formula...
View 3 Replies
View Related
Feb 11, 2009
I need a simple formula that would save me some annoyance. Basically I have like 20 columns filled with various numbers. At the last column I like to take the last number in that row. However each row "last number" is in different column. So how do right formula that would basically take the last value in that row?
I have illustration here.
column 1 column 2 column 3 column 4 Final Value
ROW1 3 5 7 7
ROW2 2 2
ROW3 1 4 4
ROW4 8 8
What I want to do is write formula in Final value that will take the last number in the row.
View 3 Replies
View Related
May 25, 2009
I play Keno a lot and have what is probably a very simple formula request. If I’ve got for example this result pasted into Excel: 06 12 15 21 23 27 28 31 32 34 48 49 54 56 60 67 68 74 76 77. How would I go about determining if a pair or two numbers had come in?
Say I was looking for 31 and 76, is there some way I can get Excel to recognize the two spot hit then post in response either 1 (in bold) if yes or 0 (not bold) if no the two did not come in together? Note this would not be only one or the other, they both would have had to hit at the same time.
View 3 Replies
View Related
Jul 29, 2009
i need to use a cell which has the value 00:30:00 (formated as hh:mm:ss) displayed as 0.020833333 (formated as general)
what i need to do is use the 0.020833333 for a calculation.
now the problem is that i have 00:30:00 as input.
that means the user is inserting 00:30:00 in a cell. i need to convert this to 0.020833333 and use it in a formula
the conversion itself is easy. (just change formating of the cell)
i tried using it like this:
A1: 00:30:00
somenumber<(TEXT($A$1;"##.#########"))
but that gives me strange numbers
and what i need is this:
somenumber<0.020833333)
View 9 Replies
View Related
Aug 5, 2009
I needa formular that will subtract a number per day, for example as one day passes you can set the amount of number to be taken away from the total.
View 12 Replies
View Related
Oct 7, 2009
I need a formula to add the number 1 into cell J9 when cell d9 is no longer blank.
I'm sure it's really easy- but I can't figure it out.
View 4 Replies
View Related
Aug 9, 2012
If I want to include the value of cell C4 in a formula, is it possible to keep the formatting ?
Cell C4 = 10,000.50
If I use a formula :="Profit for the Year ="&C4
ResultProfit for the Year =10000.5
View 2 Replies
View Related
Jul 1, 2013
I have a formula that computes the row number in B58. I would like to combine the row # in the formula after M_MAN!A1015 for eg M_MAN!"&B58&" instead of having to type this. I can then use the foormula to copy the balance of the formulas
=-SUM(OFFSET(M_MAN!A1015,0,22),OFFSET(M_MAN!A1015,0,23),OFFSET(M_MAN!A1015,0,24))
Workings  BC57Row #Quarter5810150Spreadsheet FormulasCell
FormulaB58=ROW(HC_MAJOR_EXP)C58=-SUM(OFFSET(M_MAN!A1015,0,22),OFFSET(M_MAN!A1015,0,23),OFFSET(M_MAN!A1015,0,24))
View 2 Replies
View Related
Feb 3, 2009
Is it possible to determine how many times a formula will be used? Here’s my problem.
I have items that are bought, and then altered. So I need a cell to determine how any times my ‘IF’ formula is used. My current formula is:
Formula entered into D3.
=IF(D2>0,D2,””)
Row 2 is the amount and Row 1 are dates.
If I drag the formula to the right, E2 will be equal to 0 so will enter 0. But I need it to say what ever is in D2 3 times. (As this particular item takes 3 weeks to do) So D2 & E2 & F3 will all have whatever was entered in D2.
View 9 Replies
View Related
Jul 18, 2006
I am trying to create a formula (or if VBA is better) where when a name is put into a field the next alpha mumber is created. Have attached example.
View 3 Replies
View Related
Aug 2, 2006
In a cell there would be a formula, an "if" formula and I want to know is there something in Excel that will lock in a true statement. Like lets say in cell "A1" a formula would read =if(b1>1,.001,.002), I want to lock in a number when the statement is true. Cannot be change no more after when it is true.
View 9 Replies
View Related
Sep 24, 2007
If/when I have removed information from a row, can anyone tell me how to remove empty row but leave row numbers intact and following each other in numerical order.
View 5 Replies
View Related
Apr 16, 2008
I have a monthly report on an excel spreadsheet that I must sum two columns from the previous month for every row in the sheet. I wish to take the value from column B and add to the end of the formula in column A. For instance my column A would contain the following: "=1200+6595+2599+275"
Column B would be a single number, i.e. "3200"
I want to be able to click a button and get "=1200+6595+2599+275+3200" in column A and place a "0" in column B for every row on the sheet. I have a pretty good understanding of VBA, but I am still learning the Excel object model.
View 2 Replies
View Related
Oct 4, 2012
I am trying to make a formula which will tell me if A1 is a postcode (a letter and a number e.g CV42 6AQ)
In the A column, it looks like this:
CV42 6AQ
FC45 D4D
West yorkshire
PR42 6RD
Etc.
i want it to identify all the postcodes, and NOT "West Yorkshire" because it does not contain a number.
View 3 Replies
View Related
Mar 29, 2014
I have WorkbookA that pull numbers from WorkbookB. In Cell D2 of WorkbookA is the name of WorkbookB. The data to be pulled from WorkbookB is in Cells H364 through S364, H being data for January and S being data for December. In January, the data from Cell H364 is pulled into WorkbookA. In February, the data from Cell I364 is pulled into WorkbookA, and so on.
The formula can be written such that, based on WorkbookA indicating in Cell B14 the current month, each month pulls the correct month's data from these cells in WorkbookB.
View 14 Replies
View Related
Jun 12, 2014
I need a formula to copy a number stored as a text to the number.
I have a formula in, let's say on Sheet1, cell A1: =IF($M$3,TEXT(N7,"0%"),TEXT(N7,"0.0%"))
I then copy this cell to another sheet, let's say Sheet2, cell A1 =Sheet1!A1. I have then have a bar chart linked to this cell, but it's reading it as text and no bars are appearing in the chart - even though the cell in the second sheet is formatted as a number!
FYI - my work around is to link my chart to the underlying data in the formula above, in this case, N7. But then I'm not getting the whole number (%) or decimal (%) that I want when the value is displayed in the chart.
View 3 Replies
View Related
Jun 25, 2014
I need a formula to take away a percentage according the amount (number) ot procedures.
For example:
- procedure - price for one - 5 of it - 10% discount - final price
The idea is, for example, if I type amount of 5 procedures, automaticly to make 10% discount, if I type 10 procedure - make 15% discount etc.
View 6 Replies
View Related
Jan 5, 2014
I have the following formulae to pick up a random number from 0 to 9999.
=TEXT(RANDBETWEEN(0,9999),"0000")
I want to start this random search from a specified number. for example if i press F9 it should show 3456 at first press and then onward.
View 1 Replies
View Related
Jan 27, 2014
I have a list of customer including customer name and customer number. Is there a way to exact customer number out only? This is the example list.
WAL-MART STORES INC - 1028
KOHL'S - 28983
JOHN'S PET PRODUCTS - 3692
PET SUPERMARKET, INC. - 2298
COSTCO WHOLESALE - INTERNATIONAL - 2783
LOBLAW CO-WESTERN GROCERS - 7424
All I want is the number behind the dash. There are few customers that have more than 2 dashes.
View 8 Replies
View Related
