I have =VLOOKUP(A4,Jan!A:D,2,FALSE) in my workbook, but I would like to be able to have the sheet name (Jan) as a cell reference?
I would like to be able to type the first three letters of the month in C1, so that the formulas automatically update to look up the data for the sheet specified in C1. Can this be done?
I have a field set up to display the contents of a cell (sheet 1) using VLOOKUP if a match is found in a separate worksheet (sheet 4). Here is the code I'm using:
=VLOOKUP(C:C,Sheet4!G:G,1,FALSE)
Is there a way, if there is a match, to display the contents of another cell of sheet 4 in the cell of sheet 1?
EX:
Sheet 1 C:C matches Sheet 4 G:G
Sheet 1 C:C displays I:I from Sheet 4
I have two sets of data, as per example below, and trying to figure out the syntax to perform a lookup based on the first two words in a cell (separated by a comma) in the corresponding data, and return a mapped value.
Master Data
Stock
Description
Code
[Code].....
I have 2 inventory reports: what my store has and what my supplier has. I need to copy Tab1:K# to Tab2:T# provided that Tab1:A#'s contents match Tab2:A#'s contents. The A column represents the SKU of the item, but there is a difference in the amount of SKU's in each (my store sells ~6,000 items, supplier has ~10,000 items), so it's not as simple as sort by column A and copy pasting column K to column T.
For instance:
On Tab 1, A2's value is [1], K2's value is [9.38].
On Tab 2, A70's value is [1], K70's value is blank, but I need it to be [9.38], to match Tab 1's respective SKU.
I almost thought I had it figured out with VLOOKUP, but I cant seem to get it right... It doesn't reference the correct number.
Screenshots for reference
First tab, from the wholesaler: [URL]
Second tab, store's stock: [URL]
I have a multi-worksheet workbook.
In the first worksheet, called "Overall Totals", I have only two cells utilised (A1 and B1).
The first cell contains the name of one of the other worksheets. This may change, in order to reference other local worksheets.
The second cell should use the value contained in the first cell to go and get a particular piece of data from the other worksheets.
So, for example, if the first cell (A1) in "Overall Totals" contained the data "SheetName 1", and the data we wanted to get from that sheet would be cell F12 from that sheet, then I would expect the second cell (B1) in "Overall Totals" to say something like.
='SheetName 1'!F12
if I was hardcoding it. But I don't want to hardcode it, as the value in A1 could change, and I'd have to re-change lots of hardcoding.
So can I do something like....
='CELLVALUE(A1)'!F12
I am working with a spreadsheet that has our user data; User(First) Name, User(Last) name, Computer Name, MAC address, Phone Number, Seating Position and Network Port Number.
I have let this get horribly out of sync, moved computers around, and moved them while the MAC address column was hidden, As a result, I have a bunch of users listed as being at the wrong computer. I have a second spreadsheet I generated that gives me the logged in user for about 2/3 of my computers, with the computer name(Just 2 columns, 'A' and 'B'. I would like to have Excel match the computer name and then overwrite the user name. For example, if the first computer in my correct user list is DELL-99945ty2, and the user name is "Jimbo Jones", I would like it to search the first Excel doc(The one with all of the user data), and replace the user name with "Jimbo Jones". Is this simple and straightforward?
In my main spreadsheet, the user name is in column B and the computer name is in column G, So I want to use the values from Column B in my second document to find its match in Column G of the first spreadsheet, and then replace the data in Column B with Column A in the first spreadsheet.
I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'.
e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
I have a line of code that returns a run-time error 1004 whenever it is passed through. All I am trying to do is copy and paste. I am missing some glaring error? (It is only a selection of the code to highlight the part I am having issues with. "maxdate" and "d" have been set)
VB:
Dim ws, ws1 As WorkSheets
Set ws = ThisWorkbook.Sheets("Data")
Set ws1 = ThisWorkbook.Sheets("Target")
[Code].....
I have a loop function that goes through a list of employees and i want to move their name to a different sheet(monday, tuesday...) so that I know who is in on what day. is there a simple way to have it add the next name to the bottom of the list?
View 3 Replies View RelatedI have contacts list that I made and want to take it to the next level, but don't know how. See attached.
The goal:
1. Highlight an entire row of someone's contact info (i.e. row 9)
2. Click the "ORDER FORM" text box to activate a macro that populates specific cells on the 'order form' sheet with the customer's contact information from the highlighted row. The information I want copied over is in red on the order form sheet. THAT'S IT!! (Oh, and 'paste value' the current date from H5 so that the date is static on the order sheet
If possible, I would like to have an error check in case no row is selected prior to clicking on the text box that pops up the note "Please highlight a contact row before clicking the 'Order Form' button."
I have a workbook called "Staff Planner". I need to check Columns C to I in Sheet "Plan" to firstly find text in a red font, and then copy this value to Sheet "Useage" in Column K. (So, search C1:I1, find red font, copy contents of cell, paste value to K1). I then need to repeat this with blue font/value to H1, green font/value to E1, and finally pink font/value to B1
View 9 Replies View RelatedI have a workbook with two sheets of data. I want to hide column B of Sheet2 and clear contents of range B2:B50 if the value in A1 of Sheet1 is "a".
View 2 Replies View RelatedI have a master sheet where users can change the name of 20 different sheets in the workbook by changing a cell value on the master sheet. Here is the code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
If Target.Cells.Count > 1 Then Exit Sub
[Code]....
The next step which I am having issues with is hiding the sheets. I want all sheets in the workbook to be hidden except for the master. I only want the other sheets to be visble if the user inputs a name on the master sheet.
So if the cell contents on the master sheet, say "B9", is blank, the sheet in the workbook that corresponds to that cell will remain hidden. If the user inputs anything, say "Sheet1", in cell "B9" on the master sheet, I want that sheet to become unhidden and to be named "Sheet1"
I have attached a workbook stating my problem.
file1.xlsx
Have a cell (G6) that contains the following formula, which looks up the cell adjacent to it (H6):
=IF(ISNA(VLOOKUP($H$6,Customers,2,0)),"",VLOOKUP($H$6,Customers,2,0))
I want to copy this formula to hundreds of cells below the first cell, with a formula that increments as follows:
=IF(ISNA(VLOOKUP($H$7,Customers,2,0)),"",VLOOKUP($H$7,Customers,2,0))
=IF(ISNA(VLOOKUP($H$8,Customers,2,0)),"",VLOOKUP($H$8,Customers,2,0))
=IF(ISNA(VLOOKUP($H$9,Customers,2,0)),"",VLOOKUP($H$9,Customers,2,0))
....etc.
Is there a way to copy this formula, and have it increment as shown?
I want to end up with is when I type text in any row of the Master sheet, I would like the entire contents of each row to automatically be repeated on the worksheet of the same name (as per column O). For example row 5's contents from the Master sheet would duplicate into the sheet named Accommodation. In addition, should the details ever change in the Master, this would need to be reflected in the individual sheets also. My only concern is that I'd like the individual sheets to fill from the top rather than match the exact row number position from the Master, ie row 5's contents to be placed into row 2 of the Accommodation sheet seeing as it's the first occurrence, and row 6's would go into row 3 etc.
View 2 Replies View RelatedI want to end up with is when I type text in any row of the 'Master' sheet, I would like the entire contents of each row to automatically be repeated on the worksheet of the same name (referred to by text entered in column O). For example row 5's contents from the Master sheet would duplicate into the sheet named 'Accommodation' as O5 contains the word Accommodation.
In addition, should the details ever change in the Master, this would also need to be reflected in the individual sheets. My only concern is that I'd like the individual sheets to fill from the top rather than match the exact row number position from the Master, ie if row 5 had the first occurrence of the word Accommodation in column O, the contents of that row would be placed into the next available first blank row of the Accommodation sheet, which in this case would be row 2 as the headings are on row 1.
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 RelatedIn workbook 1, I have summary sheet with columns
A -> SNO
B -> Customer Name
C -> Product Name
D -> MODEL
E -> Quotation Rate
I have to retrieve MODEL & Quotation Rate .
and also having seperate sheet for each customers say X1 , Y1 , Z1 ( so other sheet names as X1, Y1 & Z1)
In X1 sheet , I do have following columns
A-> Product Name ,
b-> Model (as of now limited to 1 per product name)
c -> Rate
Now my request is as follows Based on the Column b value in Summary sheet - i have to goto respective sheet and do vlookup for the respective product name and retrieve model and fill it in Column D. I heard i can use INDIRECT function and Vlookup in this junction .
I need to lookup to search a range on a different sheet and return the appropriate corresponding cell.
Basically its if a1 is found in the other sheets range a1:a100 then return the corresponding Bcolumn value from the different sheet.
Formula
=VLOOKUP(A2,'All Users'!A$2:A$100,'All Users'!B!2:B!100)
Example of 'All Users' Sheet
A B
STAFFIDSTAFFNAME
24177John
10487Paul
20031George
84087Ringo
85772Pete
24485Stuart
3829George
51836Yoko
I would like to have vlookup formua. And I would like the sheet for the table array section be linked to a cell value. So in my workbook this is my vlookup formula:
=VLOOKUP(B2,sheet1!B2:C8,2,FALSE)
I would like to to be something like =VLOOKUP(B2,(=b3)!B2:C8,2,FALSE). This way I can change the value in B3 and the vlookup formula will look for values in a different sheet as opposed to the one I originally designated in the formula.
Here is my workbook: Book1.xlsx
I have two worksheets in one document. On sheet 'M_Admission' there are numbers for each week. can those numbers be automatically copied to the correct cell on sheet 2 'M_Actual' See the attached Excel worksheet to see what I mean. (Excel 2002)
View 3 Replies View RelatedSee the attached example spreadsheet which will make this much easier to understand. I want to create a formula in the second worksheet that says "If cell A1 of sheet 1 = "a", Then i want to put the contents of cell B2 ("flour") into cell A1 of sheet 2, I want to put the contents of cell C2 ("egg") into cell B1 of sheet 2 and I want to put the contents of cell D2 ("sugar") into cell C1 of sheet 2. If not put 0.
View 3 Replies View RelatedI need a macro to have the copy of selected cells on sheet1, and paste it on sheet2 of the same workbook, whenever I take the print of the sheet1. My requirement is that Each time when I take the print, the selected items get pasted simultaneously on next available cells on sheet2.
Example of my worksheet is attached : Example.xlsx
The sheet to be exported is titled TI
I need the name of the name of the txt file to use the values in cell AE1 & Q1 from a sheet titled Project Report. eg cell AE1 contains 1783 and Q1 contains Ore Vally so the file name needs to be 1783 Ore Valley.txt
In the sheet TI, under column A, there are cells that contain the number 0. I want the export to ignore any rows which have a 0 under column A
The data from each colum in the sheet TI needs to be separated by TABs and not commas, ful stops, semi colons, colons etc etc.
Once all of this works, i need to be able to just click on cell J1 in sheet Project Report so that with this single click the txt file is produced.
How can i delete every formula for a sheet without deleting the contents?
View 5 Replies View RelatedIm looking to ensure the sheet contents are clear before continuing with the macro routine. At the moment however, the macro runs I see the data flash up and then off. So it appears the data is being cleared as soon as it loads. Current code is:
View 2 Replies View RelatedI've got a spreadsheet with a 'data' sheet and I want to add and name additional sheets based on the contents of a range in the 'data' sheet - it could be 1 extra sheet, it could be 100, the range is open ended.
View 3 Replies View RelatedI have a userform in a workbook and depending on what selection the user makes from a combo box, I want the data to get pushed into the relevant sheet.
I did think I could get away with
If Me.cmbtype.Value = "Tool" Then
Set ws = Worksheets("Tools")
ElseIf Me.cmbtype.Value = "Guide" Then
Set ws = Worksheets("Guides")
etc but its now not copying data into Tools if I selected Tool from cmbtype (but when I select Guide it does appear to work).
The rest of the code is the same regardless of what type they select from cmbtype, its just which sheet the data gets copied into that changes.
I am trying to place the contents of an array into a column on the sheet. I can enter the array into 1 cell (which strings the values), but I can't seem to make range(1)=array(1).
View 9 Replies View Related