Cell Reference Moves When Data Table Updates
Jul 7, 2013
I have a data tabel in Sheet 1 & Sheet 2
In sheet 3 they are combined via reference in order for me to do calculation in the input - They are combined in sheet3 as the columns are not identical.
Because the data-tables will be updated via sharepoint, i have to link to cells beneath the data-tables as i expect these will be fill out when the tables update.
My problem is that when the tables update, i dont see the updates in my "combination" sheet. it appears the the data tables add a new line, which moves my cell reference 1 line down.
Ex: Had a reference in Sheet1'E3 - after the data table updates the reference is now Sheet1'E4.
View 2 Replies
ADVERTISEMENT
Nov 19, 2008
I have two worksheets in my workbook;
1. Table
2. Raw / Source data.
I have entered all the formulas into my table (sheet 1) referenced to the source data but I want to upadate the source data regularly but when I delete sheet 2 all the formulas come up with #Ref error. how i can update my source data so my table automatically updates
View 2 Replies
View Related
Jan 27, 2014
I have a data table that looks like this:
RATE
Monthly
289.68
1.00%
262.81
2.00%
276.04
[Code]....
all the RATE numbers are manually entered.
now, if i reference the 3.00% number under the RATE column to the INPUT cell that I entered when I setup the data table, this happens:
RATE
Monthly
289.68
1.00%
262.81
[Code]....
this is obviously not correct, it is the value for the entry above.
and...
if i make ALL of the RATE cells then relative to the 3.00% one... (in other words 2.00% is actually =A5-0.01 and 4% is actually =A5+0.01)
i get...
RATE
Monthly
289.68
1.00%
262.81
2.00%
250.00
[Code]...
this is weird b/c the number for the first one and last one never change, but all the ones in-between change.
incidentally, the formula which is being solved is simply a 30k loan pmt for 10yrs ( =PMT(RATE/12,120,30000)*-1 )
View 5 Replies
View Related
Oct 2, 2009
I have to keep a record of the running totals of school house points for each week. The problem is that teachers are very lazy and don't record data every week so I have many blank cells which my current formula can't cope with. I've tried using N/A but it doesn’t seem to work? (Have thought about threats of violence but would probably lose my job) I’m if there is no data (blank or 0) then I need it to keep the same total in the cell as the previous week and so on until new data is entered and updates the total. I have attached a simplified copy: Teachers enter points in the HP sheet, the Running Totals sheet (TAB) contains the formula.
View 4 Replies
View Related
Jun 30, 2009
I want to create a table that updates automatically with the name & highest score top.
I.E. currently I have :-
Dates starting in A4,
names B2 : K2,
scores B4 : K4,
Not too fussed about having the date in the results table but i'd like something like :-
Scott 4 points,
Geoff 7 points
Tony 2 points,
I would like a table to automatically sort a table (preferably on sheet 2) so it looks like :-
Geoff 7
Scott 4
Tony 2
Is this possible ?, I only have 6 rows of data so far so it can be re-arranged if needs be and data will be added each week.
View 10 Replies
View Related
Mar 11, 2009
I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:
View 2 Replies
View Related
Jan 25, 2010
I have attached an excel sheet that takes 2 inputs in cells A2 and A3, sums them in cell A6, and then writes the outputs in cells A9 onward, recording the new sum as changes are made to cells A2 and A3.
Below the last output in the list (Cells A9 to ...) I want to have a Sum of the above outputs. In other words, I want a sum of the previous outputs, and I want it below the final output, moving and recalculating each time another change is made to A2 or A3. How can I do this?
View 2 Replies
View Related
Apr 30, 2009
i have csv data which has splits cells due to commas between numbers, and then moves data further to the right, is there a way to fix this, so for example this is what some cells look like
31874.90 JPMORGAN
but need to be like this
31 874.90 JPMORGAN
View 9 Replies
View Related
May 6, 2014
I know how to make a dynamic chart that shows only the last X number of rows. However, I'm looking to do something slightly different.
I have columns A and B both with 3000 rows of values. I would like to make a bar graph. Some of the rows are NA(), some have the data that I would like to graph. The data I would like to graph is always in a group, say from rows 100 - 200, but this could change in location from rows 95 - 195 instead, and could also change in number of rows to be rows 95 - 150 (ie. 55 rows rather than 100 rows). I would like my bar graph to be able to search Columns A and B and pick out the section with data (ie. not NA()) and graph it. I had introduced the NA() hoping that I could just put the entire range in the chart and it would pick out the numbers, but I gather this only works when using a scatter graph which just won't work for my needs.
View 4 Replies
View Related
Aug 15, 2007
I have a Workbook (only 1 sheet) that is filled out each month and consists of sales and revenue data. I need to copy the total sales and total revenue and paste them to another sheet. The only problem is that it is never consistent as to which cell the totals will be located in. They will always be in the same column, but almost never in the same row.
For example, one month there will be 8 individual sales and the total will be in Cell F13, but the next month will be F20. THe same needs to be done with Total Revenue (which is always one cell right of the Sales Total) I need to copy and paste the cell with Total in it (in this case F13) and do the same for each month after. This is not difficult to do in Excel, but i need some code to include this process in a macro.
View 4 Replies
View Related
Mar 19, 2013
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
View 3 Replies
View Related
Apr 4, 2009
I would like to make an excel worksheet that can do the following:
At the beginning of the worksheet I have a reference block w/ 2 rows, 4 columns of number of 1-8 (randomly, input manually). Then, I would have the same block (i will call these member blocks)(2r, 4c) repeatedly vertically (maybe 10~12 times etc...) and these block will have 2 rows in between to seperate each other.
Then, right on top of each of the member block (the row above the member block) there will be input space where i will manually input numbers (4 at most ~ also 1-8). Each of the number i manually put in will place into member block with its location reference to the reference block at the beginning. (and each number will be highlighted in one color, so 4 colors are needed.) Finally there will be several of these on a page. attached is a sample of what i kinda want but they are all input manually.
View 4 Replies
View Related
Jan 30, 2014
I know how to turn a column reference in a structured data table into an absolute reference:
=Table1[A] becomes =Table1[[A]:[A]]
However, how to make a cell reference, like this one, absolute to that it still locks on this row, column A when I drag it across. I don't want to use copy & paste, as I have other cell references that i need to leave dynamic.
=Table1[[#This Row],[A]]
I've seen that @ can lock rows, but I don't seem to be able to lock an individual cell.
View 2 Replies
View Related
Dec 12, 2008
I have a worksheet that uses a lot of vlookups and I have to update the filenames and locations quite often. I would like to update a single cell rather than updating every single formula.
However I am getting the #VALUE error when trying this.
Simplified Example:
I have: =VLOOKUP(A3,[Table.xls]Sheet1!$A$1:$B$4,2,FALSE)
I would like to place [Table.xls]Sheet1!$A$1:$B$4 into a cell (D1) for example.
And have my vlookup function as =VLOOKUP(A3,$D$1,2,FALSE)
This way I only have to update D1 when I want to change the filename instead of a whole lot of functions.
View 2 Replies
View Related
May 27, 2009
Sheet 1 contains:
Item Sheet 2
ABC =vlookup(A2,'Sheet 2'!$A:$B,2,false)
Sheet 2 contains:
Item Data
ABC 2
I'm trying to get the vlookup to return the value "2"
Right now, I'm manually entering the tab name in the vlookup function, even though it's contained in cell B1.
The tabs are contained in the same workbook if that matters. Since this workbook is growing rather quickly, this is a painful process and doesn't feel very scalable. Since I'm using a mac, I need to do this with functions vs. macros. Does anybody know how I can reference a cell for the name of a tab in the vlookup function?
I was thinking I could maybe somehow do this with the INDIRECT function but I'm stumped.
View 3 Replies
View Related
Nov 23, 2012
I have made a pivot table which I use each month by just updating the source data, however I was trying to have a cell in another workbook say "=A31" but it keeps going "=getpivotdata(...specific name".
My usual practice was to copy the top ten rows of my pivot table into another file but I am trying to erase this step.
View 1 Replies
View Related
Jan 12, 2009
I have a series of cells that can be auto filled based on the initial response in cell B8. For example, if cell B8=ABC, then cell B22 should be auto filled with 123, and cell B24 should be auto filled with Yes and cell B56 should be auto filled with the word Hosted.
View 5 Replies
View Related
Jun 22, 2009
Is it possible to have a workbook (1) with a list on it and another workbook (2) which has a dropdown list on it that shows the list on the 1st workbook. But when a new entry is made on workbook (2) it is added to the list on workbook(1)
View 8 Replies
View Related
Jul 1, 2014
I'm trying to create a simple Excel spreadsheet (not looking for Access or complex relational database stuff) to have the following:
A tab with data about a person : name, date of birth, join date, and a list of sessions they attend (eg. Monday Morning, Monday afternoon etc).
A different tab in the same sheet, or a form to select from that list of people, and allow changes to the information, and the ability to save it back to the data tab. Ability to add new people or delete people would be useful.
I am looking to create this as a basis for managing sessions, creating a register etc....
Any simple spreadsheet which I can use as a starting point, or to use as a reference in getting it established?
View 3 Replies
View Related
Jan 2, 2010
The following formula is in cell A3: A3=IF(A2="X",A1+365,""). Rather than A3 displaying only 1 year, I would like for it to update after that day occurs to =A1+730, and then A1+1095, and so on.
View 2 Replies
View Related
Sep 14, 2008
I have created a macro that copies information from various cell on one sheet to another. When I run the macro the screen flickers about 5 times. Is there a way to stop.
View 5 Replies
View Related
Aug 9, 2012
I want to reference a table (not a pivot table) from another Excel file. I want it to update automatically if the original table is changed in any way.
View 3 Replies
View Related
Dec 12, 2012
i want to match a cell data with a range of cells and if matches return the cell reference in another cell
View 3 Replies
View Related
Jul 12, 2008
I have a workbook that starts the beginning of the month by entering daily hours in cell D3 (Day 1 in cell D3, day 2 in cell E3, day 3 in cell F3 etc). Column B has several codes, but the one code the macro looks at when going down the current day is a letter "W" for "Worked". Therefore, Rows 4, 5, 9, 12, 56 (examples only - it changes daily) etc. could have a "W" and when the macro is ran, evertime it sees a "W" it includes the hours found in row 3 of the applicable day i.e. starting on row 4 the formula is =if($B4=D$3,D$3,""). This copies to the bottom row using the shortcut (Ctrl + Down Arrow) to find the bottom. What I have done is entered Zeros all the way down and changed Zero Values so they don't show.
Where I get in trouble is if a zero is removed, the shortcut stops at that break thinking that's the bottom. The bottom moves as we remove equipment out of the line up or add new equipment. What I am trying to do is have Excel figure out where the bottom row is for each daily calculation when the macro runs down the daily column.
View 9 Replies
View Related
Dec 7, 2006
I've a master sheet called records. It consolidates all the data from various worksheets. Overtime, the lists gets very long. So I'm proposing this. I want a macro which - Shift all the data yearly into a new directory at
G:MMTPMVarious StockArchive. When it saves at this directory it should bear the year as the folder name. The dates are retrieved from column H. - it should be operated at the every end of the year.
View 4 Replies
View Related
Jun 19, 2008
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?
View 9 Replies
View Related
Sep 21, 2013
I have a number in X11 that can be anywhere from 1 to 26.
In Y 11 I want to reference Column A with the Row being the number in X11.
For example if the number in X11 is 13, I want Y11 to reference Cell A13, or if the number in X11 is 14, then Y11 would reference A14.
View 5 Replies
View Related
Oct 20, 2006
In design mode on my UserForm, I have an object on top of all other objects (it's hidden until a button is clicked). That's how I designed it and it was working fine when I ran the form. Now, when I run the form and click the button, the object appears at the bottom below all other objects. I've tried closing Excel & re-opening it, setting the ZOrder in design mode and in the CommandButton code--nothing works. If I did do something to cause this--I have no idea what it was. I've been moving and re-sizing some objects, but not adjusting the ZOrder.
View 9 Replies
View Related
May 21, 2008
I have a userform in wb "A" that I key data into. When I hit the 'submit' button I need the data to go to wb "b", ws "data" and go to cells in columns "CA-CK". All that I can do, when the data goes to wb "b" it need to load to the row that has the same date as the date I enter in textbox1 on the userform. The date column in wb "b" is column BX.
View 9 Replies
View Related
Sep 4, 2009
I wish to create a button which will allow the user to goto the page in my spreadsheet.
I no i can use macros or hyperlinks to do but i can only use this button once for it to work. I want to cut and paste the button to save time.
View 10 Replies
View Related
