Make Selected Contents Appears Automatically According To Weeks In A Sheet?

Mar 13, 2013

I have a sheet named PE which is the main source of handling a contents in other sheet.

How to make a selected contents in PE sheet appears automatically according to weeks in sheet1?

How do i use data validation, offset or combo box to solve this problem?

View 7 Replies


Automatically Clear Contents Of Cell When Selected

Oct 13, 2009

Is there way to automatically clear the contents of a cell when the user clicks in that cell. Eg, click in "A1" then then "A1" clears automatically.

View 10 Replies View Related

Referencing Selected Tabs To Print Selection & Update Table Of Contents Sheet

Nov 30, 2009

I searched but didn't find exactly what I needed. I have a workbook with 31 sheets. It is a price guide with each category on a separate worksheet. I figured out how to list the sheets on a separate tab. What my client wants is the ability to:

1. select certain categories for printing, the ToC, Cover and backcover pages have to print in every case

2. the ToC has to change depending on the sheets selected.

I'd rather write some code and give him an an easy command button rather then teaching him how to select non-concurrent sheets and printing only active sheets.

What I'm really stuck on is the updating of the ToC with active sheets only (category and starting page which changes depending on pages selected).

View 13 Replies View Related

How To Automatically Run Macro On A Sheet Once Selected

Feb 14, 2012

I'm trying to run a macro called ClearDataSoFar whenever I leave a sheet and come back to it. I think it needs to be within a Worksheet_Activate statement, but not sure how to flesh it out.


Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.ScreenUpdating = True
End Sub

View 6 Replies View Related

Displaying Weeks In Selected Month?

Feb 18, 2014

I have a workbook that needs to display the relevent weeks in month, the dates would be the start and end dates of each wekk (monday to Friday) but also need to take into consideration, partial weeks i.e. only a wednesday, thursday and friday at the start of the month given where the first available date falss on.

see the attached sheet for reference, I am looking to do this with as few formulas.

It also needs to show a list of monday only dates for the selected month.

View 7 Replies View Related

Automatically Sort Column When Sheet Selected/Activated

Jun 3, 2008

i want to automatic sort data very time i open the file or any change in column b.

i have data in column B from B1 to B100

how to collect data from Column B and place them in Column A star from A1 because Column B not Organized!

I mean B1 have data
B2 have null
B3 have data
B4 have nul .... etc

i want to display all Column B in Column A one by one to be Organized.

View 4 Replies View Related

Automatically Count Weeks

Jul 26, 2008

Heres the deal: based on weekly reports, I need to count and sum orders created in our CRM.

Im exporting, once a week, a full report of opportunities created in our CRM. Week after week I copy&paste the changes to my main file or dashboard. This allows me to see, manually four values my sales manager wants:

1. Opportunities created in the last week
2. Opportunities created in the last 2 weeks
3. Opportunities created in the last month
4. YTD

I dont need help with the last one, thats the easy part. The thing is, I have to do this manually. Every thursday I run the report, export it, find the new opportunities and add them to my main report. Then, I just modify an already defined IF formula that counts and a SUMIF one that sums the values, so it will take into account only the last week, the one before that one and the whole month.

So, my questions are...

1. I need to set different formulas for count and sum, thats clear, but how can I make this autimatically without having to change the formula each week?

2. Do I need to consider the date my main file is modified, and count backwards?

As usual I am not sure Im being clear, though I hope Im getting better at this. Im attaching an example where B2:D7 is similar to my main report, and G4:M12 is my DataTable.

The formulas in C4, C5, C6, D4, D5, D6 should be "automatic", so when I copy&paste the extra rows from the weekly reports into my datatable, those cells will count and sum without me having to change the period in the formulas.

View 9 Replies View Related

Copy Data From One Sheet To Another Automatically Based On Number Selected In Dropdown Control

Apr 25, 2013

I'm trying to copy data from one sheet to another sheet automatically based on a number selected in a drop down control.

I have used OFFSET to pull some information successfully from a list. This is easy for me to do with single entries and a list and has worked well. I want to do similar but with a dataset not just single cells.

But, I can't seem to make OFFSET work to show a set of data easily.

Perhaps I will need VB code to do so? Or there is another control I'm not aware of? I've done very little with VB.

I'll attach a sample file to try to show better what I'm trying to do.

View 7 Replies View Related

Distribute Project Hours Automatically Across Weeks

Mar 27, 2013

How to achieve the same as in the linked thread below, but in weeks instead of months? I'm very insecure about how excel deals with weeks.

[URL] .....

View 1 Replies View Related

Merge The Contents Of Selected Cells

Feb 9, 2010

