Hi All,

I have 6 sheets in a workbook. 5 have results at A51. In 6th sheet, I want to write a sum formula adding A51 in all those sheets. I can write =sum('Sheet1'!A51,'Sheet2'!A51,'Sheet3'!A51, ........).

But I know there is something much better way.

Please help me.

Raj

I have 6 sheets in a workbook. 5 have results at A51. In 6th sheet, I want to write a sum formula adding A51 in all those sheets. I can write =sum('Sheet1'!A51,'Sheet2'!A51,'Sheet3'!A51, ........).

But I know there is something much better way.

Please help me.

Raj

- Sum of all sheets not hidden
- Can i have a list of all the Sheet names in one Total Tab
- Sumproduct-give the sum of all the instances
- Find the sum of all the cells in a column which have a certain value to the left
- Freeze only the first row of all the sheets.
- =sum of all cells of a certain color??
- Sum up range of cells through all the sheets
- Macro to do the sum of values from two different workbook for all the sheets
- To do a sum function across all the sheets.
- Execute A Set Of Operation On All The Sheets In A Workbook
- How to total of all the cells in a range beneath the merged cell
- Formula to get the sum of all work hours
- Adding values of multiple excel sheets and populating the master sheet
- Sum of cell reference in multiple sheets
- Summing of numbers between sheets for same column values
- How do I find the sum of all digits in a number?
- Sum of all columns that match criteria of another column along the same row
- Unhide all the sheets at once?
- Sum of cells which use a dark red font only
- How: SUM of all rows contained word "Pen"
- How do you copy the names of the sheet automatically?
- Summing Multiple Sheets Per Employee
- Saving all the sheets as .txt
- Return a list of all the worksheets?

I currently have a total sum in the frontpage of all the sheets in the workbook. It's something like:

=SUM(total:end!F68)

total being the frontpage and end the (hidden) page at the end which stays always behind newly entered sheets.

But I use a filter (vba) to hide/show sheets depending on their name.

Now is there a way to make a (dynamic) sum of all the sheets shown/not hidden?

I want to have the names of all the sheets in the Total Sheet in the cell B2

B2 A

B3 B

B4 C

Now if i add any sheet between Sheet A and Sheet B it should immediately show in the list similarly if i delete any sheet then also it should ommediately show , it should also maintain the order in which the sheets are added.

Is this possible.

Please try to explain as well as try to work out in Vb as well as Excel Programming. and attach an example.

I Have this formula (kindy donated by this forum):

=(SUMPRODUCT((ISNUMBER(MATCH($J$5:$J$21,'Sheet1'!$A$5:$A$21)))*(F36='sheet1'!$B$5:$B$21)*('sheet1'!$ C$5:$M$21)))

the problem is that it only gives the sum of the array for $C$5:$M$21 which matches the first value of $J$5:$J$21, and there is often more than one match for the values in $J$5:$J$21 against $B$5:$B$21.

Is it possible to re write it so that it would give the sum of all the instances in $B$5:$B$21?

Hope that all made sense

Thanks

For instance:

9/2 | 123 | Ralph | 1.02

9/2 | 123 | Ralph | 0.34

9/2 | 123 | Ralph | 2.30

9/2 | 123 | Fred | 6.70

9/2 | 123 | Fred | 1.34

----->>>

9/2 | 123 | Ralph | 3.66

9/2 | 123 | Fred | 8.04

I have around 200-230 sheets in an excel file.

I want to freeze only the first row of all the sheets.

i want a vba code to do the same.

Thanks in advance for any help.

Best regards,

Me.

For example, have the sum of all Red cells in sheet 1 in cell A1 of sheet 2, sum off all Blue cells in sheet 1 in cell A2 of sheet 2, etc...

I found a cool way to sum up range of cells (e.g. A2:E2) through all the sheets (except the first sheet were the function is).

I write:

VB:The problem is that as soon as I hit the ENTER the symbol '*' become the actual range of the current sheets. e.g. sheet2:sheet4.[/COLOR]If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:If I add a sheet it does not dynamically included.=SUM(Sheet2:Sheet4!A2:D2)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

How can I make the symbol '*' stay?

I thought to use the indirect function but I could not get the right syntax.

I wrote '*' in cell A9 and then wrote:

VB:Any advice?=SUM(INDIRECT("A9")!A2:D2)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thank you,

Tamir

I am new to this forum...& to macros n VBA as well

I have 2 workbook with a.xlsx & b.xlsx with almost 25 sheets each...All the sheets are in same format with same name except the first one for both the workbooks. I want to add the values(P4 to BE323) from both the sheets and print the result in the respective cells of new (third) workbook named add.xlsx with exactly the same sheet name for all the sheets.

For the first sheet say 1 & 2 for both workbook a & b respectively...I want the sum for their values in add.xlsx workbook in the 1st sheet named "main".

Also I want the code as dynamic as possible.

Please let me know how to go ahead with this...

Thanks in advance... :)

Help me to develop a Macro in which we can do a sum function across all the sheets that i have on the work book.

But the sum function should restrict itself to the maximum no. of rows that each row has got on which the data is present.

Lets say that we start the calculation on the first sheet.

Shankar.

I have a workbook with n number of worksheets namely 2, 4, 5, 6, ….. Etc. I want to perform a set of operation on all the sheets, the problem is that the sheet nos are random, and some of them maybe missing too. so pls help me to execute a Loop in such scenario.

For eg , I want to delete columns A to D in all the sheet.

there is a simple solution to the but for the life of me i can't figure it out

PROBLEM:

set up the spreedsheet

Right from A1 to D1 select all four cells and merge them. In the merged cell goes the title Jul-05 say (it's a date at any rate)

In A2, B2, C2, and D2 goes Wekk 1, Week 2, Week 3, and Week 4 respectively

in A3, B3, C3 and D3 go 5, 10, 15, and 20 respectively

right now imagine that all along those three rows it goes from Jul-05 til the end of the year in the same format - obviously some months have 5 weeks in them (as in the attached file)

the formula (AKA PROBLEM)

what I want to do is anywhere else on the sheet, let's use cell B10 for the sake of argument to enter our forumla

In A10 ( the cell next to it) I would like to be able to enter any month in the same format say - Jul-05

and in B10 it returns the sum total of all the cells in the range beneath the merged Jul-05 cell

so ther'es gonna be a lookup in some form but I just can't get it to define the right range for each month

I am new to excel.

my excel version is 2003

I have 5 excel sheets sent by 5 customers, the columns are as following

ProductCode Description QtyOrdered

I need to merge the 5 sheets and produce 1 Master sheet based on the quantity ordered for different products. The products ordered by customers are not in specific order.

For example

Customer1

P001 XYZ 20

P002 BBB 90

P006 VVV 8

Customer2

P001 XYZ 10

P009 NNN 3

P006 VVV 5

My problem is if customer 1 has ordered product X and customer 2 and also ordered product P001, the Master sheet should show the product P001 as the sum of all the customer excel sheets

As per my example the Master should show product P001 as 30

Please help me is their any way to get this result automatically

Any help is much appreciated

Is there a formula for doing this quickly?

system. Each system has multiple items in it with different yearly

quantities.

For an example, in sheet1 I have a table with 3 columns A- is the item#

(00010001), B- is the system that Item # is placed in (BB001), C- is

the quantity of items in that given system (111000). In sheet 2 I would

like column A to be each system (BB001-BB100) and column B to be the

sum of all of the different items #'s in that system (lets say system

BB001 has 3 item #'s 00010001, 00010002 and 00010003, all three of them

have a quantity of 111000).

What function or formula would give me the correct sum of 333000 for

system BB001? How would I make this dynamic so that if 2 more items

were added to system BB001 their quantities would be included in the

overall sum of quantities in that system?

For example if i have the number 1234, i want excel to do the following

calculation:

1+2+3+4 and return the value 10. I need to know how to do this for any

number of any size.

I would like to get the sums of all columns in B, that match criteria from column A, and then show that number. Column A is a date in the form of MM/DD. Column B is a numerical value ranging from 1-3.

So basically Sum of all Column B where the corresponding A value is the criteria e.g. 11/8

Thanks for the help I'm still somewhat new to excel and this forum has been a great benefit.

I'm looking for a macro that gives me an option of unhiding all the sheets at once or a group of them (check box for sheet names with select all option)

I am trying to produce a spreadsheet that shows a column of figures for sales for each invidual person in a specific company. At the moment the spreadsheet sums the total sales amount for each company and lists it in dark red beneath the sales amount for each individual. What I need to do is produce a grand total at the very bottom of the list which produces the sum of all the entries which are written in the dark red font. I pretty confident that this can be done but I have no idea how to go about doing it.

I have attached an example of the type of thing I need to do to this post. The spreadsheet needs to be quite versatile because the people who will be using it will need to add/remove companies and sales figures as necessary and so I am trying to make it as easy as possible for them to add/remove stuff without fudging up the entire sheet and making all the calculations incorrect.

Grateful for any help you guys can give me.

James

I need your professional help to create formula which will be:

SUM of all rows in column B which contain the world "Pen" in column A.

huhh.

Will really apreciate your advice

Ivan

thank you in advance

I am having a problem. I have 20 sheets set up exactly the same with employee payroll and another sheet that lists all of the unique names and id's. I need to get a total sum of all the sheets per employee. Please note that some employees can appear multiple times on a sheet.

Thanks in advance Stephen.

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=

ABCDEFG1last namefirst namePositionIDGenderDeptamount2JonesJohnX89891MF410$1,830.003JohnsonMikeX89892MD167$2,220.004AllenRayY89893MF410$675.005ThompsonDavidY89894MG43A$300.006JonesAlZ89895MHYJ$195.007PhillipsTomC89896MT567$150.008SmithBenV89897M67L$240.00Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Yesterday this macro was working, today not.... I am getting crazy! :-)

Thanks in advance for all the help.

The aim of the macro is to save all the sheets in .txt files named as the original sheets.

Sub save_as_txt() Dim nome As String Dim wknew As Workbook Set wknew = ThisWorkbook With Application .ScreenUpdating = False .DisplayAlerts = False End With Dim cartella As String cartella = InputBox("Please insert a name for the new folder.") MkDir cartella For x = 1 To Worksheets.Count nome = Sheets(x).Name Sheets(x).Copy ActiveWorkbook.SaveAs Filename:=wknew.Path & "" & cartella & "" & nome & ".txt", FileFormat:= _ xlUnicodeText, CreateBackup:=False ActiveWindow.Close Next End Sub

*** ok, the new folder is created in the default excel folder (documents). I think that the solution is to create the folder in the same path of the active workbook? How can i do? Again, thanks in advance and happy new year!!

The only way I can think of doing this is w/ VBA, but i'm wondering if anyone can think of a way to do it w/ just formulas.

No luck finding an answer? You could always try Google.