I have a list of sales which is mixed with Dollars and Euros in the same column. I want a formula which will detect which currency is which and make 2 seperate totals for the corresponding amount. I have the currency signs in the same column but seperate to the amounts.
View 9 RepliesI have two worksheets, one which contains a list of workstation numbers, and another which contains all of the workstation numbers and additional information on the workstations in the company.
I want to remove all workstation numbers and their information which are not present on the first list from the second list.
In the first w.orksheet each workstation number is in a seperate row, and in column one (seperate cells).
In the second worksheet, each workstation number and its corresponding information is on the same row, and each different workstation is on a seperate row with its information.
In sheet1 i have a Listbox(Controlbox), which stores data of 100 names. Now in the D column i need to type the Name. So i would like to have a code which can pull the list box below the Cell I am Typing and it has to match the strings i type to list box(i.e, Pull data from the Listbox as i type the character in a cell)
So here i need 2 Modules
1- Which will pull the data from the listbox
2 - which will bring the list box below any cell which i am typing
The following code fills down column B for rows 3 to 110, regardless of the inserted "If Not IsEmply' statement. I've got formulas in Column A from row 3 to 110, but visible values in rows 3-5. I want it to fill the for the visible values only.
View 2 Replies View RelatedI have two lists, one that is 99,000 lines and the other is 150. I am trying to find out where the word(s) in the list of 150 is present in the list 99,000.
1st list of 99,000 is in Sheet1 A1-A99000 and the second list is in Sheet2 B1-B150.
The caveat is that in column A there is additional words in that cell so you can not do a simple vlookup, because there may or may not be an exact match.
I have been stuck for hours and the best i can get is to use the match function but it is not working because it is not exact. Last thing i want to do is use the CTRL F key to lookup all the words.
I'm trying to set up a "simple" calculator for a co-worker who sells memberships at a country club. The purpose of the calculator is to help show an individual why it makes financial sense to join now (mid-year) and have monthly dues waived for some number of months as opposed to waiting until December and joining right before the membership deposit goes up in January and be required to pay dues from day one.
I'm not exactly sure how to build an NPV formula for the 'join later' column (cell C11). The assumption I am using is that the prospective member would take his money and invest it at the assumed rate and then use those funds to join and pay dues at the later dates. I've got a formula in there right now, but it is only giving me their net out of pocket expense, not the net present value of those funds.
here is what I have so far: ....
How can you calculate the present value of a negative value in excel?
View 2 Replies View RelatedI've created a spreadsheet that I use to calculate weights of steel members for my job for estimate purposes. I'm trying to figure out how to save some time by not having to enter/copy/paste formulas every time I do takeoff for an estimate.
The bold green cells in column K are always linked to the yellow "Part Heading" above in Column A. As I do my estimates, I copy and paste a "Part Heading" with a "PL/Member" formula below for the next piece I'm working on. Every time I paste a new "Part Heading" with a "PL/Member" formula, I must change the formula in Column K to link to the next cell up in column A with a value present. In the screen shot, cell K/90-K/96 are linked to cell A/89. Cell K/83-K/87 are dependent cell A/82; K/74-K/80 are dependent on cell A/73, and so forth. Since each "Part" varies on total number of "Members", I can't seem to write a formula that will stick when copying and pasting.
My question is: Is there a way that I can automatically link each cell in Column K to the next cell up in Column A with a value present?
how to use COUNTIF to count up the number of times something appears. What I want to do is add up values if an H is present in the cell with the value eg. if 3 cells contained:
H, 3.5
7.5
H, 3
...then 6.5 would be returned.
I have a list of dates in column A which are in order but non consecutive. I am creating a new table with all consecutive dates. I need a formula to check whether a date is in column A and then give out a number in the corresponding column to the right. I am able to use Vlookup for this but if the date is not present in the first coumn it returns an N/A response. Is there a way of returning zero if the date is not present?
View 5 Replies View RelatedI have a command button that is linked to a sub procedure that sends emails by sales rep. When the information is separated by region I want to disable this option. So I have the following sheets that may or may not be present: "Southeast", "Midatlantic", "South East", "North East", "Central West" "Southwest" and "Mid Central". If one of these sheets is present in the work book I would like a simple:
Code:
msgbox "You cannot distribute email whens sales have been grouped by regions", vbcritical
Not sure of the logic I need to include for this specific case.
I'm currently trying to sum across a row only if certain text is present. So, if A1 has CT8 and A2 has 5 and A3 has CT5, I want to just sum the cells with CT in them. I've already worked out how to strip the CT from the numbers in the sum, but not how to specifically only sum if CT is present. And of course, the sheet uses other letter/number combinations that need to be separately summed.
I would like to do in the column next to it is where there is a date add 45 days, and where the cell is blank just leave the cell next to it blank.
I've have tried several methods, as below and I either get an error message or the cell doesn't return what I would expect.
=IF(C3:C13>"",45,0)
=IF(ISNUMBER(C3:C13)+45
=IF(C3:C13,0,45)
If you want to create a FOR...NEXT loop for values that are present, how would you go about it? Sometimes you may have 1,2,6,8,12,13 and the next 2,3,5,6,7,10,11,14.
It may be a number between a certain range, but the sequence and step will change every time (for trying to get a Auto-filter to cull a list based on descriptor)
Which formula should I use to look for the present value factor if I have the interest rate and the # of periods. attached file.
I have 2 columns with approximately 200K lines of data. There are only 84 and 823 unique values in each column respectively. On my report.
Column A is essentially a customer list, column B is a product list. I am trying to find out how many unique/individual products a customer orders. I have a separate column that has the customers in it (Column G), and in Column H, I want it to count the unique products ordered by customer.
I changed some of the data, but essentially what I attached is what I need.
why match turn out NA but data is there ,I'm not very good in this or this formula don't really work,
View 5 Replies View RelatedI have to use data tables to calculate Net Present Value (NPV) by altering specific variables.
How to use the data tables function under the what-if analysis.
My sheet and calculations are all set up
But for the life of me i cant figure out how im going to tell excel that im changing for example wage costs and not fixed assets when trying to asses the NPV.
Ive done it manually by altering the figures on the Inputs and Data Sheet.
My NPV calculation is on the Workings and calculations sheet.
The data tables i have done manually are on the Inputs and Data sheet.
I am looking for a way to ensure that all the leading zeros in one column are present without having to format the column cell be cell. Is this possible?
I have a spreadsheet with 6 sheets. There are 3 sheets that have information regarding the soil, geology and drift deposits of an area. The other 3 sheets relate to these sheets to display the information for each area. The way that the spreadhseet is set out pretty much explains what i am trying to archive. Basically I want each sheet to say whether or not the rock, soil or drift type is present for each area. And if it is present to calculate the percentage of total area. I have created a pivot table for each of the info spreadsheets - but need it to look like the "table" sheets, because it needs to be added to a report. Any questions and i'll try and answer them , but it's quite difficult to explain in words what i am trying to achive.
View 4 Replies View RelatedROW 1 - list of headings
ROW 2 - blank cells that may contain an "X"
At the end of Row 2 I want it to find out which is the last column with an "X" in it, i.e. F2, and return the title of that column, i.e. F1.
highlight cells that are present in all selected columns. For example, if i had a file with 6 columns in it, I would like to highlight the cells that are present 6 times (once in each column).
View 10 Replies View RelatedColumn A has 200 names.
I want the names John, Tom, George, Frank and BillyBob to be filled with blue.
I want the names Sally, Mary, Kathy and Gertrude to be filled with pink.
I want the names Ebenezer, Vader and Fagin to be filled with black...and so on.
What is the simplest way of see if after a filter has been applied with a criteria, whether any data is visible using VBA?
View 2 Replies View RelatedI have a situation where i have headers in a file but just because there is a header does not mean there is any corresponding data in that col.
I was keying off if just the header was present then i need to do "something" but now i need to key off two things:
1) Header present
2) If there is anydata in the col when a header is present.
Question: How to use vba to determine (quickly) if there is any data in a given column.
How do I delete a worksheet but ONLY if it exists, if it doesn't exist, ignore the command. I'm afraid to mess up the other code in the macro/sub, so here it is...
View 2 Replies View RelatedI have a spreadsheet with many many columns...the top of each column has a string (it's a bird name). Below in the same column are various blank spaces or letters (defining species abundance, if there), example:....
View 2 Replies View RelatedI have a dropdown list in A1 of sheet1 that is populated by a dynamic named range beginning on A1 of sheet2. I also have a dynamic named range beginning on C1 of sheet2.
I want to create an IF/THEN function that checks if the value currently present in cell A1/sheet1 is also in the range at C1/sheet2. I have achieved this using lengthy OR() functions, but I want to get away from this.
So I am trying to write a bit of code that will insert a number in sequence in column A if there is data in column B. I need the column to be numbered 1-### if column B contains any data. I not been able to find this code anywhere, just delete if or count if statements that do not give me what I need. I think I have a few pieces of the code (e.g. Active Cell Offset) but can not seem to fit together in the correct order.
View 3 Replies View RelatedIs there a way to export as PNG all the graphs present on a spreadsheet? I have several excel files and each one has around 20 plots...I can export one by one but this is really difficult. I use mainly the latest mac version but PC is also available around here.
View 3 Replies View Related