this is my first post in this great forum. let me start talking about what I need, I used to convert some statement from PDF to XLS format for my work purpose. sometimes I am facing a problem that some text in the statement will come in more than one row, actually I need it to come in one cell but it is coming in several rows that mean, if I want to sort or filter my data, I will lose some important data. What do I want???

I want a macro to merge the contents of selected cells and put it in the first cell of the selected cells then clear the other cells. I will use this macro for the following cases: Case 1- assume we have this data in any sheet.

D1=you? ( all without any space )

after selecting the said cells and applying this macro, the result will be:

B1=How are you? ( one space only between the content of the selected cells )

Case 2- assume we have this data in any sheet.


View 2 Replies View Related

If Statements: Option Of Between 3 Weeks And 6 Weeks

Sep 7, 2009

i currently have a what if statement

=IF(B2>=NOW()-42,"< 6 wks","> 6 wks")

I need it to also give an option of between 3 wks and 6 weeks

Therefore all options are

3-6 WKs
< 3 WKs
> 6 WKs

I have mastered 2 but now need a 3rd.

View 7 Replies View Related

Fill Drop Down Box With The Contents In A Selected Column

Jul 20, 2009

I would like to fill my dropdown box with the contents in a selected column (removing duplicates).

View 13 Replies View Related

Move Contents Of Manually Selected Cell

Aug 22, 2009

I have an array, lets say B1:D20. I would like to be able to manually select any cell within this array and then run a macro which will place the contents of the selected cell into the cell in column A in the same row as my selection.

View 3 Replies View Related

VBA - Userform: Click In A Text Box, Contents Is Selected

Mar 20, 2007

I'm building a userform (for the first time), and I'm working with a textbox control. I'd like to make it so that when the user clicks in that textbox, the contents is selected, so that if they begin typing, it will type over what is already there.

My question, specifically, is whether or not there is a property I can set to make this the default behavior, and if not - what event do I tie to this action? Is it the "Enter" event?

What I have now, which will probably be unacceptable to my users, is code that looks like this:

Private Sub tbName_Enter()
ActiveControl.Value = ""
End Sub
This makes the contents disappear, but I'd rather they stay there and just become "selected".

View 9 Replies View Related

Taking Value From Another Sheet And Multiplying (cost X Hours) And Combining Days / Weeks

Jun 18, 2014

I have tried to make a sheet which has

