I've got a workbook for aesthetic reasons having text entered by users directly into protected autoshapes. I wanted to link text in one autoshape with another. If that was not enough I wanted to carry forward that text to another autoshape if a different autoshape did not contain text!
View 11 RepliesGot a workbook with two sheets. Under Sheet1: I create two Autoshape Bevel's. One is to display the two pictures, one is to hide the two pictures. If I hit a Bevel 1 or Bevel 2, I want the code to be written into the Workbook so when I open it up again it will remember what settings I had.
Under Sheet2: In the VBA code I have. I want to put a Two IF statments in here to make the logical decisions for me. I have tried it but they can't recognize the AutoShapes. I was using "ActiveSheet.AutoShape("Autoshape##")_Click", but it was not picking up a variable.
Private Sub Worksheet_Activate()
ActiveSheet.Shapes("Picture 32").Visible = True
ActiveSheet.Shapes("Picture 33").Visible = True
ActiveSheet.Shapes("Picture 32").Visible = False
ActiveSheet.Shapes("Picture 33").Visible = False
End Sub
I link the two bevel's on Sheet1 to run the Macro on Sheet2!Worksheet_Activate. How do I find the name of the Bevel's? Can I use If Statements, so make decisions on which Bevel was pressed, and retain the settings for the next time I open the Workbook ?
I have a chart which has Autoshapes linked to other cells in the spreadsheet above each bar on the chart. Is there any way of making them dynamic, so that if the value of the bar changes, the Autoshape will move up or down with that bar?
View 4 Replies View RelatedJust trying to work out the best way to be able to determine autoshape colours using cell values. For example I have 12 shapes that need to coloured either Red, Green or Orange depending on a specific cells value, 1 for Red, 2 for Green and 3 for Orange.
I have sort of managed to successfully complete this for 1 of the shapes, by using the RGB Fill option (Using 3 = Red, 4 = Green and 45 = Orange). But cannot for the life of me work out how to do this for multiple shapes using different cell triggers. Below is the code that I currently have and the spreadsheet that is linked to:
I have a line graph which I have annotated using the autoshapes function in Excel. Various high points and low points have notes attached in order to make the graph clearer to clients.
Unfotunately, if I want to update the range of the graph (say to include two months instead of the usual one,) none of the autoshapes move.
I thought that I could use Excel... All I want to do is to use cell data to inform an autoshape, e.g. the length and width of a rectangle, etc. without resorting to VBA.
View 7 Replies View Relatedfill color of autoshapes on condition depend on cell value
View 1 Replies View RelatedI have created a 2 drop down list with varios sizes of steel in 1 and quantity in another.
what i want to do is assign a figure in the steel list e.g 203x203x46 UC = £20 per metre and in the quantity list it will just have the quantity required then i will create a cell with the total, so it would go something like (203x203x46 UC = £20 per metre, quantity = 6, total = £120.
I am trying to have a cell in a diffrent work sheet display information from another sheet.
I entered ='Weapons Table'!A48 and it is displaying in text not what is actually in that cell.
on the cell above it I have entered ='Weapons Table'!A47 and it displays fine.
I've tried typing = then just clicking the cell, and it is still just displaying it as text. I've spent 3 days on this sheet and have only 1 error, that is driving me crazy.
how I can change the color of an autoshape based on a value in a cell?
View 4 Replies View RelatedI'm trying to link values from one worksheet to another (in same workbook). I'm nearly there, but falling down on one area. Say on sheet2 I have these values(in columns A and B):
C1 100pf
C2 100pf
C3 100pf
C4 100pf
C5 100pf
C6 100pf
C7 100pf
C8 do not fit
C9 do not fit
C10 do not fit.............
I have one cell with $20,000 hard coded (cell A1). In another cell I want the cell to say "20,000 Capital Raise" (cell B1). And i want B1 to link to A1 so that if i change the number in A1 it will also change in B1.
Here's what i have in B1 so far: ="$"&J6&" Capital Raise"
This produces "$20000 Capital Raise" in cell B1.
So the only thing i'm trying to figure out is how to get the comma in the $20,000 so it will read "$20,000" instead of "$20000".
I want the contents of the text box (which will be input by the user) to update a cell in another worksheet. I have found numerous examples of how to display the contents of a cell in the text box but I want to know how to display the text box contents in a cell.
View 9 Replies View RelatedWhy the heck every time I enter in to the "edit text" area of the chart title box in a chart in Excel 2010 and type "=B27" (without the quotation marks, and understanding the contents of "B27" has the text I wish to display) does this idiot thing simply display "=B27" (again, without the quotation marks)???
View 4 Replies View RelatedIt is quite standard to create links between workbooks, and generally I do this by inserting an "=" sign in the cell I want data to appear in for Workbook 1, I then open workbook 2 where the desired data is and click on the cell housing the data I want.
I just went to do this, and upon putting the = sign in the cell I want in workbook 1 and clicking in workbook 2 nothing happens except my cursor is now in workbook 2 and the = sign just remains alone in the workbook 1 cell.
If, however, I put an = sign in a cell in workbook on and then click another cell in that sheet or a cell in another worksheet but in the same workbook a proper link is created, so the problem seems to be isolated to links to other workbooks.
I've attached a basic excel file with an example of the problem I need to solve. As you can see, if you select "No" to "Do they have a pet?" then conditional formatting blocks out the next field. The problem with that is, it still allows you to enter a value into the "Type" field which is skewing my results. Is there a way to allow a value in "Type" ONLY if there is "Yes" in "Do they have a pet?". I would also like it to delete the value in "Type" if I later select "No" instead of just covering it up. Is this possible? I'd like to avoid the scripting route if at all possible but I'm not sure it is...
Also, I have two linked spreadsheets, one pulls the totals off the other. However, If I add rows into the original it does not update the total formulas in the second sheet. Is there a way to make it do this automatically?
I am working with excel2003. I have to different workbooks in which on is a Bill of lading that is used to show the items being shipped. The other workbook has a list of the items that we ship.
On the BOL I want to be able to type in the "L" number (or I.D. number) in D14, D19, D24, D29. By typing in the L-number I want it to automatically bring up the product name into cells E14, E19, E24 and E29. With the exact name of the item it will help prevent employee error. I want it to also bring over the exact weight over from the flavor list in cell range C5:C28.
I have the FlavorCodes range with all the L-numbers on the BOL in cell range U9:U48. I thought I had it set up correctly but when I select the L-number in the drop down list in cells E14, E19, E24 and E29 I get error code #NA. I thought I defined the cell range but it is still not working.
The workbooks are attached below.
I have a problem and wanted to know if there was an easier way to create links to various worksheets. I have a folder with 59 worksheets. The names of the Worksheets are the weeks of the year(example LA-01-01-09) I have another workbook with 13 worksheets one for every month and a sheet that totals the whole year up.
What I want to know is how can I create a link to everyone of those weekly sheets to go to my monthly sheets,so when data is entered into the weekly sheet the monthly sheet will update automatically? The weekly sheets are setup exactly the same. I'm looking only to pull row 3 columns c:d:f:g:h:i over to my monthly sheet.
I have an example of my weekly sheet and monthly sheet.
I have a Form that when the 'SUBMIT' button is clicked, I would like to validate the information and then write it to a 'MASTER SHEET'. However, the validation has gotten lengthy and I have opted to put the validation code in a separate Module to keep my general code a little cleaner.
PROBLEM:
The original code would kick you back to the form if something was wrong via a 'END SUB', but as now the SUB is on a different Module, it isn't working.
Here is the pertinent code on the general code sheet: ....
I want to pull data from about 100 different Excel files. They are located in
the same folder and drive and the data I want is in consistent cell
locations. I would to find a fast way to reference the files. something like
S:FolderJanuaryCaseNumber1.xls that will allow me to pull-down the
folumula and copy the month and case number from two separate fields on my
spreadsheet. So I would specify the month and case numbers and have them
automatically populate my link formula.
in each cell I have formatted a Workday formula which is for example:
=WORKDAY(D16,3)
Now I want to add to that function
"If D16 is blank return blank"
What I am getting at the moment is that the if D16 is blank I am getting #VALUE!
THis makes the sheet very hard to read.
If possible I would like to create using vba a button at the end of my rows of data allowing a user to copy and insert the row next to the button, i.e. duplicate.
I am using the following code to create the button but am not clear how to link a button to a row or cell. The button would be within a cell in a row at the end of the data.
Range("a2").Offset(rowoffset, 10).Select
ActiveSheet.Buttons.Add ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height
rowoffset increments within a loop and a button is placed at the end of each row of data.
I would also like to know how to name the button.
I run a number of spreadsheets each 4 week period and these are stored in a folders named after the period. ie Period 10 will contain workbookA10wk1, workbookA10wk2, workbookA10wk3 and workbookA10wk4,
Period 11 will contain the same workbooks but named workbookA11wk1 etc.
The data from the workbooks is then used in graphs / tables for each year.
I always pick up the same cell ref in each workbook to compare each week and my table is set out with heading of period and wk.
Is it possible to use the table headings to produce the names of the workbooks that you wish to reference, ie: ='Period 13[workbookA13wk1.xls]Sheet1!$D$17 to appear under Period 13, wk1
I am working on a project and if i hit button one in my VB program i need it to pull up a program under c:programfiles/user/name.xls
how do I go about linking cells?
Example:
Lets say I have a workbook with 6 sheets on it and the sheets names are:
Sheet1
Sheet2
Sheet3
Sheet4
Sheet5
Status
And we will just use cell A1 through sheet1 to sheet5 and the status sheet to grab the numbers from sheet 1 through sheet5
Sheet1 through sheet5 will have numbers in cells A1
Sheet1 : 586
Sheet2 : 436
Sheet3 : 621
Sheet4 : 610
Sheet5: 561
And in the status sheet I would like to find the highest number from these 5 sheets and put it in cell A1 status sheet and have it linkable like when it finds the highest number it will put it in cell A1 on the status sheet and when I click on the A1 cell in the status sheet I would like it to goto that sheet that has the highest number it found and maybe highlight it or something.
I have a multi-tabbed spreadsheet that keeps crashing when I try to set up a reference from one tab to another tab in the same spreadsheet (e.g., =Sheet2!B1) . The spreadsheet does NOT have any macros in it although my Personal.xls DOES have numerous macros that I have been using for over 6 years with no problems. In any ase, when I start Excel without loading personal.xls, so that NO macros are loaded, the spreadsheet still causes Excel to crash when I try to set up the reference to another tab.
View 9 Replies View RelatedIs it possible for me to link to other spreasheet data, and have the links update (retrieve the data) without those files being opened?
View 9 Replies View RelatedI have a workbook Book1 that has 4 sheets, one of those sheets needs to be an exact replica of a sheet from another workbook Book2. I tried to directly link the data and use Vlookup's but the problem is that when someone opens Book2 and inserts more data (Inserting rows) it does not show up on Book1.
View 3 Replies View RelatedI have sucessfully created a spreadsheet which links to another using a vlookup and an array. I now need to reverse the process. My requirements are this:
I have individual spreadsheets which represent individual projects/quotes for our customers. I'd like each of our sales staff to have a summary spreadsheet which summarises all their ongoing projects in one spreadsheet, dynamically.
when a new project is created on a new (projects/quote) spreadsheet, it looks to the summary spreadsheet and looks to see if that project exists in the summary, if not, it creates a new line and writes the summary details to it. then as the project spreadsheet evolves, it updates its details on the summary sheet dynamically.
I compile a report each month. I have a program/macro built to do this so based on the month I select it runs the code below. The code below would run if i selected January. I run this report on a spreadhseet and its for a ton of people, and my code is really long. I know there is someway to shorten it, but i cant figure out how. Below is an example of one persons, and the next persons would begin on the row below the example.
Sub January_NB()
'Week1
'Christie
Sheets("New Business").Activate
With Sheets("New Business")
Range("B8").Select
ActiveCell.FormulaR1C1 = _
"='\lm-file-dfs-01centralPermktUnd-Prod-CtrsProperty Count SheetsNew Work ReportNB Team Count sheetsChristie[Count Sheet January Christie.xls]Tally'!R73C4"
Range("D8").Select
ActiveCell.FormulaR1C1 = _
"='\lm-file-dfs-01centralPermktUnd-Prod-CtrsProperty Count SheetsNew Work ReportNB Team Count sheetsChristie[Count Sheet January Christie.xls]Tally'!R6C4"
Range("E8").Select...........................