I have a work book with 32 sheets. The first sheet is Monthly sum up and the rest of the sheets are the transactions of different dates. for Eg. sheet 1 is for first date of the month and similarly upto 31 days. The customers purchase commodity from me and I write their names in the column A and the value in the column B.
On day 1 mr X is the first customer and I write his name in the cell A2 and the value of goods purchased in the B2 cell of sheet 1 but the other day some other customer comes before him and so his name may slip from A2 to A19 in the next days sheet. Similarly on third day he doesn't come and his name is not in the 3rd sheet. So to sum up the name of the customer my vary in different cells in different sheets. I want to sum the value of the goods taken by the customers in the main monthly sum up sheet.
I have merged 336 individual spreadsheets into one book, now I want to merge the data in all the sheets into 1 individual sheet. All the sheets have the same size and range, I need to copy a constant range(row,column) from all the different sheets into one.
I have 20 sheets in my workbook, I know if i use the following code, that it sums all of the values AC10 across my 20 sheets.
=SUM('1:20'!AC10) However, is there a way that I can show the value of AC10 in each sheet without having to manually select or edit the cell in the function/formula?
I have a workbook with 11 sheets. In 10 first sheets i é use function PRODUCT(B35;$H$2) to calculate the values in each cell.
Then i tried to get the summation of 10 sheets in 11th sheet for every cell as following but i keep getting zero as a result instead of the summation: Sheet11!B5 =Sheet1!B5+Sheet2!B5+Sheet3!B5+Sheet4!B5+Sheet5!B5+Sheet6!B5+Sheet8!B5+Sheet7!B5+Sheet9!B5+Sheet10!B 5.
i am going to have 25 sheets for subcontractors in a construction magement workbook. i want to delete 7 cells on each of the 35 sheets. they will all be in the same place on all sheets. ex c7 g18 e14 etc on all 25 sheets. i was wondering if there is a way to create a function that can perform this operation.
I will like a macro to compare row one to row one on sheet 2 column A. If the number is the same then it will compare from the same row column K from sheet 1 and column m from sheet2. If it is less then copy to sheet4 and highlight in red, if more than highlight in blue and if equal green.
Data is input onto sheet 1, this is copied into sheets 2, no real problem there. However i need to enter data onto sheet 3 and if that data appears on sheet 2 i want the line, not just the cell to be coloured on sheet 2. The data on sheet 3 will not be in any specific order in the column
I have tried conditional formatting but keep getting a message to say it cant be done and offering a formula which i dont understand.
I have done alot of research in the formula realm but haven't ventured into the VBA coding side of the house due to the inexperience. I'm having issues w/ a particular calculation and hoping you all can help me out.
I hope I can explain this well.
- I have a total of 31 sheets (1-31) - In each sheet there is a series of number (B16:L16) - I want to take the average of all these numbers
I'm able to get the average of each sheet then take the average of those but that is not what I'm trying to do because of the following problem.
Let say in sheet1 that 5 cells are filled from the series (B16:L16) but on sheet2 there is only 1 cell filled and on sheet15 there are 10 of the 11 cells filled.
When you take the average of each sheet than average that it will not come out with the correct calculation. I'm trying to average ALL numbers combined from Sheet1 to 31 from cells B16:L16.
How can I do this. I have tried several solutions but nothing working properly. This is the one I have tried but doesn't work properly:
I have a master tab that has data grouped as follows
Column 1 title of a section (written once then left blank for the rest of a section)
Column 2 numerous descriptors (each in a new row)
Column 3 The response to the descriptors in column 2
I want to link entire sections from column 2 and 3 into different excel files so that if I make changes to column 2 or 3 in the master tab, the changes are made to all tabs. If there is a way, I'd like it if I add a row to the master, it is automatically added to the others.
is it possible to provide a formula to match as per the uploaded file. Data in Sheet one columns A and B, compared to what is in Sheet two column A I have a lot of data, and only need a Y for yes, and N for No as a result.
Applying my indirect formula to the rest of the cells in my sheet properly.
I am getting the values of cells in other sheets, using INDIRECT to use cell values to name the sheets. Here is an example of how the cells are now...
A B
1 Sheet 1 =(INDIRECT("'"&A1&"'!$A$5"))
[Code] ....
The formulas in B1 are going to return the value of A5 in "Sheet 1" (or whatever value is in A1) and B2 will return the vale cell A5 in 'Sheet 2. The next line will be the value of A6 in Sheet 1, and the next will be A6 in Sheet 2 and so on.
So I have quite a few rows to get through I want to be able to autofill so it looks like this...
A B
1 Sheet 1 =(INDIRECT("'"&A1&"'!$A$5"))
2 Sheet 2 =(INDIRECT("'"&A2&"'!$A$5"))
[Code] ......
As it is now, when I autofill, it autofills the Sheet name part of it. It is looking like this...
A B
1 Sheet 1 =(INDIRECT("'"&A1&"'!$A$5"))
[Code] ......
Excel isn't intuitive enough to know I want to move cells in the referenced sheet.
I am trying to find a way to create new sheets in a workbook that when created will reference a new row on a data sheet in that workbook. Each workbook could end up containing varying numbers of sheets (anywhere from 2 to 150+) based on size limitations. There are some lookups and referenced data on each sheet that would be copied as is and not reference the data sheet. I have not included these in my example.
I'm trying to avoid having to change each cell's reference manually for all of these sheets (which is one of the solutions I've come up with so far). My current method of creating these workbooks does not involve the data sheet and each workbook starts off with data only on sheet1. I select and copy that sheet into sheet2 and every subsequent sheet. I want to incorporate the data sheet so it can be filled out by someone else and copied into that tab to populate all of the data sheets.
I have 5 sheets, each sheet has 1 column of data (column A). I want to find the values that exist in all sheets. I will gather all values in 6th sheet, and I want to write "YES" in column B next to value that exists in all 5 sheets. How can I do this? For example:
The cells I want to fill are in column V in sheet 2. ie v10, v11, v12, etc.
On sheet 2 column d contains an item count and V is the count of that item.
Then on sheet 1 column ranges (B4 To B49) (G4 To G49) (L4 To L49) (Q4 To Q49) contains the relevant item code to column D on sheet 2. Also on sheet one the count section that I want copied into V on sheet 2 which is columns D,I, N,S 4-49 etc..
My issue is that sheet 1 is constantly rearranging and sheet 2 is in numerical order of column D.
So how do I get sheet 2 V10 to populate by searching for the item code of D10 on sheet 2 in B,G,L,Q of sheet 1 then once located to then copy the contents of the number from D,I,N,S that works with the item list?
Then once done this that I can copy the formula all the way down column V in sheet 2?
I have a sheet that needs to pull data from diferent cells and sheets at the same time. If I have to have all the data on one sheet I can change that as this sheet isn't in production yet.
On Sheet1 cell D6 needs to be able to get data from Sheet2 Cell C2 or Cell N2 or Cell Y2. On Sheet1 cell F6 needs to be able to get data from Sheet2 Cell C3 or Cell N3 or Cell Y3.
While doing the above it this also needs to be accomplished.
On Sheet1 cell D9 needs to be able to get data from Sheet3 Cell C2 or Cell N2 or Cell Y2. On Sheet1 cell F9 needs to be able to get data from Sheet3 Cell C3 or Cell N3 or Cell Y3.
Is Indirect able to do this or do I need to use something else? I am trying to do this without macros if possible.
is it possible to keep the same active cell when switching sheets in the same workd book. I am in sheet1 and cell C15 is my active cell. When i move to Sheet2 it either defaults to A1 or the last place i was in this sheet. I guess it would be the Worksheet_activate() and deactivate function but not sure
I have to make something what will import data from other documents.
I made a template report. Now I need a button or something what will open a file (a want to choose it) and import some cells from 5 sheets (not whole sheets) to the contfirmed cells in the template. All of the files are the same (i make inspections of some stuff) only data is different.
It will save a lot of time, cuz i have about 300 something documents from which i have to make the reports.
I have two spreadsheets, one is a Log Sheet for invoices that we have been printing out and filling in my hand throughout the day as our field technicians complete their work. The second spreadsheet has a sheet for each technician where we manually input the technician's invoice numbers and payroll for each job completed on that day.
What I'd like is a way to combine these spreadsheets so that instead of printing and filling in the first sheet by hand, I could input the invoice data and have it automatically populate the cells for the appropriate technician.
This is the running log we keep throughout the day:
2rp76ky.png
Then at the end of the day, we painstakingly transcribe the data for invoice number and payroll onto this sheet for each technician:
4vg6dk.png
What I want is for Excel to automatically fill in each technician's payroll log with invoice number and payroll time as we fill in the running log throughout the day, is this a doable thing?
Step 1: I have data coming from a data dump which is placed on the "Summary" sheet of my excel workbook. I need to take column A (beginning at A8) and create a new sheet (based on "Template" located in the same workbook) for each name in column A.
Step 2: I need to place the name of the new sheets in cell C4 of each sheet so I can do a lookup using C4 as my lookup_value, so this can't be a formula like "=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)" because lookup doesn't function properly.
Step 3: Delete "Template", so that the only things left are the "Summary" sheet and the new sheets.
Each department will have their own workbook with a "summary" sheet and different sheet names although they will all start off with the same data dump and will all be in the same format. Each department could also have a different number of new sheets added depending on information from the data dump.
I found a utility (ASAP Utilities) which will do the steps I need done, but it won't record in the macro.
Budget time is fast approaching and each department needs their worksheets.
I am looking for some code to scan my sheet names and delete all the sheets that don't have names corresponding to the values within a cell range contained on another sheet in the workbook. For example, if any sheet name, excluding a few reserved sheets, doesn't equal one of the values contained in cells A2:A23 on sheet "XYZ" then delete it.
The best solution I could come up with using my experience was to loop through each sheet and compare the name to each cell in the named range, but if it's possible to somehow declare the named range so that each sheet only has to compare to it that would be ideal;
I am using a spreadsheet for a weekly football league. I would like "Sheet 1" to have the picks for the current week with the point total at the bottom for each team (I have that set up), but on a different "Sheet" I would like to keep a running total for each team. I know how to creat a formula for the total points, but how do I get that formula to attach to the corresponding column on the same row.
EX: A B "Team Name" "Formula for total points"
I would like A and B to stay connected when I auto arrange them in numerical order.
I'm building a table that references a dynamic range on a pivot table. So far I've gotten the function to return the address on the pivot table but it wont return the value. I'm doing this so that I can both refresh the two source data sets and summarize (pivot table) and also graph these two summarized data sets since you can't graph two different pivot tables together.
This is my code so far: Iferror - To return zero if the source data doesn't exist yet Address - To concatenate the match functions Match - to find the row and column numbers on sheet "B" that match the x,y call on the original sheet "A"
=IFERROR((ADDRESS((MATCH($A2,'Install Data Pivot'!$A:$A,0)),MATCH(B$1,'Install Data Pivot'!$4:$4,0),1,1)),0)
Returns $B$5 since this is the cell where the information lies.
I've tried just adding the sheet name like this...
=IFERROR('Install Data Pivot'!(ADDRESS((MATCH($A2,'Install Data Pivot'!$A:$A,0)),MATCH(B$1,'Install Data Pivot'!$4:$4,0),1,1)),0)
But that doesn't work. I've also tried using different variations of Indirect and Index but I still can't get it to reference the other sheet.
I have twelve sheets that each have a total score on cell G10 that I want to have copied to a thirteenth sheet in a specific column (F5-16). What would I have to do to make that happen? The G10 cell is an auto-summed cell, if that makes a difference.