Resource, Task, Rate (I'd like this to be on another sheet, as there is a lot of repetition) Also, right now the columns for hours are based on weeks, but i'd like to do it by days and then collapse by weeks, and months and get totals by weeks and months too. Then, I'd like to multiply the rate by the hours to find the total number of hours, however the way it is done right now is not very intelligent and very time consuming when creating the formula, which looks something like this :


what can be done to make this a bit better?

View 2 Replies View Related

Is There A Way To Make A Field/button To Go To Column A For The Row Selected

Jan 23, 2009

I'd like to create a go back field/button/something that would allow a user (who is on a row, in a column very far right) to click to go straight to column A for that selected row.

View 2 Replies View Related

Make A Row Disappear When Marked If The Column Is Selected?

Dec 22, 2012

I run a football sweepstake involving 120 people. Each week everyone picks a premiership team who they think is going to win. If that team loses or draws they are out. This carries on until there is one person left and they win the money. I need to make a table with all the premiership teams and names down the x and y axis. When the team is marked as "lose" everyone who has chosen that team gets deleted.

View 5 Replies View Related

Save File To PDF - Only One Sheet Appears?

Mar 3, 2014

I have six sheets in the work book, but one of them spills on to more than one sheet. If i print it or use print preview it displays all the sheets but when converting to pdf with below code only one sheets worth appears, then it displays the next sheet in the workbook, how to amend this so that if it spills over it all converts to pdf

The worksheet in question has 2,000 plus rows, and depending on what the end user selects, it can contain anything between 15 and 300 of those rows (it is a picking list). i currently have it set so that it prints to paper with five different sections for various product types and a summary page, each section has a named range and i have assigned macros to buttons such as immediately below.

Sub printoutsummary()

View 5 Replies View Related

If ValueA Appears In Different Sheet, Sum ValueB With Conditions

May 31, 2009

Im having difficulty writing a formula to solve the following dilemma:

I have Sheet1 with staff details and each staff member may be listed more than once. I need to use columns C (StaffID) and L (Load). Load values may only be 1.0 or 0.5.

Then I have Sheet2 which is being used to compile data from a range of sources this is where I need to put my formula to get my answer. Sheet2 has column A (StaffID) and each ID will only appear once on Sheet2.

The formula required needs to solve if the StaffID from Sheet2 appears in Sheet1 (keeping in mind it may appear multiple times), sum the Load values for that StaffID provided the Load value on each line only equals 0.5".

If the StaffID doesn't match, the cell return is to be 0.

I will then need to do this again in another cell where the Load value only equals 1.0 (obviously just amending the condition value in the formula).

View 11 Replies View Related

Delete Rows So That Student Only Appears Once On Sheet

Feb 13, 2014

I use this code to create sheets for courses but the student appears more than once - see attached Sample (Before sheet).

How can the code be changed so that the student only appears once on the sheet? - see attached Sample (After sheet).


View 2 Replies View Related

Macro To Print Specific Sheet If Name Appears In Cell

Nov 13, 2013

I am trying to print one of the Sheets in my Workbook by looking at a specific cell that can have one of the sheet names.

View 7 Replies View Related

Automatically Make New Tables?

Nov 10, 2009

I got a excel file which i use to add invoices on. But these invoices are numbered and in a book.
1 book has 50 pages, i have to write the amount of $$ on it. But i made this file to enter the amount of money on the invoice. And the sheet automatically gives me the total amount of money which was earned when the book is full.

Now i was wondering if its possible to let excel somehow add 50 new lines with the same formulas used in the first 50 lines. So once line 50 has info enter, excel will add 50 new ones with the correct info(book and page numbers).

Can excel do this with some sort of macro?

View 14 Replies View Related

Excel 2007 :: VBA To Make Command Button Inactive Until User Field Selected

Nov 14, 2011

I am creating a userform in Excel 2007 which requires a user to pick their name from a drop down box then press Ok, what i want to do is disable the Ok button until the user field has been selected.

View 1 Replies View Related

Automatically Clear Contents Of Cell Range

Jan 17, 2013

Is is possible to clear the contents of a cell range if a message box appears stating an error. I have tried the following code but the logic doesn't actually work and am struggling to achieve what I am trying to do.

My code so far is as follows:


If ActiveSheet.Range("F84") > 0 And ThisWorkbook.Worksheets("PES").Range("D24") = 0 Then _
MsgBox "Your Entitlement is currently 0", vbCritical, "Error"

The message box appears fine if the IF statement is true but if IF statement is false, the cell range of K84 to T84 on the current sheet is still cleared..

View 2 Replies View Related

Make Data Recur Automatically?

May 13, 2014


this is the set of data i have

My problem is now i have to add data of in such a way that if a entry comes in with and a role already present there in month of jan like happened there with in the month of jan and role sr.development engineer it should automatically add data from past month and give value.

I already tried some if and funtions and combination of vlookup.

View 1 Replies View Related

Make Cells Shift Down Automatically

Jun 18, 2009

i need a button to copy the content of A8:C28 and paste in another section of the worksheet, i get the button to copy and paste the selected range, but when i copy and paste again it overides my current paste, i need it to paste my new selection underneath my previous paste.

View 9 Replies View Related

Automatically Color Coding Based On Cell Contents

Jan 28, 2013

I would like to automatically color the fill of a cell based on the cell contents.

For example, if the cell content is "1" I would like the color of the cell to be cyan and if the cell content is "2" I would like the color the cell to be red.

I've tinkered with several different formulas but I just can't get anything to work.'

View 2 Replies View Related

Copying Cell Contents From Another Spreadsheet That Automatically Update

Apr 26, 2013

I am working on a manpower spreadsheet and need for one spreadsheet to extract data from another and automatically update if it is changed and I am struggling below is an example of what is on spreadsheet 1:


and the information from spreadsheet 1 is copied to spreadsheet 2, however if amendments are made to spreadsheet 1 they are automatically updated on spreadsheet 2:

Spreadsheet 2:


View 1 Replies View Related

Rearrange Selected Columns Automatically

Jun 21, 2013

Daily I am getting a file which have so many columns with different names. I used to select and move my required columns to separate place. For exp.

I have the file with various columns, such are.

Sales Qty, Year, Tax, Company Code, Discount Paid, Sales Amount, Company Name

The above column order are not constant, daily the order will changes. But we have to make the order as below

Company Name, Company Code, Year, Sales Qty, Sales Amount, Tax, Discount Paid

Like this we have so many columns in the file and we used to rearrange as required.

Any formula or any macro to avoid this manual work with error, so that i will save my time and free from error.

View 2 Replies View Related

Automatically Scroll Down When Cell Is Selected

Jun 8, 2009

I have a report with 5-8 sections on it. What I am trying to do is scroll down the worksheet when the first cell in each section is selected so that the user can see the whole section. I tried a something like this

View 3 Replies View Related

Copyrights 2005-15, All rights reserved