The find value will always be the same...its just a placeholder that is randomly inserted. (currently i am using the value "Placeholder"). basically in column B i want to insert the iteration of the Place holder. the attached spreadsheet better illustrates what i am trying to do. starting the count at zero is kind of important, but not the end of the world if it is impossible.
View 3 RepliesI am having trouble using a cell reference with a VLOOKUP formula. i have 2 worksheets each containing an invoice number column. the first sheet uses the VLOOKUP to find a shipping code from a range of values in the 2nd sheet (the range includes the invoice number, shipping code and 2 other columns). i can get the formula to work by simply typing the lookup_value into the formula like so: =VLOOKUP(7128,'Detailed List'!$B$7:$Q$1500,5,TRUE)
this gives me the correct result but what i need to happen is for the formula to take the lookup_value from the invoice number cell reference in the first sheet for that record and match it to the same value in the 2nd sheet. so instead of the code above it would read: =VLOOKUP('F10','Detailed List'!$B$7:$Q$1500,5,TRUE). the value of cell F10 is '7128'. however, when i try this i get a return of #N/A and no matter what variation i try it still produces the same result.
I need to find the row number of a cell based on a particular value. I am populating a row within a spreadsheet with a value (the columns have unique identifiers). After that is done, I need to go to a different spreadsheet, grab different values, and place them in a different column in the above referenced spreadsheet. So, what I want to do is find the row number for the unique identifier, then place the value in the column.
View 3 Replies View RelatedOn sheet 2 in cell C5 I need it to find it from sheet 1 referring it from the word "Drinks 1" thats in Q1 and so on along the row
View 9 Replies View RelatedHi, sorry for posting this twice but i wasn't sure which category it fell under!
I have a slight problem with excel and and was wondering if anyone can help.
I have 3 columns, A, B, C: .....
Cells L5 - L65 have percentages. B5 - B65 Have the associates names. I want to write something in another cell that looks for the highest percentage and then prints the name as the highest.
View 11 Replies View Relatedi have duplicate cell entries occuring. I have a column of about 8000 entries (Column B) and would like to have a cell at the top of my spreadsheet that displays where the first duplicate resides (Row No. will suffice).
At present i have a conditional format on dupllicates, but is is a big task to scroll down through all the data looking for them.
It now requires a special chart, I am trying to replicate what this other program does, but is slightly corrupted and it's chart section does not seem to work on XP.
The program was made in DOS so I can't provide a Print-Screen shot to show how it looks. If it gets too confusing describing the graph, I have to get XP to somehow run a DOS program correctly so the graph may appear to get a Print-Screen shot. The chart shows the "best 3" NEGATIVE rise in a progression and the rising lines are colored in red. If the other values are POSITIVE, it shows a fall in Blue. ( black background). So if the value of Item 1 is -17.5% and the next Item 2 is -12%, then the red line of Item 1 will show the highest.
BUT, it sort of follows the line back from the OVERALL percent values at the end of the calculation from the start of the first percent value, it's first entry. I have included a sample sheet, all I need to know if a progressive chart can be created with the way I have positioned the numbers, if not let me know how the numbers need to be positioned and it's simply a matter of me making a few changes in the workbook that processes the sums to suit. Finally, the amount of entries can be anywhere from 3 to 30, I have shown in the example 9 entries. The purpose is to show a progression of price changes using percents as the indicators.
I am running a sales spreadsheet that requires sales entered each day. I use a simple formula to run a continuous total starting at Monday and ending Sunday (1st cell for sales input is B4, second D4, third F4 and so on. 1st Cell for accumulated sales is C4, second E4 and third G4 and so on.
I use B4+D4 to caluculate the running total in E4 or example,and to eliminate that total appearing in G4 where the next formula is B4+D4+F4 I precede that with an IF formula IF(E4=0,'', ). This way I only show the current Week to date totals, not the upcoming days in the rest of the week. My problem lies in Holidays. If for instance D4, the Tuesday was a holiday and I enter either "holiday" or just leave it blank, the rest of the weeks formula will not work. I get a Value message. How can I achieve my goal of getting a daily week to date total without having it show in the upcoming days AND have a holiday in there too?
I have a column of data showing trade results - in the most basic way I can explain, as my account value GROWS by a preset amount (say 20-30%) how can I ADD to the number of contracts I am trading. For example I start with $10,000 and the account grows to $12,000 or 20% - according to my own trading rules, this allows me to ADD an additional contract to trade (I start with 1 contract). As the account continues to grow progressively 20% from the previous 20% jump, I add ANOTHER contract.
and to top this off, I do the same in reverse, If I lose or go into a drawdown .. how do I reduce my number of contracts traded?? so lets say I am up to trading 4 contracts and I lose say 10% of the account value - I want to slow down or "ease up" the number of contracts until I get the account back to its previous high. Anyone know how this can be programmed??
One last final caveat - when you go on a winning streak, I add for every 20% INCREASE in the account value and when I lose I reduce contracts at every 10% pullback to preserve winnings as much as possible.but I am ALWAYS allowed to trade a MINIMUM of 1 contract
ok, this may be the same answer as the previous post i did, but if so, i can't figure that part out.
In O2, if M2 = 8, and N2 = 2008, then add O3 + 8 rows (O3:O10)
or, if M2 = 5, and N2 = 2009 then add O16 + 5 rows (O16:O20).
I have this data:
Valor
5
2
5
3
16
1
2
Progressive Total Sum can't > 28:
How can I get this in column "NrPag":
Valor NrPag
5 1
2 1
5 1
3 1
16 2
1 2
2 2
i.e.:
5+2+5+3--> < 28 so page N.1
5+2+5+3+16 > 28 after 3 I need page N.2
...
3 1
16 2
1 2
2 2
Excel 2007
ABCDE12145101843222121028543291410388563015104796731501058178325210
685894953107839104354108841011445510985111349661101215516710013135668
981417576999151858708916195971801720607291182161738219236274902024637
59121276476872228657786232425Sheet1
I came across the solution to this post - [URL] ..... - and was just wondering how to adjust the formula so that it calculates the correct tax starting from the net amount.
Say for example, an employee is paid a fixed, after-tax salary amount, and the employer is responsible for covering the tax - the salary payment needs to be "grossed-up" to calculate the correct amount of tax.
With 'tax brackets' as follows:
Taxable incomeTax on income (2011 – 2012)Tax rate
$0 – $6,000Nil0%
$6,001 – $37,00015c for each $1 over $6,00015%
$37,001 – $80,000$4,650 plus 30c for each $1 over $37,00030%
$80,001 – $180,000$17,550 plus 37c for each $1 over $80,00037%
Over $180,000$54,550 plus 45c for each $1 over $180,00045%
The formula to calculate tax, starting from the gross amount, is:
[Code] ......
Each row in Table 1 represents an "Expanded" array formula.
In Table 2 I have a "Running" formula to return progressive max values working Left to right.
My question is, can this be written as an array?
Returning something on these lines ...
{(IF(B1="","",MAX($B1:B1))),(IF(C1="","",MAX($B1:C1))),(IF(D1="","",MAX($B1:D1))),(IF(E1="","",MAX($B1:E1))), etc.}
Drag B7 across and down to see what the array should contain for each row in Table 1
Sheet1 ABCDEFGHIJKLMN1Table
1 1234 42 141 5 3 53 1 46 423 64 122 4567 89105 122 4657 89106 7Table
2 1234 48 144 5 5 59 1 46 666 610 122 4567 891011 122 4667 8910Spreadsheet
FormulasCellFormulaB7=IF(B1="","",MAX($B1:B1))
I have several series of 13 figures (from 0 to 20), such as :
000141833000001
I would like to filter those series with the following condition :
Each figure (except the zeros) should be higher or equal to the first figure (found on the right) which is not a zero.
In the example :
14 should be higher or equal to 18 : FALSE
8 should be higher or equal to 3 : TRUE
3 should be higher or equal to 3 : TRUE
3 should be higher or equal to 1 : TRUE
So, the serie is FALSE, due to the fact that 14 is not higher than 18.
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.
Marking My Post As? How do i mark my post as solved?
View 5 Replies View RelatedI have a worksheet ('Matrix') that has a grid that is 40 rows x 40 columns. On another sheet ('Drivers') I have a two columned table where a user is enters two numbers between (you guessed it!) 1 and 40. What I'm trying to do is within the grid, have a "Y" appear on the crossing point as denoted by the table. I've tried a couple of experiments with MATCH, but I'm not getting very far.
I've attached an example document to make this a bit clearer and I've coloured the references on the 'Drivers' sheet to correspond to where they should appear on the 'Matrix' sheet. Sorry if that's a bit garbled. Let me know if you need any clarification.
Usually when I try to mark a row/column for future reference I code the following:
View 14 Replies View RelatedIs there a way to go down a sheet and mark a certain row with say an X Then run a macro to copy all the cells in that row marked with the x to another sheet. There may be 45 X's down the sheet.
Example Below..
FirstLastAddressCityStZipCode
DaveJones55 Pine StElkhartPA3333221
xTomJones56 Pine StElkhartPA3333221
JoeJones57 Pine StElkhartPA3333221
xAmyJones58 Pine StElkhartPA3333223
So to either copy them to another sheet or even better a Word Document that is setup that has Cells to accept everything from First to Code.
I am wondering what is a convenient way to invisibly mark off certain cells on a spreadsheet, so that VBA can later recognize that they are so marked, and thus treat them differently. I have a spreadsheet listing various farms that our hatchery purchases eggs from. I have a commandbutton on that sheet that opens up a userform, allowing the user to assign certain farms to participate in various breed tests. Ideally, when the user selects a farm for a breed test, the corresponding cell on the spreadsheet (containg that farm's name) will be marked in some invisible way. That way, when I run another macro, the code will be able to recognize that cell as marked, and treat it accordingly.
So, what is the favored way of marking a cell in a way that will remain invisible to the user? I know that there are plenty of properties that a cell has, many of which appear useless to me. Is it good protocol to adjust any of these as I like, or is there some method that is to be preferred?
So I am trying to mark certain rows as duplicates if their values in Columns J, AD and ABS(BD) match. J and AD contain Number letter Identification combinations and BD contains a value. I currently have a formula as shows that will mark all of these values:
[Formula]
=IF(AND(COUNTIFS(J:J,J15,BD:BD,ABS(BD15),AD:AD,AD15)>1, (SUMIFS(BD:BD,J:J,J15,AD:AD,AD15,BD:BD,ABS(BD15)))<>ABS(BD15)),"Duplicate","")
[Formula]
The issue with this formula is that I do not want it to mark rows that look like this:
J - AD - BD
AA1 BB2 20
AA1 BB2 -20
AA1 BB2 20
I have a lot of rows that will appear like this. Their sum in BD equals the absolute value of the value in BD. I thought this would get picked up using the SUMIFS portion of my formula but it is not. I cannot find the problem.
there are loads of posts sitting with requests for more info but just sit there because 2/3 replies have ben posted but nothing has been actualy done.
i dont look at these because i assume problem has been fixed so when i/others look we could actually still have a go at helping the original person better still once this is in place have a link to unresolved posts
I am looking at a register of documents that are constantly being revised. What I need to do is mark up which ones need to be reviewedDocument RevisionViewedapples1apples2yapples3apples4apples5pears1pears2ypears3pears4oranges1oranges2oranges3
If a revision is marked as Y then previous revisions don;t need to be looked at and can be marked with n/a anything older doesn't need to be reviewed except for the latest one. In the above table if apples revision 2 has been viewed so rev 1, 3, and 4 are no longer relevant and only rev 5 needs to be reviewed. Pears rev 2 has been reviewed so on rev 4 needs to be reviewed. No Orange documents have been reviewed so only rev 3 needs to be reviewed.
The above is just an example my table consists of 8000 entries.
A little background, I am trying to match CC deposit batch transactions to daily sales transactions. I have two reports, one from our credit card processor/gateway (Report "B") and one from our software where the daily sales (Report "A") are recorded. I used the INDEX/MATCH formula to pull the information I needed from Report "A" into Report "B". It worked perfectly. My question is, is there anyway that I can show what information has been pulled from Report "A"? Possibly by highlighting what was pulled over? It is a longshot, but it would make my life so much easier if it was possible...
View 1 Replies View RelatedI receive an automation error, when using this code, the debugger is marking the line with n =...
View 14 Replies View RelatedI have a huge lists of names that I need to work with. The first is a list of all employees. The second is a list of those employees who have submitted their monthly report. In theory every record should be a match. I need to compare the lists and mark the people who are missing their monthly report in the main list of all employees.
Not sure how to set this up in Excel
Sheet 1: Column A - All employees names, Columns B-M: used to show whether a report has been submitted.
Sheet 2-13 (July, Aug., Sept., through June): Column A - employees who have submitted their report.
I need Sheet 1 to show either all of the missing records across all the months or it can show the ones that have been received. Either will work.
I am trying to find the percentage in a column that is used the most. I know =MAX() finds the maximum number but I need the one that is used the most and if there are 2 that are the same, I need the greater of the two.
View 9 Replies View RelatedI am trying to find the next cell in Column C that contains a value. Please see attachment.
ie.
I need a formula in cell E7 that will find the next number in Column C after the row it is in (ie. the number "2" in cell C9). E7 should then return the row number (ie. 9).