I have a workbook with numerous worksheets. Each worksheet is similar to all the others, and I want to be able to (for example) lookup the values in cell C10 for each worksheet and record the value in a summary worksheet.
The name of each worksheet is in Row 1. Is there a way to reference the sheet name using the values in row 1?
I need to copy my worskheet multiple times to create about 10 of the same worksheets within a workbook. I know if you click in the very most top left hand corner of the worksheet, then click copy, go to the new worksheet and click paste, it copies the whole page, but it does not copy over the VBA Code.
Also, for some reason, it does copy over my macro buttons, but does not put some of them in the right spot?
I was wondering if there is a way to list all filenames from a directory on a worksheet including the files located within it's subfolders? I found a bit of code at [URL] that is as follows, but I can't seem to get it to read the files in the subfolders... Also, would it at all be possible to hotlink each entry so that the user can double-click the name in the sheet and open the file?
VB:
Sub ListAllFile() Dim objFSO As Object Dim objFolder As Object Dim objFile As Object Dim ws As Worksheet Set objFSO = CreateObject("Scripting.FileSystemObject") Set ws = Worksheets.Add 'Get the folder object associated with the directory Set objFolder = objFSO.GetFolder("C:") ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & "are:" 'Loop through the Files collection For Each objFile In objFolder.Files ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name Next 'Clean up! Set objFolder = Nothing Set objFile = Nothing Set objFSO = Nothing End Sub
I need to make several exact duplicates of an existing worksheet which includes several form controls. Basic Copy/ Paste the worksheet works well but row heights are different and hidden rows are unhidden. Is there a way to make an EXACT duplicate ?
View 2 Replies View RelatedHow can I disable the paste function on an Excel worksheet, including drag and drop?
Preferably I still want to retain the ability to copy.
I am trying to create a marksbook that will ignore blank cells and calculate a percentage based on the amount of tasks completed.
So far I have managed to get it all working EXCEPT if I enter 0 as a value.
For example, suppose there are two tasks, both of equal value. If a person was to get 100% for the first task, and 0 for the second task, you would expect the total to be 50%. I cannot get my formula to recognise that a 0 has been entered.
(Cells F5:T5 are what the task is out of. Cells F6:T6 is the weighting. I want to be able to enter a 0 in cells F8:T8 to include it in the final percentage.) ...
How do I modify my formula (below) to include an OR statement?
=SUMIF(I4:I20,"Core",G4:G20)
I need something like this:
=SUMIF(I4:I20,"Core" or "Existing",G4:G20)
I have 3 columns of information. Column A will be between 1-6, Column B is between 1-10, Column C is the results that I need averaged. I need the formula to give me the average of Column C of all 1's in Column B that are even numbers in Column A. I have been looking on forums, and can find multiple criteria for ands, but can't see where I can do an and /or. I could do Column B = 1, Column A =2, but I need it to be OR =4 OR =6. I don't see any easy answer in excel for even/odds.
View 8 Replies View RelatedThere is a big range of cells with normal numbers (ex. 100, 150 .. etc), but I need to convert them in the following formulas that give the same numbers as a result: for example if the cell value is 100, I need to convert it in =if(iserror(100);0;100) and so on with all other values. Is it possible this to be done automatically for all cells?
View 5 Replies View RelatedI have two columns that I want to compare - but one of them is the result of a VLOOKUP - so it may actually contain the #N/A error - how can I compare them so that:
if col 1 agrees with col 2, then "yes" is returned
if col 1 does not agree with col 2 but the VLOOKUP in col 2 has NOT returned an error (so it's still a valid comparison) then "NO" is returned
if col 2 shows a VLOOKUP #N/A error then "NO" is returned
or, perhaps more straightforwardly:
Col 1 = ABC, col 2 = ABC then return "YES"
Col 1 = ABC, col 2 = DEF then return "NO"
Col 1 = ABC, col 2 = #N/A then return "NO"
I have a list of team members and their scores for team building events. I need to create a list of the top ten scores (including any ties). The list will have to show the top ten scores as well as any additional names whose score ties with one of the top ten scores.
I tried large and also a pivot table using the advanced but cannot get it to show the top 10 unique scores as well as any duplicate scores.
complicated formula for working out prices including applying discounts.
I'm looking for the correct formula for G6,G8,G10 and G15,G17 and G19
I think I have them correct(?) to include standard days and extended days but I can't make it work to include Half days. The formula gets too complicated for me to follow.
I am looking for a formula for the following:
If there is nothing is U6:W6 return blank
Otherwise sum the contents of Bf6:BH6 and divide by the number of cells that are not empty in the range U6:W6 (to get an average of only the values in that range not including blanks).
I'm struggling to come up with a formula that ill give me the following:
I have will have a row of data (cells A4:H4 on the attached example), in the respective cells below each value in row 4 I want formula that will give me a value of 10 if it sees 90% in the cell above reducing to a value of 0 if it sees 80% in the cell above. I think that there should be a relatively straightforward formulae for that, my problem is that I want to add a further criteria that says; if the value in row 4 increases above 90% and reaches 95% then I must return a value that is 10 for 90% but "stretches" to 12 for 95%. Anything below 80% should return a value of 0, and anything over 95% should return a value of 12.
1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).
I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)
2. To include all unique string values except those starting with the letters "IC"
3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.
Here is the formula I currently use which serves #1 (above):
[Code] .....
Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.
Example of desired results:
Column A | Column B
AA | AA
DD | CC
AA | DD
CC |
DD |
DD |
IC |
IC |
How can I write an IF statement that evaluates whether a cell's value is
BETWEEN two numbers?
Example:
A1 = 89.99
I need a statement that evaluates whether A1's contents are between 80.01
and 90.00.
How do you go about entering the word "ZERO" in an IF statement? Every time i try to add it to my formula it turns it into the number 0 which is not what i want because i want to differentiate between the two??
View 1 Replies View RelatedI'm running the subtotal function, but for some reason Excel is including first row of next group in one of the groups. The label I am subtotalling on is the result of a formula, but why would that make a difference?
See row 11 and 12 in the attachment : subtotal problem.xlsm‎
i can do the very very simple math formulae in excel, but now i have two excel files. Suppose the first excel file contains the numbers: x1 x2 x3, and that the second contains the numbers: y1 y2 y3.
i need to make a formula that includes x1 value and y1 value, then another one that includes x1 and y2, then x1 and y3, then x2 and y1... and so on.
I have a row of cells containing numbers, some of which are 0, how can i get an average where it averages all cells except the 0, as at the moment it is distorting my results.
View 3 Replies View RelatedI am using a formula to include all values greater than zero in my average which has worked fine until i tried to extend the parameters, then it gives me the Value? sign. Does anyone know another way to accomplish what i want?
here is what i am using now.
=average(if(BZ28:CM28<>0, BZ28:CM28, " "))
i was trying to make the CM extend to CS, but for some reason it is not working.
Anyway, i figure a different equation might be better than what i
have and solve my problem.
I have entered the following formula to add up a list of data. =COUNTIF(B7:B100,"*") This doesn't include gaps with blank cells and only gives me the total number of cells that contain text. However, some of the text is duplicated and I only want to count the total number of unique entries in the list.
View 6 Replies View RelatedI’m trying to save a new workbook with the current date included at the end of the filename, in the format yyyy-mm-dd.
I took a guess at the following but it doesn’t work - the filename comes out as 'Report39163' Can anyone help please?
ActiveWorkbook.SaveAs Filename:="\UsersDataTemp" & "Report" & Format(Date, yyyy-mm-dd) & ".xls")
How do I count the number of air handling units, "AHU" column without counting the duplicates........
View 9 Replies View RelatedI want a macro to sort Column A through B (Sort newest to oldest)that includes the first row. I can record a macro but it doesn't include row 1.
View 9 Replies View RelatedI want to create a 'capture all' formula that adds up cells in a column This column may contain blank cells in one column but when copied to another column, the same row may contain a value.
example I want the sum of A5 + A8 + A10 + A22 but in column A cell A5 may be blank and so may A22 but when the formula is copied to column B all the cells may contain a value
When I try to do it I receive a Value# error for columns containing blank cells I don't want to do a separate formula for each column
I have a sheet that calculates what items are on any given transaction - this works well. I now want another table that counts how many times a certain brand comes up.
For example one item on a transaction might be "TENSOR REG TRUCK BLK 5.0 x2"
I need the table to look at this and count if tensor is in it (sheet name: 'Sheet 1') but I want it to look at the list of brands and and reference that brand rather than type it out individually.
I need some VBA to copy row 2 then paste it (including formulas) into the next available row in the same sheet but then also clear the data in row 2 but retain the formulas.
View 2 Replies View RelatedI have searched the forum several way to resolve my issue and the only solutions were as follows:
PHP
Public Function UserName()UserName = Environ("username")UserName = Computer("UserName")End FunctionOption ExplicitFunction NetworkUserName() As StringDim responseNetworkUserName = Environ("Username")End Function
Problem is that I get a #REF! and #NAME? instead of the user names
I am using Excel 2000 (I can not upgrade, Government PC) plus we need it to support Excel 2000 - present versions.
I wish to query two ranges of cells. the cells are on two separate worksheets
in the same workbook, but do not know how to specify more than one range in
an IF function. i need the formula to look at both ranges for a particular
value the ranges are:
'Substance Use'!G9:G71 and 'Mental Health'!G9:G71
how to construct the fiormula.