Double Lookup To Retrieve The Contents Of One Cell And Put Into The Main Sheets Cell?
Apr 18, 2013
I get a report each day with a list of issues. the "group" that works the issue and the "priority". Based on these two factors, i need to do a double lookup (vlookup?) to another tab or file to match the priority and group and see what value should be brought back for each lines results. For example, if group1 had a prority3 issue, the lookup would find the value from the other sheet or file and bring back the value and put it at the end of the row where the formula is.
Attached are examples of the sheets.
sheet1.jpg
sheet2.PNG
View 4 Replies
ADVERTISEMENT
Apr 29, 2014
I am trying to create a formula which will allow me to type a word in a cell on the Main sheet, and then pull any row from multiple sheets and display them on the Main sheet. For example, my sheet named Main looks like this:
A B C D E
1 Query:
2 Fruit Store Cost Mode Date
And there's a sheet for each month with the same format and different data:
A B C D E
1 Fruit Store Cost Mode Date
2 Apple Dominicks 1.99 Bagged 3/16/2014
3 Apple Piggly Wiggly 2.19 Ala Carte 3/14/2014
4 Banana Jewel 0.49 Bagged 3/1/2014
5 Banana Dominicks 0.57 Ala Carte 3/16/2014
6 Banana Costco 0.69 Ala Carte 3/16/2014
7 Cantaloupe Jewel 2.99 Ala Carte 3/14/2014
8 Cantaloupe Dominicks 1.99 Ala Carte 3/1/2014
9 Eggplant Jewel 0.99 Ala Carte 3/30/2014
10 Eggplant Dominicks 1.19 Ala Carte 3/30/2014
11 Eggplant Costco 1.29 Ala Carte 3/21/2014
12 Eggplant Safeway 0.79 Ala Carte 3/16/2014
13 Pears Jewel 1.19 Ala Carte 3/14/2014
14 Pears Dominicks 1.59 Bagged 3/1/2014
15 Pears Piggly Wiggly 0.99 Ala Carte 3/30/2014
16 Rhubarb Jewel 0.99 Ala Carte 3/14/2014
17 Rhubarb Piggly Wiggly 0.59 Ala Carte 3/14/2014
18 Rhubarb Costco 0.89 Ala Carte 3/14/2014
19 Tomatoes Jewel 1.99 Ala Carte 3/1/2014
20 Tomatoes Safeway 1.69 Bagged 3/30/2014
21 Tomatoes Dominicks 1.89 Carton 3/27/2014
... and so on. On my main sheet in Cell B1, I want to type a fruit name, such as Banana, and have every row in every sheet with Banana in column A,pulled and displayed on the Main sheet. Firstly, is this even possible with a macro?
View 4 Replies
View Related
Apr 14, 2009
I need is spread across various rows. In a separate sheet I want to be able to lookup two identifiers and pull the number needed in the adjacent row.
The first identifier is a 3 letter character, the second is CURREVO and I need the number to the right of CURREVO. My problem is CURREVO is not always in the same column, but in relation to the 3 letter character is always in the same row.
A B C D E F GDMGCURREVO52011.25YTDREVO243085.00DTYCURREVO11892.50YTDREVO59783.50ECUCURREVI1943.00YTDREVI 5,541.25CURFRQI4.00EEGCURREVO32864.75YTDREVO205426.75EICCURREVO658761.26YTDREVO3507022.68EILCURREVO335741.70YTDREVO1720830.72ENTCURREVI161242.39YTDREVI638681.84CVLCURREVO796266.21YTDREVO4816890.98ENDCURREVI34479.19YTDREVI44074.54EYECURREVI11880.12YTDREVI 108,007.02(Null)MICCURREVO1098694.15YTDREVO5766072.54NEUCURREVI25251.90YTDREVI 158,236.60CURREVO207.00
i.e.
in a seperate sheet, I want to pull NEU, in that row I want to find CURREVO and bring back the adjacent number = 207.00
View 9 Replies
View Related
Aug 24, 2006
I want to be able to send out one “master” workbook with the fourteen ranges, and use a pull-down list (or anything else) so when the user selects a state, the proper lookup range will be used. In the attached abbreviated example, I want the lookups in Column B to refer to the state designated in B2. In this case, the VLOOKUP works correctly, since Colorado zip codes are in the ColZip range.
The people in Idaho or Oregon using this will only be working with their respective zip codes in ranges IdaZip or OreZip, so if they enter their state in B2, I would like the VLOOKUP to look in a different range. My whole idea is to only have one master workbook so I don’t have to create one for each district office. Maybe I’m thinking in a wrong direction altogether.
View 3 Replies
View Related
Oct 18, 2006
I have a workbook with a sheet titled Variables. There are 6 additional sheets in the workbook and each sheets needs to be named with the cell contents of A2:A7 in the Variables sheet. I have attached the workbook and you can look at the names in the sheets tabs to see what I'm trying to do here.
The workbook is too large to attach but can be viewed here:
http://www.shuffleupanddeal.org/name_sheets.xls
or
http://www.shuffleupanddeal.org/name_sheets.zip
View 9 Replies
View Related
Mar 23, 2009
I have a master sheet that has 3500ish names on it, and another sheet that I'll need to drop in a list of about 1000 names. What I need on the master is a way to check the dropped in data, find duplicated names, and flag them up.
Now, I believe what I did last time was have a true/false method of telling me if they're on both sheets, then use an IF formula to instead make Trues into "yes" and Falses into "no". However, I can't for the life of me figure out how I had it previously checking both sheets and confirming/denying if they're on both sheets or only appear once.
View 2 Replies
View Related
Nov 10, 2009
I have 5 sheets. In the first sheet I have set up 4 cells where I want the contents of them to automatically rename the other 4 corresponing sheets.
Eg.
In Sheet 1, Cell B6, I want the contents (which will be text) to be the name of sheet 2 automatically.
In Sheet 1, Cell B7, I want the contents (which will be text) to be the name of sheet 3 automatically.
View 8 Replies
View Related
Aug 29, 2008
I would like to use VBA to search a folder and copy data from tabs within the excel files there. The data will be pasted to a tab of same name in the the main file. All the files are in the same format.
So far I have only managed to list the files in the folder using code I found on your site!
View 7 Replies
View Related
Feb 22, 2014
In the attached spreadsheet I need a formula to extract the value from column C that matches the date in Column E where the date in column E is equal to or falls between the dates in Columns A and B.file now attached.
View 4 Replies
View Related
Dec 10, 2013
What I would like to do is use the IF function to retrieve data from another sheet called Data which has six columns but has various amounts of data that I would also like to pick as a drop-down menu.
The initial cell has a dropdown populated with data using the Data Validation.
Example would be if the following is selected from the initial drop-down menu and then allows me to select data depending on the previous selection.
PX1 - fetch information from Data T2 to T25 and then select that data which then would allow me to select data again from say column U2 to U9.
Is this possible?
View 5 Replies
View Related
Mar 12, 2014
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
View 3 Replies
View Related
Aug 15, 2009
the formula i used is " =IF(A2=A3;"YES";"NO")
the contents are as follows
A
Numbers
2525 - YES
2525 - NO
2526 - YES
2526 - NO
2528 - NO
2530 - NO
2527 - YES
2527 - YES
2527 - NO
how can i use a formula... that tells the last double entry yes too instead no?
for example above...
2525 - YES
2525 - NO (i want the formula say this "YES" too..)
So i want to find out through filtering.. all the double entries with YES.. so i can work with it without missing any double entries.
View 3 Replies
View Related
Feb 6, 2008
I have a sheet where i have many differently named areas (like state1_1 and state1_2) When I doubleclick on a cell then a macro should run with following criteria: 1) Macro will run if the doubleclicked cell is part of any range in the list. Here I mean that names of ranges which belong to that list start with word state (like state1_1 and state1_2). No other ranges should not be in that list. If the cell is not in the range that is part of the list, then nothing should happen.
View 2 Replies
View Related
Jan 1, 2009
The datas has stored in columns "a:g" all the way down.
When i double click any cell in column "A" I would like to store the value one by one into the column "K" from first cell to down.
View 9 Replies
View Related
Mar 25, 2014
I have a data sheet that I enter all data into. I would like to divide the data into different sheets depending on the month the job was entered. Please see a sample I have attached. I have tried to convert the month (colE) into a figure (colF). I am hoping the data automatically copies across into the correct month sheet or perhaps I can push a button and it will do it for me. Data will get entered on a daily basis by staff but only onto the main sheet, this will then by some miracle be duplicated into the corresponding sheet without loosing any data on the main sheet.
View 8 Replies
View Related
Dec 26, 2013
I'm trying to create an auto fill setup where 12-15 work sheets all get their data from 1 main sheet where I will import sales numbers. On a monthly basis I create reviews for all sales personel and the inputting of data alone uses precious time. So I would like to import the sales data, seperate it by column, and have the data auto fill the sales associates fields automaticly. So if I import the data to sheet 1 and associate John Smiths sales data fall on cell A1 thru A20 and directly below him Jane Doe's sales data falls on cells B1 thru B20. John's review will auto fill from the data on A1-A20 and Jane will have an identical review on a different sheet that auto fills from the data in B1-B20.
View 4 Replies
View Related
Mar 4, 2014
I want to calculate all datas from everyday of a month in a main sheet which is like;
{=average(if(('(1)'!$C$3:$C$15=C3)*('(1)'!$V$3:$X$15>0);'(1)'!$V$3:$X$15);if(('(2)'!$C$3:$C$15=C3)*('(2)'!$V$3:$X$15>0);'(2)'!$V$3:$X$15))}
In the main sheet C3 cell will be equal to c3:c15 serie and values in the v3:x15 serie will be greater than 0. Then formula calculate average of v3:x15 values based on c3 cell like vlookup. I'm writing formulas like that everytime and want it to get datas from all days of a month from 1 to 31. I tried '(1)31)' and when named sheets like 1,2,3 '1:31' i got #value error however when i write 31 if formulas for all days i got the correct results.
View 1 Replies
View Related
Mar 29, 2009
I have already placed a siniliar question but I think it fell through the cracks or it was to difficult to understand!! I am losing my mind as the hrs tick down and I can't get this done
I have a detailed sheet called "detail"
I have 5 other sheets called "J1" "J2" "J3" "J4" "J5"
The sheets are exactly the same with headers already in. However "detail" sheet has all the detail and the "J" sheets are blank.
I need a macro in a command box that will start on line 5 of the detail section and look in column "D". if either of the "J"s are in column D then the row will be copied, cell colour turns green, and pasted in one of the 5 "J" sheets depending on the value. This process should continue to loop until there are no more lines left in "detail" to copy
I just can't get my head around how to write a macro that will perform this. I have searched through different threads and tried and manipulated a few but I can't get it to work at all. I think I am so frustrated after 8 hrs that
View 9 Replies
View Related
May 6, 2014
I have a workbook that contains multiple sheets, currently five sheets of which only two of them are Info Datasheets.
I would like to create a VBA code that pulls data from multiple info datasheets and compiles that data into one Main Database sheet. However, after that initial collection, I need the code to maintain the rows data. Specifically, if any row's data changes in the info Datasheets I need that row on the main Database sheet to update.
The columns in the info datasheets range from A:AH, however I only need the columns A:E to be copied. And I would like the code to add column F in the main datasheet displaying the sheet name were the data was retrieved
Each Info sheet (Metals, Polymers) are setup identical; Rows 1:3 are the headers, and Column D and/or Column E will denote a change to the row.
The Main Database sheet (Table of Context) has 2 rows for the header. So, the data will start on row 3
F-S886 (Material Properties Database)_DRAFT_5.xls
View 7 Replies
View Related
Nov 23, 2006
I need to retrieve the name of a worksheet for a product.
Example
Workbook1 contains the Product number in column A, i want to enter a formula in column B that will search column A in all the worksheets in workbook2 for that product number, and retrieve the worksheet name that the product number is in
View 9 Replies
View Related
Dec 30, 2013
I have attached a workbook stating my problem.
file1.xlsx‎
View 10 Replies
View Related
Apr 30, 2009
I am trying to automatically reference the contents of another file into my main file. Basically I have one schedule that I've automatically generated in AutoCAD, and another formatted template. The AutoCAD schedule is completely unformated.
I want to include a link to the AutoCAD scheule (a *.CSV file) on my main spreadsheet and have the autoCAD schedule populate my template. I've done some reading, primarily on here, and I think I need to use either the INDIRECT() or the INDEX() function.
I can get INDIRECT() to work, but it's fairly repetitive (not overly important, but still.....). More importantly, though, the schedule file has to be open, which I would like to avoid if possible. INDEX(), I just can't figure out how to work!
View 2 Replies
View Related
Jan 16, 2009
=INDEX('sqft - PC each bldg'!D2:D1705,MATCH(Sheet5!B3&Sheet5!F2,'sqft - PC each bldg'!A2:A1705&'sqft - PC each bldg'!C2:C1705,0))
my result is N/A
I wonder if it has to do with both lookup ranges being columns?
View 9 Replies
View Related
Jan 13, 2010
I have a list like this:
xxxxxxxxxx1-jan | 2-Jan | 3-Jan
-------------------------------------------------
Location 1
Bob Smith ----B2--- C2 ---- D2
Frank Thomas-B3---- C3---- D3
Location 2
Bill Clinton----ETC ALL
George Clinton
And another sheet, a similar list, (just names and randomly sorted) but populated with the values I want,
xxxxxxxxxxx1-jan | 2-Jan | 3-Jan
-------------------------------------------------
Bob Smith --- 2 -----5------ 1
Frank Thomas 3----- 1 ------2
Bill Clinton ----4----- 5 ------2
George Clinton 4----- 4 ----- 4
I want to check the name in A2 on the first sheet against all of column A on the second sheet, then find the number value for the correct day for that name.
How can I write a formula that will look up these values for me? I looked at OFFSET but unfortunately I don't have a lot of excel experience (yet)
View 9 Replies
View Related
Jul 25, 2014
What I want to do is take information from a main sheet and pull certain rows (determined based up on the selection made in the delivered to column) and move it to a worksheet that contains information only for that individual entity. Below is the list of headers in my lis of all information
Ticket #DateDelivered ToNet kg WtTonsRunningTotalDaily Total
As I said, depending upon the name in the delivered to, I would like to carry forward the following information only for the specified vendor. For example if we have 3 entries 1 delivered to company a, 1 to company b and 1 to company c, each would carry forward to the respective worksheet for that vendor.
Ticket #DateDelivered ToTons
how I might accomplish this in a usable format for what I am trying to do.
View 2 Replies
View Related
Oct 21, 2009
I'm looking for a solution for my worksheet. I've got a main tab in which contains deposit data, I need to take the raw data format it and break it down into 7 different tabs. Each week a report is exported and then one for the actual month containing all the data. I’m currently manually recording some formatting macros because I haven’t learned to write my own yet. The number of columns in this report is always constant but the rows vary depending on the number of deposits. I have 7 tabs where the data from the main sheet will be broken down in. In my spread sheet I have the following headings. (A1)Date, (A2)Batch, (A3)Source (A4)Control (A5)Total Batch (A6)Total Variance
The information I would use to copy the rows to other sheets is going to be based on (A2).
Anything that starts with U65 would go into the U-65 tab.
1,7 or 8 would go to the lockbox tab.
2 or 5 = IN-HOUSE
3 = WIRES
4 = DATA OCEAN
Eh, WH, or WE = ACH
Finally the variance tab will contain any rows which have a number other than 0 Positive or Negative. My biggest problem is that I never know how many rows the data will have so when I recorded my macro I Started dragging my formats to about row 200. It could one day surpass this number so If I had a choice id set it to row 1,000.
View 5 Replies
View Related
Apr 17, 2012
I have a workbook with several sheets of varying length (amount of columns). The length of each sheet changes every week. I want particular data from the sheets to come together on one sheet in as organized a fashion as is possible. I'm currently losing my mind copying and pasting every week when I need to update the thing.
View 7 Replies
View Related
Jul 7, 2009
I have multiple sheets Names(Sheet1-Sheet5).
Im trying to compile them all on the same sheet (Main Sheet). But each sheet is NOT the same. I need to choose what Columns needs to be copied (I can use one macro for each tab if needed)
For example I need Sheet1, Columns (A,C,E,G,S) copy that and then go to (Main Sheet) and paste in the next blank line (column A)
Seems like it does not know where to paste.
Here is one I have tried ....
View 9 Replies
View Related
Jan 8, 2010
I have to write some VLOOKUP formulas to pull several items from a report, the problem is there could be multiple items for each lookup value as the example below:
Company Data ABC Monday ABC Friday ABC Thursday DEF Friday GHI Monday
I need to reference every piece of data for every company but using the VLOOKUP formula, I am obviously only able to get the first instance of the lookup value (company ABC)
How to I write the formula to lookup the other data? The practical appliation I am using this for has anywhere from 300 to 400 lookup values.
View 9 Replies
View Related
Jun 22, 2006
I have a Skills Matrix sheet consisting of 100 rows with Employee's names and 60 columns of all possible skills.....where an employee has achieved a skill a date appears in the appropriate column .
I want the user to be able to enter a name in another sheet and for the system to show ( on that sheet ) ONLY the column headers and dates of the skills they have achieved beside that name.
View 9 Replies
View Related
