I have a cell range of L3:N3 on Sheet 1 and on Sheet 2 in A1 i have the value of N3 of Sheet 1.
Now, if I move L3:N3 to eg. R10:T10 I want the value A1 ( Sheet 2 ) to be updated to T10 ( Sheet 1 )
Right now, it wont update dynamically, value of A1 stays at N3...
I have a Big Question about the SMALL Function in Excel. I would like to know if it is possible to use a syntax that will make the small function update my graph dynamically.
Personnel transfer out of the Department on know dates... but they come in to the Department all the time. I would like to know if I can increase the "range of my data" even if I don't have data in the cells (i.e. B2:B6 have data, I want the range to include B7:B16, which has no data)... When I have no data in the cells I get errors/ circular references...
Ideally, I would like be able to add or remove personnel and the SMALL function would "organize/update" my graph dynamically. Is this possible?
I'm trying to create a chart and set its source dynamically.
I've tried two methods, the first by setting the XValues of a chart's series collection to a range specified by cells,
ActiveChart.SeriesCollection(1).XValues = range(Cells(9, coloffset + 4), _
Cells(9 + numvsteps, coloffset + 4))
And by using the chart wizard.
Call vchart.Chart.ChartWizard( _
Sheets(resultssheet).range(Cells(9, coloffset + 6), Cells(9 + numvsteps, coloffset + 6)), _
xlLine, , xlColumns, _
Sheets(resultssheet).range(Cells(9, coloffset + 1), Cells(9 + numvsteps, coloffset + 1)), , _
False, "End-End Eff., " & current & "A", "Line Voltage", "Eff. [%]", "test")
Setting the XValues property gives an "Unable to set the XValues property of a series class".
Using the chart wizard, trying to set the category labels parameter of the chart wizard to a range doesn't give an error message, but does leave the category labels section of the chart (when I click and view it) blank.
I've also tried
resultssheet = "Sheet1"
tempstring = "='" & resultssheet & "'!" & "R9C" & (coloffset + 1) & ":R" & (9 + numvsteps) & "C" & (coloffset + 1)
ActiveChart.SeriesCollection(1).XValues = tempstring
When I step through the code, tempstring is "='LineReg Results'!R9C1:R19C1", which is correct, but I still get an "unable to set the xvalues property of a series class" error.
Interestingly, if I record a macro of me setting the category labels, and play it back, I also get the error.
I have a table which takes the average of the last three years. The formulas are in cells below the data. The data is set up to be first data down to the oldest data. each time data is added, a row is put in on top. how do i get the formula in the cell to update dynamically. What would the average formula look like in the cell? Do you just say =average(name of file!RANGENAME).
View 5 Replies View RelatedI'm am trying something that is still a little over my head...
Normally, I merge cells the easy way:
I have a 'receipt' worksheet with about 500+ lines of data, here is an example of what could be in cell A166: 1.1 NET_AMOUNT 742,523,253.83
In another worksheet, is there a way to look at the whole worksheet for column A in receipt to say:
if 1.1 is true, capture text to the right of 'NET_AMOUNT' then format using =Dollar?
I have problems with dynamically adding and removing checkboxes. I have a form and there is a button to add more rows to the table. Every row includes a checkbox too.
So first i add a row, and then add a checkbox to a specified cell in that row.There is a button for removing rows as well (witch should removes checkboxes as well).
My problem is when i ad the checkbox the linked cell property only works for the first one.When i add the second row the linked cell of the firstly added checkbox changes to the one in the new row and the new checkbox has no linked cell. I am adding the stuffs as follows:
VB:
Private Sub addBtn_Click()
Dim y As Integer
y = findFunc("end") // Y define where To insert the New row
Cells(y, 11).EntireRow.Insert
Cells(8, 11).Copy
[Code] .....
Any way to add checkboxes dynamically.
I'm hoping this is a simple question someone might help clear up for me.
I've defined a few cell ranges on a worksheet (in my example I've defined their names as site1, site2, site3, site4, site5). All these ranges are identical in # of columns and rows.
On a separate worksheet, I'm trying to display a single named range, depending on the value in a dropdown menu.
In my example, I have a pulldown menu with the following items: site1, site2, site3, site4, site5 (mirroring the named cell ranges). I'd like to display one of these ranges, depending on which name is selected in the dropdown.
I am creating a budget spread sheet, where a different sheet is used for every month.The months are divided into weeks of the year (week 1, 2... 52), but it also includes weeks where only a few days of the month occur (eg. Feb 2012 - week 1 includes 30 and 31 of Jan, then 1, 2, 3, 4, 5 of Feb and week 5 includes 27, 28, 29 of Feb and 1, 2, 3, 4 of March). Now, since week 5 of January and week 1 of February will be the same, I would like to link those cells, so if I was to change the cell value in January, it automatically changes in February. The issue is, that I also want to make it so that if I am to change the cell value in February, it alters the cell in January (so cell F33!Jan = F33!Feb and F33!Feb = F33!Jan).
View 2 Replies View RelatedI have been tasked with coming up with a solution to an excel issue my boss has. I'll try to explain it as simply as I can but it might get confusing.
We have workbooks containing ledgers for a retail establisment. We create a new book for each year. Each book contains a sheet per month. We also have a comparison book/sheet. Currently we have the rolling total for each line item transfer over to the comparison sheet and then manually workout and enter the data for the same day from the previous year. What we would like to do is have the data from 2008 automatically update as we update the 2009 data.
This what my excel sheet looks like that i am using the date ant time cell is set by using the now formula to get the current date and time, but I only want the date tiem change for each row when it changes.
For example when the data for the first row changes all the dates and time change, but I only want the effected rows time to change not all the times and dates, this progam is set up to monitor inventory and when some makes a change to the inventory I need to now when the that data was recorded but do not want the users to have to enter the data and want it to be enter automatically that is why i am using the now function. The data may not change at all for a couple days that why I need to be able to keep the Date data from changing automatically.
Bin #ProductAmount
D-21Red Lentils 3/47/7/09 1:54 PM
D-22Red Lentils 1/27/7/09 1:54 PM
D-23Yellow Peas 3/47/7/09 1:54 PM
D-24Yellow Peas 3/47/7/09 1:54 PM
D-25Yellow Peas 3/47/7/09 1:54 PM
D-26Yellow Peas 3/47/7/09 1:54 PM
D-27Yellow Peas 3/47/7/09 1:54 PM
D-28Yellow Peas 3/47/7/09 1:54 PM
D-29Yellow Peas 3/47/7/09 1:54 PM
D-30Yellow Peas 3/47/7/09 1:54 PM
Let’s say I have a 20x6 array. For a known value in column 1, I could find the corresponding value in any other column by using the vlookup function. What function might I use if I wanted to locate a value in column 1 and update the cell in the same row in column 4 and column 5?
View 4 Replies View RelatedHow do I link two cells so that when I change one value the other changes and vice versa?
How about 3 cells?
Example:
Cells A1, A2 and A3 are all the same part number, but in diffrent catagories. If I used a part and reduce my number in A2 I want it to reflect the change in A1 and A3. The next day I use a part and reduce it in A1 and I want the new value to reflect in A2 and A3.
I can make it so A2 will equal A1, but if I was to update the value at A2 it overwrites the formula.
What I'm trying to do is this:
In sheet "Available", I have a list of all shirts available in store. I often make a printable shopping list in sheet "Shopping list". Once the shopping is done, I would like to automatically add the shirts bought to "Available" sheet.
I have attached a workbook that has 2 documents in it. The first sheet is an inventory list. It is formulated so that when somebody enters amounts in the "Count, Used and Restocked" fields the "Ending Total" shows the final count. I would like this sheet to auto update the "Starting Count" field and erase the "Used" and "Restocked" fields so the next time the document is opened the "Starting Count" would be the same as the "Ending Count" from the last time the document was edited and the "Used" and "Restocked" fields would be blank. I assume this would be done with a Macro but I'm a Noob so I might be wrong.
View 7 Replies View RelatedI am entering data (exam results) in one sheet for a form class (each form has a sperate sheet) but want this data to be updated in another sheet called "combined" which is a full list of all the data for the year group of students. How would I do this? I have copied the information into the combined sheet using paste special but it wont update?
View 4 Replies View RelatedI want the system to update the date or date and time in some particular cells when i am updating /using a particular. i written a code
But neither gives an error nor it gives any values. my code is below. Also i want the time details to be fixed meaning once i enter cell a1
it produces the current time / date , it should not be editable again
[Code]...
Anyway, I have 70 Sheets ( Tabs ) which when i'm finished will have the same cells in the same location of each sheet, referring back to a different line in a Data sheet at the beginning.
IN order for me to get there i need to edit each of the relevent cells but changing the formula so that it reads the next line down. I was thinking i could maybe get a macro whicle will increment each of the formula cells contained with a Named Range, this would make it more accurate than using the Find/Replace method of updating the formula's on a per cell basis....
I have two spreadsheets which carry the same information in three cells. Instead of just placing a link, =B2 etc., I would like to have the cells linked to each other through code.
For the one sheet I can use this with no problem, but as soon as I place this worksheet change event in the other sheet but reverse the cells references Excel gets unhappy.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Target.Parent.Range("B2, M2, N2")
[Code]....
Any proper way to update a cell no matter which spreadsheet the change takes place?
Sheet1 >> change is made to cell B2 and that same value is applied to Sheet cell C4.
I've created a bit of a monster Excel model taking in an enormous range of inputs and outputs. Most of the formulas are vlookups and sumif's. The workbook is now about 15meg. I've got autocalculate turned off because of the great deal of time it takes to recalculate. When I do try and re-calc some of the cells update but very many of them do not change at all. If I then go into these cells and edit them (F2 enter) the correct output is given! If I close the book and re-open it normally shows the correct outputs, after a while it goes back to the error above.
View 3 Replies View RelatedI have cells (range named Formulas) in a worksheet A (wsA) linking to another workbook. This workbook has several sheets, each a different forecast. From a Radio Button Group on wsA I would like to choose forecast, FC1-FC3, and then all the cells in the range Formulas would be updated with the choosen FC.
I would like a VBA script to handle this:When the workbook is activated the present Forecast in the Radio Button Group should be stored as a variable (oldFC)When I choose a new forecast, the value should be stored in a new variable (newFC)I then want to loop through the whole range (Formulas) and update each cell and it's part of string value to the newFC
Before update:
wsA cell N3='H:[5500-plan-06.xls]FC 3'!N$16
wsA cell N4='H:[5500-plan-06.xls]FC 3'!N$17
When new Forecast is choosen this should be update to:
wsA cell N3='H:[5500-plan-06.xls]FC 1'!N$16
wsA cell N4='H:[5500-plan-06.xls]FC 1'!N$17
I am using this formula to calculate a column of numbers that are both negative and positive numbers. =SUMPRODUCT(--($A$5:$A$9647<=TODAY()),--($A$5:$A$9647>TODAY()-365),$C$5:$C$9647). I need to keep this formula the same where it will calcuate on a rolling 365 day but I need the total to be only the absolute value (abs).
View 2 Replies View RelatedAttached simplified sheet gives background - but need to update the cells in the aggregate monthly table C44:C55 based on data in single cell above, and according to the correct month. Need do this without the previous months data changing.
Month is determined by cell E8 and data to be updated to the agg month range is to come from cell C15. The month date will change automatically as will the data in C15.
I'm using a total of 20 Rows and 2 Columns. Each row has Column A for Description and Column B for Score. There are total 10 subjects with 10 noneditable rows, and 10 editable rows.
I'd like to protect column A and B for rows 1 to 10 and unprotect only Column B for rows 11 to 20. When any cells from Column B, rows 11 to 20 updates, I'd like to automatically sort columns A+B for ONLY rows 1 to 10.
Therefore, rows 1 to 10 need to be sorted based on the scores on Column B, and since rows 11 to 20 are just input fields, they are never to be sorted.
Column B for Rows 1-10 will be formulas and are based on Column B values from rows 11-20.
When a value in any of the rows 11-20 of Column B is updated, Range A1:B10 will automatically sort from highest score to lowest score (Column B).
Since Column B for rows 1 to 10 contains formulas and not actual values, would it throw off the order of things when sorting formulas?
the macro coding to perform this task.
Here is just a visual of what the spreadsheet will look like
....A B
=======
1||A (B11)
2||B (B12)
3||C (B13)
4||D (B14)
5||E (B15)
6||F (B16)
7||G (B17)
8||H (B18)
9||I (B19)
10||J (B20)
11||A 10
12||B 9
13||C 8
14||D 7
15||E 6
16||F 5
17||G 4
18||H 3
19||I 2
20||J 1
The Bold are editable fields. and I want to use auto sort A1:B10 based on updated values of B11 to B20.
I have also attached the basic excel file that situates my concern.
I have a command button that runs a macro to insert two new rows between row 15 and 16 no real problem (see code & sheet below), but I would like the Total Hours and Total Cost rows to include the newly added rows i.e.
Rows 16 & 17
Cell B18 now contains =B2+B4+B6+B8+B10+B12+B14+B16
and
Cell B19 now contains =B3+B5+B7+B9+B11+B13+B15+B17
If another two rows are added then Total Hours and Total Cost Cells 20 & 21 will now be
Cell B20 now contains =B2+B4+B6+B8+B10+B12+B14+B16+B18
and
Cell B21 now contains =B3+B5+B7+B9+B11+B13+B15+B17+B19
Command button Code to insert row
Code:
Private Sub CommandButton1_Click()
Const fWhat As String = "EXTERNAL"
Dim sR As Range, fR As Range, fAdr As String, nRw As Long
Set sR = Range("A1:C187")
[Code] ..........
Worksheet Below
1
Hours
170
Cost
387.99
[Code] ...........
I am copy/pasting a new column of data (F). I have three formulas MIN,AVE,MAX watching each row in the columns "=MIN(B2:E2)" or example. When I insert the new column F, the formulas give me the "Formula Omits Adjacent Cell" warning. I don't want to turn off the warning; I want to know if there is a way for the formulas to automatically include the new data? Do I need a Worksheet Event for this?
View 3 Replies View Relatedwhy I must update all cells, with formulas, manually after I do something with the workbook. I'll try to make an example.
If I make a macro that enters =sum("F1:F10") it says #NAME? when its done, but if I dubbleclick it and press enter it calculates the cell like its supposed to. I've tried to press alt + ctrl + F9, but that doesnt work.
Is there a command to dubbelclick the entire workbook and then press enter if you know what I mean?
I have the following code in Sheet1 for file Book7.xls
Private Sub Worksheet_Calculate()
On Error Resume Next
For Each rcell In Range("A1:D6")
Select Case rcell.Value
Case Is >= 5
rcell.Interior.Color = vbBlue
Case Is < 5
rcell.Interior.Color = vbRed
End Select
Next rcell
End Sub
Range A1:D6 is linked to an external source file which I am changing every now and then.
A1 has the formula:
=[Book8.xls]Sheet1!A1*1
Copied all the way to D6.
[Book8.xls]Sheet1!A1:D6 presently has all values 1 hence the formatting in Book7 is all red.
Now I an changing source file to Book9.xls (thru EDIT menu->LINKS). [Book9.xls]Sheet1!A1:D6 has all values 10. But when I change external source to Book9.xls, the right formatting did not apply. All are still red (instead of blue). I still need to go in one of the cell and press F2 and F9 to trigger calculation.
How do I change the code in order to recognise the calculation in order to trigger the codes in the event?
I am trying to use a row of cells as update cells, where the user inputs an amount into greyed out cells, which in turn updates Sheet1!$I$2:$I$11, from which it will update other worksheets that are currently in progress. but I do not know how to go about it. Is it possible?
For each amount the user enters (for each Code Number) a date will be displayed below the amount.
I have used the function = now() to have the most updated time but it updates a workbook when I open it in the first place. How I can avoid this?
View 9 Replies View Related