I 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.
The file will the same file (ABC.xls) BUT the user might want to change it by typing a new name & Extension in the first Inputbox.
2. A second InputBox (Drive & Path) should now Pop-up.
If it will be left empty - the search should take place throughout ALL the installed HDs.
If the user decides to minimize the search range he/she will type a Drive & Path, the search should reduce/constrain itself to the specific Path ONLY.
My task is to show its Path location in a MsgBox (or to show "file not found")
It might be a good idea if the code can be extended beyond presenting the Path to: 1. Open the Windows "Explorer" (or "My Computer") in the specific Path with the file being selected (marked).
2. Opening that file, in "Excel", upon finding it.
A 3 section, VBA, code will be more than appreciated.
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.
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.
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.
I'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:
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?
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.
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)
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.
I 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.
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).
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...
I 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:....
I 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.
Is 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.
I am going to convert different costs during life time of a product to Net Present Worth (NPW), consisting Maintenance and Rehabilitation costs (M&Rj) and Salvage Value (SV), according to the following formula: [URL] .......
SV occurs only once at the end of life time, it's NPW in Excel can be calculated as following:
=PV(i discount, AP,0, SV,1)
But M&Rj costs occurs J time during life cycle, and formulating manually their mathematical formula most times takes much time. Exactly what should I write in the cell to get their total NPW? In other words, how can I get the following formula by Excel functions: [URL] .......
Reminder: NPW= FV( 1/(1+i Discount))^n FV: future value i Discount: discount rate n: numbers of years (periods)
I have 7 columns (May 1st to May 7th) and 10 rows.
Each column needs to contain the following entries T1, T2, T2, T2, C2, C2, C2, C2, C2. These data will be entered manually in no specific order.
I want to create a macro that checks if the entries are present. If one or more is not present I would like to have the missing entries be placed in the cells A10 to A20.
