Including Worksheet Tab In Formula

Jul 8, 2013

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?

View 3 Replies


Copy Entire Worksheet Including All Macros And VBA Code

Dec 16, 2008

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?

View 6 Replies View Related

List All Filenames In Directory On Worksheet (including Subfolders Contents)

Sep 10, 2011

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?


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

View 9 Replies View Related

Copy And Paste Entire Worksheet Including Form Controls?

Dec 4, 2013

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 Related

Disable Paste Function (Including Drag And Drop) On Excel Worksheet

Jun 27, 2014

How can I disable the paste function on an Excel worksheet, including drag and drop?

Preferably I still want to retain the ability to copy.

View 1 Replies View Related

Including 0 In Formula (#DIV/0!)

Jan 31, 2010

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.) ...

View 9 Replies View Related

SUMIF Formula Including OR

Jan 30, 2009

How do I modify my formula (below) to include an OR statement?


I need something like this:

=SUMIF(I4:I20,"Core" or "Existing",G4:G20)

View 9 Replies View Related

Averageifs Formula With And / Or Criteria Including Odd / Even

Feb 7, 2014

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 Related

Including Destination Cell Value In A Formula

Mar 28, 2009

There 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 Related

Comparison Formula - Including Inherent Errors

Oct 5, 2009

I 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"

View 4 Replies View Related

Formula To Show Top Scores - Including Ties

Jul 17, 2008

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.

View 9 Replies View Related

Formula To Work Out Cost Including Multiple Discounts

Mar 31, 2014

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.

View 2 Replies View Related

IF Formula - Get Average Of Only Values In That Range Not Including Blanks

Nov 21, 2011

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).

View 3 Replies View Related

Formula To Return Multiple Criteria Including Stretch Target

Jul 8, 2014

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.

View 8 Replies View Related

Formula For Obtaining List Of Unique Values But Not Including Values Which Contain IC

Aug 14, 2014

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
CC |
DD |
DD |
IC |
IC |

View 14 Replies View Related

IF Statement Including BETWEEN

Mar 31, 2006

How can I write an IF statement that evaluates whether a cell's value is
BETWEEN two numbers?


A1 = 89.99

I need a statement that evaluates whether A1's contents are between 80.01
and 90.00.

View 14 Replies View Related

Including Word ZERO In IF Statement

Feb 26, 2014

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 Related

Subtotal Including First Row Of Next Group

Mar 9, 2014

I'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‎

View 2 Replies View Related

Functions Including Several Sheets

Aug 13, 2007

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.

View 13 Replies View Related

Averaging Numbers But Not Including Zero

Sep 11, 2009

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 Related

How Do I Get An Average Without Including The Zeros

Jan 27, 2006

I 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.

View 10 Replies View Related

Using Countif But Not Including Duplicates?

Jun 8, 2013

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 Related

VBA – Including Date In Filename

Mar 22, 2007

I’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")

View 9 Replies View Related

Counting Without Including Duplicates

Sep 10, 2008

How do I count the number of air handling units, "AHU" column without counting the duplicates........

View 9 Replies View Related

Sort Column A Including Row 1

Aug 18, 2009

I 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 Related

Sum Of Column Including Blank Cells?

Feb 20, 2014

I 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

View 5 Replies View Related

CountIf Contains Including Cell Reference?

Apr 29, 2014

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.

View 4 Replies View Related

VBA To Copy / Paste (including Formulas) To Next Available Row

Mar 20, 2014

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 Related

Including Environment Username In Cell

Feb 1, 2006

I have searched the forum several way to resolve my issue and the only solutions were as follows:

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.

View 13 Replies View Related

Including Two Ranges Of Cells In An IF Formaula

May 25, 2006

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.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved