It is entered in Column B of the sheet Round 2. Basically what I want it to return is the lower price from two different sheets looking them up by part number. Also I guess something I didn't think of is that the HD Final Sheet will not contain all the parts...while the other sheet Round 1 will have all. In cases where the part number is not found on the HD Final sheet I want it to put the price from the Round 1 sheet.
View 14 RepliesI'm trying to put together a spreadsheet that tracks disc capacity increases, affected by any incoming projects. I've managed to do so for one project, but would like to for up to 10. The way i've designed the solution (i'm sure there are far more elegant ways, but hey) is thus:
A forecast worksheet keeps track of a grand total, taking information from sheets P1 -> P10 (being projects 1 to 10). I am unable to figure a way to add up all the increases from all 10 project worksheets with one succinct formula. What I use so far is: ='P1'!C83+SUMIF('P1'!E82,"=2009 - Q1",'P1'!D82) ..................
I am trying to create a table which will search for a name throughout several other sheets. All of the same format and layout but with different data. I then need to display information in a different column of the same row of the name I'm searching, just like vlookup.. I also need to be able to search for multiple entries with the same name, so there may be 3 different entries for John Smith thoughout the few dozen sheets.
View 2 Replies View RelatedI'm trying to create a VLOOKUP that will return a value in 'Tracking' from 1 of 4 different sheets. If the value isn't found in sheet 1, the lookup should continue to sheet 2. If not found in 2, move to 3 and so on. In cases where there is no value, I would like to return " ".
Trying this:
=IFERROR(VLOOKUP(A2,'1'!A:P,2,FALSE),IFERROR(VLOOKUP(A2,'2'!A:P,2,FALSE),
IFERROR(VLOOKUP(A2,'3'!A:P,2,FALSE),IFERROR(VLOOKUP(A2,'4'!A:P,2,FALSE)," "))))
This formula returns values only in sheet 1. It won't continue to 2 and so on. Also, this will return a 0 for a blank value. If working as intended, 'Tracking' will show "g" in B2 and " " in C2.
I have an excel sheet that contains employee details (Emp. code, Name, Dept. & Shifts for the month) on one sheet (named Manpower) and their respective shifts on other sheets. The emp code in the manpower sheet should be looked up in the other sheets and the corresponding shift on the particular date should be returned. The problem is that since the sheets are arranged by departments the emp code has to be looked up in all the sheets till a value is returned.
View 3 Replies View RelatedI need to write a formula that looks for a value from column a, find the
sheet with a matching value in cell e4 and returns the value of cell ac1 of
the worksheet.
This formula will live in column b of the summary sheet.
Example:
Summary Tab
A B
1 19-Jul 19,000(formula result)
2 16-Jul
3 23-Jul
4 30-Jul
Data Tab 1
E AC
1 9-Jul 19,000
It will need to look in cell E1 of every tab in the work book (52 tabs) for
the value found in $a1.
I have a list of cusips/numbers in one file and I need to see if this cusip/number is in another file which has three worksheets. So tried this formula and what is wierd that if the cusip is in the first tab it returns #N/A, but if it's the 2nd tab it returns the right answer but if it happens to be in the third tab I get "false" instead. Why only if it's in the 2nd tabe does it return a value? I would combine all of these worksheets but it exceeds the number of rows allowed in the version I have.
I'm pasting the formula...hoping someone can point out what is wrong. I'll break it up to make it easier to read.
=IF(ISNA(VLOOKUP(E7,'P:Global InvestmentsPerformance ReportingDATASAC (new)Unclassified[SAC08_Unclassified_SeptMonthEnd.xls]Unclassified'!$B$2:$B$7137,1,0)),
IF(ISNA(VLOOKUP(E7,'P:Global InvestmentsPerformance ReportingDATASAC (new)Unclassified[SAC08_Unclassified_SeptMonthEnd.xls]Other Other'!$B$2:$B$25964,1,0)),
...........................
I have a workbook that I'm preparing, that has several (appx 60) worksheets, each named for the location they refer to. I also have a summary sheet that highlights the key data/balances. On this summary sheet, I'd like to have certain data refer back to the individual sheets. For example, I have data set up:
Location
1
2
3
etc.
Each additional worksheet is named 1, 2, 3, etc. Is there a way to set up a formula that looks to the cell where it says '1' to direct Excel to the sheet named 1?
I am trying to look up a several values across multiple sheets. Is there an easier way to do this rather than...
IFERROR(VLOOKUP($B10,ULTA!B:X,23,0),0)+(IFERROR(VLOOKUP($B10,CVS!B:X,23,0),0))+(IFERROR(VLOOKUP($B10,HARMON!B:X,23,0),0) )
That's just 3 sheets, I have 28.
What I am trying to do is have the contractor name inserted into column B on Sheet 1 next to their respective project.
As you can see each project is only listed on Sheet 2 or 3, not both.
The VLOOKUP formula so far can return the correct name from sheet 2 or 3 but I do not know how to build it into a more complex formula/macro that will do the following: ....
I have made a spreasheet with 4 sheets. In the 4th sheet are 3 columns:
IDCountryZone
There is a button on the first sheet, which when clicked produces an input box. I need the value in the Input box to return which zone that country is in based on the value of the Input Box. E.g. France to return Europe in a message box.
I have Mac:2011 version of Excel. Here's what I'm trying to do... I have 5 sheets, 1 for each week of a typical month, and then a 5th sheet in which I want to add cumulative values.
In the first four sheets, column A is the name of a person. Columns B through H are daily performance values. Column I is the weekly sum of columns B-H. I am sorting each of these first 4 sheets by column I, from highest-lowest; so a person's row position changes from sheet 1 through sheet 4, based on how well they did in comparison to other people.
I should also mention that a person may or may not appear in each sheet for weeks 1-4. They might be there in week 1, miss week 2, and be back for week 3 and 4, etc.
Now, in sheet 5, the 'cumulative values sheet', is where I'm having difficulty. Column A is again, where the names of people should go. Columns B, C, D & E are what I want to be the sum values from Weeks 1-4. And finally, Column F is a sum of these columns B-E, yielding a monthly total. Here's the logic of what I want to do...
Part 1:
Look at the name of the person in [Sheet 1:Column A through Sheet 4:Column A]... If a person's name from [Sheet 1:Column A through Sheet 4:Column A] doesn't appear in sheet 5, column A, copy that name to sheet 5, column A. Otherwise, move on to part 2...
Part 2: Look-up the week 1 value of the Sheet 5: Column A name, from sheet 1, and copy the associated sum value of that name (sheet1:column I) to Sheet 5:Column B. Repeat for Sheet/Week 2 value to Sheet 5:Column C... Week 3 to Column D, and Week 4 to Column E...
And finally, sum the values of Sheet 5, Columns B-E to column F. Sort highest-lowest.
No issues with the summing function, but I'm not sure the best way to achieve 'Part 2'. I've been fooling around with VLOOKUP, and have had some success with =VLOOKUP(A2,Week1!A2:I26,9) for column B, =VLOOKUP(A2,Week2!A2:I26,9) for column C, etc... but it's only performing properly for my first row, and then I mostly get #N/A. So what am I missing? Or is there an alternate function that would achieve this in an easier fashion?
I have a excel doc we use at work to create a schedule for our technicians.
Each sheet is a week, so the sheet names are Week 2, Week 3, Week 4.
Each technician number is listed in column B (3,4,5,36,53,91, etc)
Row 5 has Mon-Sun
The techs work schedule is in the appropriate cell. (8 to 5, 10 to 7, Vacation, etc)
I want to count the number of shifts for each tech each week, with a running total for the year. It was easy to do it on each sheet with a simple countif formula. But The problem comes when I want to count them for the whole year.
I tried creating a "stats" sheet and make vlookup formulas to call the data from each weekly sheet. But with all the techs and shift types I want to count, it was like 40,000 cells. Excel wasn't able to calculate it, it had the "processing 0%" in the taskbar.
I thought I could use =sum(Week1:Week52!AZ6:BN50), which is where I have the counts from each weekly sheet. But my data is not always in the same spot on the sheet. Because of techs coming and going (new hires, people quit).
I have this one sheet (File 1) where I have the unique codes of people and am trying to perform a vlookup() for a value by specifying the lookup in a dfferent workbook (File 2) but multiple sheets. In other words the data (unique codes) is scattered in different sheets in that workbook (File 2). I need to perform a vlookup() wherein the codes finds a match in whichever sheet in File 2 and the value in the specified column be entered against the respective cell in File 1.
I tried selecting all the sheets in the formula but it does not return a value for all the inactive sheets in File 2. Vlookup() works fine for the Active sheet only.
I have to extract multiple words from the string using VLookup function.
(Example :"Cell A1 "This is the best example")
Example : Finding "This" from cell A1- result VLookup (Sheet1) A1:D12
Finding "IS" from cell A1- result VLookup (Sheet2) A1:D20
Finding "BEST" from cell A1- result "Excellect"
Need to haev function for this
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(ActiveCell, Sheet8.Range("C16:Y1000")) Is Nothing Then Exit Sub
Dim rw As Integer
Dim arw As Integer
rw = 16
[Code] .....
As you can see, my code is located in the sheet8 worksheet object. Now, I have a few questions about this. Because I am located in the sheet8 worksheet object does that mean my code can only work in sheet8, i.e., the following won't work because I am in a Sheet8 worksheet object?
Sheet10.Range("B12) = ..... ....... .....
This is not returning a value in Sheet10? My question is how do I make my code return a value in Sheet10?
I am using the VLOOKUP function to get the exact corresponding value in one particular column. But I need to have two different value in two different columns. Here is an example.
I have attached the excel sheet as well.
Actually I do send the data to my different colleagues and they do send me back with the responses in two different column of D and E corresponding to the "ID" specified in column "B". Which I do need to MAP individually. But I want Excel to map the value of that "ID" (column B) to the corresponding responses in Column D and E with formula.
VLOOKUP generally gives corresponding value to either column D or E but i want both at a time to save time.
UPLOAD_DATA.xlsx‎
My sheets are all categorized by date, and I want to be sum individual cells on each worksheet on one final summary worksheet. For example, if cell A1 on each sheet was units sold and I wanted to see how many total units were sold between Jan-04-08 and Jan-16-08 my formula on the summary sheet would be:
=SUM('Jan-04-08:Jan-16-08'!A1)
But I want to be able to easily modify what dates my summary sheet shows so I tried using the function:
=SUM(INDIRECT("'"&N14&":"&N15&"'!A1"))
where N14 was the starting sheet and N15 was the ending sheet. It keeps giving me #REF! however and I can't figure out why and don't know if this is something I can fix.
Can I use the "countif" function using a group of sheets as the range and the criteria on a separate sheet or manually typed in?
I have tried and continue to get the #VALUE error.
OR
Can I do the same thing and perform the "countif" function by using a specific value as the way to count?
I am trying to use the MODE function over multiple sheets within a work book, however when using =MODE( 'First Page:Last Page'!K5). the formula returns "#REF!". this should be returning 7, 8 or 9. I have already checked to see if the range is wrong, but when I changed the function to SUM, AVERAGE, etc they work fine....
however due to the need for non-excel users to easily add extra pages, adding each page individually would be a pain (and would mean I would need to train the staff in Excel)
Where in this function would I put " " to return a blank field if false is returned?
=IF(D18>=1,(VLOOKUP(C18,C7:E13,2,FALSE))-(VLOOKUP(C18,C7:E13,3,FALSE))+(VLOOKUP(C18,C7:E13,3,FALSE))*D18)
- I have total of 13 sheets in a workbook - 12 sheets represent 12 months with data; 13th sheet is single sheet in which i would like to get complete overview of 12 months
- each of 12 sheets has actually sales results for multiple products with following data: internal code, manufacturer part number, name, and qty sold in that month
- situation is that some products have been phased out during the year and some were introduced so each sheet is slightly different in terms of in which row certain product is located
What i would like to achive is to make 13th sheet (whole year overview) do the following:
- there is a list of all the products in it, each product has unique internal code - this code (from each line) should be used to find that code in each monthly sheet, then find its monthly sales value (copy it) and paste it in sheet 13 in cell that represents this product and particular month.
In other words i would like to see for each product what was monthly sales throughout this year, but avoid manually filling in qty for each product per month.
I'm currently trying to vlookup the same data from 2 different sheets. Here is the code i've tried.
View 4 Replies View RelatedI’m trying to develop a workbook which holds monthly data on loan information. It tracks the interest and balance on the loan. I want the first page to have a table displaying the interest payments for every individual tab. When I was brainstorming the idea, I was considering a sort of Vlookup function to find the tab the account is on and then a further function, possibly another vlookup which connects the month to that month’s interest payment. Can anyone help me figure this out?
The attached spreadsheet is obviously simplified, there are well over 30 tabs. But I would like it to, ideally, search the account number column, search the workbook for that account number, and then when on that page use the month at the top of the first page and retrieve the interest payment and put it back in the cell. It’d also be great if the formula can be transferred between workbooks. I’m not sure if that makes sense; basically if I were to copy that worksheet into the next months book, I would like that the formula read those tabs instead of becoming obsolete due to references from the first workbook.
let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.
[Code]....
This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.
I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:
This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in
Match: Lookup value = $E3
Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1
match type = 0
This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered
Match: Lookup value = $A$4
Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452
match type = 0
Index: array = $BA$434:$DN$452
So I think my final function is
[Code] .....
But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
I am trying to use the vlookup function together with an offset function but i am not getting it to work properly.
The situation is as follows:
I have a column E in which i use the vlookup function to find its corresponding value in B which in turn refers to a named range. However, the figure i want the function to return is 2 columns to the right and 2 rows above the value which the vlookup funtion finds in the first column.
I have though of using the offset function, but i cannot figure out how to make this work.
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
I have been running a simulation for about 18 hours now and just received:
Run-time error '1004':
Method 'Add' of object ' Sheets' failed
I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...
I have a work sheet named "Main_List"...In column D starting with "D2" I would like to list worksheets that I would like to have printed via VBA.
The workbook has several hundred worksheets and I would like to list in column D only worksheets that I would like to print with VBA code.