I am having trouble writing a simple macro that can display the name of the worksheet (Sheet2) in a cell (Cell B2 of Sheet1).
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Sheet1").Range("B2").Value = Worksheets("Sheet2").Name
End Sub
I want to create a formula (preferably w/o macros) to insert the sheet name in a cell. In other words, if the worksheet name is "Location1", then cell A1 result in that worksheet would be "Location1" based on a formula that pulls it from the sheet name.
I found a post that ONLY does this for the current sheet:
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))
However, the sheet name is not frozen with it's sheet name when you switch sheets. I will have many sheets in the workbook.
I'd like to display a formula contained in a cell within the worksheet. How can I do that?
View 4 Replies View RelatedI have a workbook with 4 worksheet that store different type of data. It also has a userform that load at start of the application which is to search the data in the workbook. The userform has a combobox where the names of the sheets are stored. when the user selects say Sheet2 in the combobox, it enables the relevant textboxes on the userform and activates the worksheet at the change event. The userform has a search button that searches all the worksheets based on the text entered in a textbox.
The problem: how to search based on 1 textbox. What I want is: say for e.g the end-user selects sheet2 from the combobox, this intern enables 4 textboxes (Name, DOB, Nationality, ID #) on the userform. The end-user should have the liberty to enter data in 1 and/or any of the textboxes. The search should be performed, that if data is only in 1 of any of textboxes then give all rows that fit that criteria and display in a temp worksheet. if say the name and dob is filled by the user than what matches both should be displayed in a temp worksheet. if say dob, name and ID# given so the search button should narrow down to fit all 3 criteria and then display result in temp worksheet. As if mentioned data can be entered in either just 1 or any or all textboxes.
E.g. the worksheet is (Columns are Name, Nationality, DOB, ID#)
row 1 = name: Steven Martin, DOB: 27-may-1993, Nationality: Trinidad & Tobago, ID #: 1234567
row 2 = name: Gary Richards, DOB: 2-FEB-1993, Nationality: British, ID #: 456789
row 3 = name: David Cohen, DOB: 27-May 1993, Nationality: American, ID #: 98765
row 4 = name: Roberto McDonalds, DOB 21-Jul-1962, Nationality: British, ID # 654321
row 5= name: Gary Richards, DOB: 01-Dec-1978, Nationality: Australian, ID # 1234567
Now if the user enters only name as "Gary Richards" and search then row 2 and 5 should be displayed in a temp worksheet. if user enters name Roberto McDonald and ID# 1234567 then it should not display anything. if user enters DOB 27-may-1993 and nationality British and ID # 1234567 then as well shouldn't display anything and should a msgbox "no data found".
Example:....
i m wanting Cell B1 to display true if any of the values in column C match otherwise I want to display false.
A 1
B 0
C 0
D 0
But I can't determine how to get this done.
Each tab in my worksheet is represented by a customer's name. I would like to have inside this worksheet their name displayed.
Basically every month I keep a track of my sales in a spreadsheet.
I've attached a sample file : Discrepancy Form.xlsx‎
Sometimes I don't get paid correctly. So rather then filling everything in manually, I want to simply put a "Y" (for yes, there's a discrepancy) or "N" (for no, there's no discrepancy" next to worksheet "Sales" but then have it display the values in worksheet "Discrepancy."
some spreadsheets i open dont have 65536 rows , they only a 100 or 200 its seems the worksheets have been cut down. how can i do this based on the last row in the e coloum.
Ie last entry in the e coloumn is 500 the worksheet to only be that big
using VBA, I created a simple problem that I can't figure out how to solve in worksheet 2, I have 3 worksheets.
1. Players - Complete info of players
2. Members - When I select the team name, I'd like to display the info First Name, Last Name and Team only for the team name I've chosen. This should automatically change whenever I select a team name.
3. Team Codes - Tables I used for my vlookup and drop down list.
I have attached my file for your reference.
I have an excel workbook that has multiple worksheets that is linked to our in-house system via ODBC and it refreshes every 5 minutes with up to date sales data.
I am looking to output just one of the work sheets onto a large LCD display / wallboard.
I need to just display the summary worksheet without the excel toolbars / gui
I'm working on a project, and I need a cell on a worksheet to produce the name of the worksheet, such that if the worksheet changes, or is copied [Sheet1 renamed to Sheet(1), for example] the cell will automatically update [now displaying Sheet(1) in cell A1].
View 2 Replies View RelatedI've created a rudimentary VBA code to display 3 worksheets for 30 seconds each. Need revising it as a loop? The idea is to continue displaying each worksheet until I move the mouse or click any key.
Code:
Private Sub CommandButton2_Click()
'Close the UserForm and display worksheets
Unload Me
Sheets("GROSS").Select
[Code] ..........
How about if I have a long string of numbers that I want to display in another worksheet. For example, here is an item id off eBay, that I want displayed in another worksheet if it is present, otherwise, it should be blank. Here is the item id, they are all unique by the way.... 230092645222. I tried to use this formula below but it didn't work.
=IF(ISBLANK(inventory!S2),"",TEXT(inventory!S2))
The worksheet perfectly performs as expected, big thanks to JBeaucaire again. Now, as am looking around of the file and doing some exploration on the array, as I was trying to make this applicable to real life application, I noticed the following mysteries...
1. When I tried to delete some rows, The Members Sheet output went wrong and displaying #N/A. I noticed it's because the formula range have change. But it would be hard for a new user to re-arrange it over and over again everytime when a row is deleted.
2. I've been trying to solve this problem to no avail. I found this link that gives me an idea how my worksheet should work, this was already solved but I can't apply it on my worksheet...
http://www.excelforum.com/excel-prog...nd-resort.html
3. I would like to add another column on A to put the 0 and 1 on, but I don't need to delete this column after the resort so that next time if I need to delete another row existing row i'll just replace the existing 0 with 1 on column A.
I have a product Database with 11 columns. I am trying to build a Search/look-up userform.
I found a brialliant example of the same posted by Roy, UK. I have copied the same and its working well, but with a few little problems.
Here's what I've got:
I have a userform with a Combobox (called Combobox2) where it shows all the products from column B in the worksheet "ProductData". In the worksheet "ProductData" , I have 11 Columns with headings from B1:L1.
When user selects a product from the dropdown list (from Combobox2), all the details related to that product from other columns are displayed on the userform via labels.
This part is working fine....except that the values in Combobox2 are taken from activesheet and I want them to come from worksheet "ProductData".
I have hidden the sheet "ProductData".
Second Part is, I have about 20 products in the Database and all of them have a Product Specification Sheet in PDF format. I am just wondering, if there is any way I can have a button on the userform that can used to open this PDF datasheet for the product thats being lookeed up by user.
What I mean is, if user selects productA from the combobox2, then it displays all the info about the the ProductA on the labels on the usewrform. and if user clicks on the "View Data Sheet" button then it opens the Datasheet PDF file for ProductA.
Below is my code for the first part:
Option Explicit
Private Sub ComboBox2_Change()
Dim ws As Worksheet
Set ws = Worksheets("ProductData")
Dim Ncell As Range
With Range("B2", Range("b65536").End(xlUp))
Set Ncell = .Find(ComboBox2.Value, LookIn:=xlValues)
I have a workbook that lists various shipment numbers and their contents to several locations. What I am trying to do is, on a different worksheet in the same workbook, list all shipments to one location; having different worksheets for each specific location.
I am sure there must be an extremely simple solution to this, however I am unable to locate it.
Also I would like the separate location worksheets to autoupdate when new shipments are entered on the main worksheet.
I'm trying to add a message box that will appear when you activate a worksheet in a workbook. This is what I have, but it's not working.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh. Name = "P2 Forecast" Then
MsgBox "Ensure you have locked your forecast on the Sales Forecast Tab prior to working your P2s"
Else
End If
End Sub
I am trying to build a report based on data in a separate worksheet. The report must only show the name of the value and qty associated with it only if the qty is greater than 0.
As an example, the values can be colors.
Column A = Names of Colors: Red, Blue, Green, Yellow, Black
Column B = Qty of each Color: 5,4,0,1,0
The report is held on a separate spreadsheet. I would like to only show the values that have qty's greater than 0, not any values that have a value of 0.
I've attached a sample workbook just in case.
i want to be able to display the underlying data in a pivot table by clicking on the cell within the pivot table
ie, normally if you double click a cell within a pivot table, it will generate a new tab, and the data will be displayed in the newly created tab,
i want to be able to click a cell within my pivot table, and have the underlying data display beneath the pivot table..
I data on a master document and have data sent in from various independent offices which I would like to reconcile. Each region along with the master is posted onto a separate worksheet in my spreadsheet but I would like to copy specific information in worksheet order and paste row by row onto single worksheet in order to run a comparison. The data needed is in the same order but the number of rows reported on each work sheet will differ day by day. If worksheet 1 displays 50 rows in the consolidated sheet I would like worksheet 2 start in row 51, if however worksheet 1 finished in 24, I would like worksheet 2 to start in 25. Now I also want each worksheet to start reporting once the previous worksheet is complete and when there is no data in a specific worksheet, I would like the process to be able to continue.. so sheet 1 reports, then sheet 2 but sheet 3 has no data so it jumps to sheet 4 to begin.
For example consider various cities, New York, London, Frankfurt & Tokyo, each has sales of various products from a catalogue range but the products sold differ each week based on regional trends. The master document which is independent also reports sales but this is manual so I would like to pick up any inaccuracies through reconciliation.
Outside of VBA and Macros, is it possible to get excel to report data sheet by sheet onto one in a continuous order?
I have a worksheet with a lot of different conditions applied to a lot of cells. I'd like to do some housekeeping, but to do that, I would like a clear and complete list of all cells that have conditional formats attached to them, and what the formula/criteria is for applying said format. I don't even care what the formatting is, but that would be nice too.
So, when I go to my "Conditional Formatting" screen, I get something like:
Now, this doesn't show me much of anything about what the formula actually is. I need to select it and look around. I'd rather just get a full listing of what the rule says...
And where I might have the same rule applied to different ranges of cells And so on.
I need the VBA script in excel that will do a SQL Query, for this case I need to select a value where there are 2-3 tags and between certain period then display the result as a table in a worksheet.
Example I have a database with 3 types of tags "Tag A", "Tag B", and "Tag C", and each tag have a value with different timestamp. the database looks like this:
No. Timestamp Tags Value
1 1-Jan-2010 Tag A 18
2 1-Jan-2010 Tag C 20
3 2-Jan-2010 Tag A 20
4 3-Jan-2010 Tag B 17
5 3-Jan-2010 Tag C 19
6 4-Jan-2010 Tag B 18
7 4-Jan-2010 Tag A 20
8 5-Jan-2010 Tag A 22
9 5-Jan-2010 Tag B 18
10 5-JAn-2010 Tag C 20
I'm trying to compare 2 worksheets with the same headers(NAME, ADDRESS, CONTACT NO...). What codes should I use that when there is a duplicated row in worksheet A and worksheet B, it will be deleted and when there are 2 names with different addresses and/or contact no, the whole row in file A will be obtained. Output should also be in another worksheet. It goes like this.
worksheet A
NAME ADDRESS CONTACT NO
JOE ABC 123
MIL XYZ 567
NIK LMN 234
NED QRS 456
[Code]...
So the output should be:
worksheet C
NAME ADDRESS CONTACT NO
JOE ABC 123
MIL XYZ 567
NIK LMN 234
NED QRS 456
JIM JKL 345
SAM FGH 789
Is this possible in vba?
I have 2 problems relating to LOOKUP.
Not sure if Excel can perform these calculations as they could get to complex.
Problem 1
Can it be possible to have excel look at data from one cell reference another cell then display the results from the cell next to it in another cell, sort of example:
Tab 1 (Never changes)
AB
Bob1
Jon2
Fred3
Tab 2 (Dynamic, changes each week)
AB
Jon
Fred
Bob
So it would work as follows.
Tab2 column B will take Tab2 column A’s data check Tab1 column A and display Tab1 column B’s result.
Problem 2
Weekly league rank table that shows position movements week by week
Example.
Week1
1Jon
2Bob
3Fred
Week2
1FredUp 2
2BobNot Moved
3JonDown 2
Can Excel calculate/show the actual movements of league positions?
Sheet1 is a list of names, then a few columns of numbers.
Sheet2 contains calculations based of the numbers in sheet1.
Each sheet from 2 on is names after the names in sheet1 column A...
A large cell at the top of each sheet contains the formula to display the sheet name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
So what I want to do now is...
in cell, look for the cell in sheet1, and display the value in the cell offset by X so i can bring the numbers from sheet1 over.
In plain english the cell would say like...
"look in sheet1 for "Name" then display the cell next to it"
I know to look in the next sheet its "=Base!B2" if i wanted b2 on sheet base.
I know to use "=OFFSET(Base!B2, , 1, , )" too look at B2 and go right 1 column.
But the issue is i dont want Base!B2.. i want whatever cell in Base column A that has the text that A1 has.
If a cell is not blank, then increase that amount by a percentage identified in another cell and display the value only without any formalas in a new cell. To this end I wrote this IF statement:
=IF(I2>0,CP9=(CA9*I2/100)+CA9,CP9="")
Observation: I see a FALSE in CS9 where this IF statement exists, however, CP9 does not display the required value.
I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
P.S. I have excel 2010 and windows 7
I have Excel 2000, recently when I format a cell to display mm/dd/yyyy and enter mm/dd/yy, it is displaying dd/mm/yyyy. Or it starts out correctly and during a future opening of file it displays incorrectly.
View 2 Replies View RelatedWhen inserting "1" in the cell under soup, i was thinking it will bring up a list of its ingredients and how much of that ingredient. The values are listed in the same spread sheet below.
The amount of the ingredient is in D50 (80grams) and the item type next to it in E50(onion)
Is it possible to put in "1" soup then it displays all the items in both those cells range example(D50-E50 to D60-E60)?
Is it possible to put the value "2" in soup and it shows double the measurements? example (160grams) (onion)
I have a huge database. I am looking for a macro that can search for a particular word. If a match is found, the macro should do two things simultaneously... (i) delete the contents of the cell in which the match is found........(ii) display the same contents 1 row below and 1 coloumn to the left of this cell where the match is found. For e.g....
View 9 Replies View Related