I am trying to use a match index formula to retrieve some data from another workbook...I am wondering if I can use a worksheet name as an IF condition, i.e. I want my match index formula to search the entire workbook, and return values for which there is a match, with the IF conditions being a specific code AND worksheet name.
I am currently using the below formula to add values (D6:D225 on sheets Mon, Tue, ...) given the criteria is met in any cells within the two columns across the five sheets (E6:E225 & N6:N225 on sheets Mon, Tue, ...).
When the references to the columns do not randomly change themselves to #REF! it works fine however, it does this often, forcing my to rewrite the formula.
**Why does it do that and is there a way to stop it?
Also, when I do have to rewrite I am forced to do each cell individually to change the number within the quotes. I have tried to use a cell reference there but when I do Excel just gives me a "0" as if there is no data to be retrieved.
I am using Excel 2010 on a company computer with the macros blocked so no VBA.
I am looking for a formula or something - that when a reference number is used - it popluates cells from a list. Attached is a sample spreadsheet - 2 worksheets are being used - 1 is Purchase List and the 2nd is Fax Commitment. When reference no is filled in on the Fax Commitment sheet and it = the same reference no as on the Purchase List - I need it to populate the appropriate fields (in this case I have colour coded)
I have a dataset of a few hundred rows, but will only provide a small sample of rows for this example.
I need INDEX/MATCH to return the correct values for the subset below.
Column A (Forecast Status) - Column B (Account Name) Commit - Account01 (Row 1) Commit - Account02 (Row 2) Won - Account03 (Row 3) Won - Account04 (Row 4) Won - Account05 (Row 5) Upside - Account06 (Row 6) Lost - Account07 (Row 7) Upside - Account08 (Row 8) Won - Account09 (Row 9) Commit - Account10 (Row 10)
In a new tab, I am creating a 'dashboard' view that will group each of the following:
Commit ONLY Accounts in cell A1 (Formula to be copied down to retrieve Rows 1,2,10) Won ONLY Accounts in B1. (Formula to be copied down to retrieve Rows 3,4,5,9) Upside ONLY Accounts in C1. (Formula to be copied down to retrieve Rows 6,8) Lost ONLY Accounts in D1. (Formula to be copied down to retrieve Rows 7)
I am building a timeclock, and am using a user form to allow people to sign in/out.
I have a punch in form that logs the inputed username and password (after being checked against my employee list), along with a count of their username on this sheet (as a count of how many times they have punched in) and a timestamp (column 4). These values are all pasted to the first blank row in my worksheet "PunchInTimes".
Then, I have a punch out button that logs these same values the same way, only on "PunchOutTimes". Then, the idea is to link the index number of the PunchOutTimes record and PunchInTimes record for any given user, so that when they punch out, a complete record is pasted (to the first blank row) in a separate worksheet called "Timesheets".
I have been able to paste the username and count to the "Timesheets" sheet, however as soon as I try to index/match the count and inputed username (on the punch out form), I get an error 13 type mismatch.
I'm trying to paste the corresponding punch in time to this punch out time, so ive used an index/match with two columns (username and count) found in "PunchInTimes", and try to return the corresponding 4th column value (punch in time). After pasting the "punch out only" values to the PunchOutTimes, i used a "With/End With" statement, and set wsc equal to my 'timesheets' worksheet at the beginning of my code. So the line where I get my error is below in bold:
My issue concerns going down rows in a data set. Due to the fact that the data set I am pulling from is not standardized, and roughly 10% of the data set has two extra rows of extraneous data below the reference cell (foreign currency balances which I don't need). The point in my formula is the "+2" (down two rows) condition. I needed it so that the "+2" is replaced with a condition inwhich after the reference cell in the INDEX( and MATCH( function is found the command goes down rows until the term "USD" is found. My formula is as follows:
Rows 1 and 2 (range: A1:CM2) within Spreadsheet X contain the range of data that I need.
Within Spreadsheet Y, Row 6 will change monthly and will be input by the user. The value in Row 6 equals the data in Row 2 in worksheet X.
I need to put a formula in row 4 of spreadsheet Y that returns the value of Row 1 in Spreadsheet X.
Unfortunately, I cannot change the spreadsheet layout, otherwise I could do an HLOOKUP formula using Spreadsheet Y Row 6 and Spreadsheet X Row 2 and be done with it. I assume that I need to use an Index-Match formula, which I am not as familiar with. In addition, all of the online support I have found explains Index-Match with data in columns, not rows.
I have a data set where the row headings repeat a lot. I have 5 headers repeated probably 30-45 times each. Eg. Truck, Car, Van, Tank, House, Car
At the moment I am using the formula... =INDEX($B$2:$M$45,MATCH($O$2,$A$2:$A$45,0),MATCH(O4,$B$1:$M$1,0))
which will only return the result of the first occurrence of the cell it is looking for (for eg. I am looking for the values in the cells in columns labelled 'Car', is it possible for it to look past the first occurrence in the data set and find all the values?
I'm trying to fix a report that I created but can't seem to figure out a way to display the information for a single student.
I've attached a mock report up so that maybe it would make more sense.
So the first tab, 'DIR', I managed to find a way to pull up only the information for a single student that was selected in the drop-down D1 cell. For that I used a simple INDEX/MATCH combo. The student's name would only appear once in the 'Paste DIR' tab, so it was fairly simple to create these formulas.
Now for the 'Paste Outreach' tab, a student appears multiple times in the chunk of information. Right now, the way that I have the report is so that it would just pull the information from each row, regardless of the student. What I would like to do is find a formula similar to INDEX/MATCH, but find every instance when the criteria is met for the INDEX/MATCH. I would like to be able to log each instance a student has an Outreach log entered. Then if I switch the student selected in D1 of the DIR tab, then it would only populate their Outreach Logs.
So if Student Name & Outreach # matches D1 ('DIR' tab) & "90" in "Paste Outreach, then pull row info into rows 7-11 in Outreach. Find the next instance Name & # matches, and pull that into rows 12-16, etc.
In Worksheet 1 Column AQ contains a Cost Centre number which I would like to extract the parent description of in Column BI of the same Worksheet and then the child description into Column BJ. Worksheet 1 currently has 16000+ rows and grows daily.The location of the Cost Centre in Worksheets 2-4 is contained in Column A and the Parent Description in Column W and the Child Description in Column Y.I am currently using this INDEX MATCH formula to search all worksheets but as you can imagine it's tediously slow and needless to say sometimes not reporting/updating the values correctly likely because it's frozen.
=VLOOKUP(AQ2,INDIRECT("'"&INDEX(Sheet1!$A$1:$A$4,MATCH(TRUE, COUNTIF(INDIRECT("'"&Sheet1!$A$1:$A$4&"'!A2:A63355"),AQ2)>0,0))&"'!A2:W63355"),23,0) this looks up Cost Centre in Worksheet 1 Column AQ and returns the results from Column W in Worksheets 2-4 to Worksheet 1 Column BI
I have a table where I want to match data to a reference. The reference appears in more than one row in the first column and I want to find out which row numbers that reference appears in. However, using MATCH I can only find the first time that reference appears. Is there another way of finding at least the next row that the reference appeared in?
E.g. in the table below, Match always returns row 2 when searching for ID 123. Can I set up the formula so that it tells me where the next match appears, working downwards??
I used the code in the link for "Create Worksheet Index" you referenced and it works great. Is there a way to have the Index and the "back to Index" links appear in separate stationary windows on the left side of the spreadsheets?
I have two workbooks open, Workbook 1 (W1) & Workbook 2 (W2)
W1 has the following data entered in about one of our clients:
A1= Client Company Name A2= Client Name A3= Client Address A4= Client Phone Number
What I would like to do is the following:
In W2, when I enter (in any cell, but let's say C3 for example) that C3 = A1 (from W1), that I can automatically have the following cells D3, E3, & F3 filled in with the same information appearing in cells A2, A3, & A4 from W1 as well.
I believe this must be possible-- I just don't know the right formula & steps to take, to make this happen.
I want to be able to reference cells in a table by using two drop down lists. One drop down for the columns in the table and another drop down for the rows in the table. The values in the columns will be different metal materials (aluminum, copper, stainless steel, etc.). The values in the rows will be thicknesses of those materials (1/16", 1/8", 1/4",3/16",3/8",1/2",3/4",1"). I already created a drop down list for the materials and another for the thicknesses. But now I want to be able to select a particular material and then a particular thickness and receive back in another cell a value that's cross referenced in a table. Specifically, the values in the table will be the weight of the particular material and thickness per square foot. I'll populate all of the cells in the reference table manually. I'll hide the table on a separate worksheet. i haven't created the table yet.
combining multiple rows of related data into one row but have not seen one that matches my situation. My experience level with excel only includes some basics like vlookup, match and recorded macros, although I have used vba code for a macro that met my needs. I have a parts list for a PCB that has many different parts. Some of the parts are used multiple times across different reference designations("Reference" column in the file). I have shown a desired format and the original format below. Since the item number has blanks across the reference designators I haven't found a solution that would work for me.
I am trying to fill in reference cells horizontally but fix the column and increasing the row number as it fills. For examples I want to start my refence as "=A1" in cell B1. I want to copy this reference horizontally to column Z1 but want the refences to be A1, A2,...,A26. Excel only seems to increase columns when filling horizontally and rows when filling vertically. Transpose works but it's an array which would slow the spreadsheet down.