Auto Referencing The Date To A Table
Sep 24, 2008
On XL2003 i am putting together a worksheet that has a list of tables by month i.e. a table for January then a table for February below it and so on...
Within each table the data is referenced as follows:
=SUM('V:Product Testing InformationAuto final test stationsTest ResultsSAP Files[Test Data070801.xls]Sheet1'!$AV$18)
How can I change the highlighted date to reference a cell containing the date? i.e.
=SUM('V:Product Testing InformationAuto final test stationsTest ResultsSAP Files[Test DataA2.xls]Sheet1'!$AV$18)
In cell A2 would be "070801"
View 9 Replies
ADVERTISEMENT
Aug 1, 2008
I have been struggling with my Excel 07 Date format. Nearly every time I perform a calculation, enter in a value etc in a spreadsheet my answer is returned as a date. So for example if I enter in a cell: = 5 - 4. I get the answer "1-Jan-1900" instead of 1. I have to manually set the formats to general or number if I want the correct format. The setting seems to be the default one and occurs for all workbooks I open. What can I do to change this.
View 5 Replies
View Related
Jun 6, 2014
I am trying to take the year from today's date then subtract away a date from a referenced date in a separate workbook. Then I want to put this result into an IF statement. For example if the result is between the number 1 and 4, display 1, if the result is between 5 and 9, display 2, etc. Right now, I'm simply trying to reference the separate workbook for the date and subtract that from today's date, but I'm stuck because I only get the date's serial number back.
View 1 Replies
View Related
Jun 26, 2014
I have a spreadsheet with two sheets in it.
Sheet 1 = sheet with formulas/functions for data analysis
Sheet 2 = sheet with table
I am trying to reference a column in the table on sheet 2 from sheet 1. Specifically, I am trying to set up Data Validation that will create a list of unique values from a column in the table. I tried:
[Code] ......
There is a space in the header name of the column. but I was unable to get the validation to work even with columns that have just a single word name.
I also tried:
[Code] .....
and that did not work either.
View 3 Replies
View Related
Mar 16, 2014
I am trying to pull data from different Tables within a workbook by typing the name of the referenced Table in a cell rather than leaving it static withing a VLookup formula.
So far I have this:
=VLOOKUP($B6,OctTable,MATCH(D$4,$D$4:$V$4,0),0)
What would I do if I wanted to be able to dynamically change the "OctTable" portion with the name of another table which I type in call A6? I have researched as best possible and think that it might have something to do with INDEX, but I have failed to figure it out.
View 1 Replies
View Related
Aug 20, 2014
The macro I have has 2 arrays that at present has a representative sample of the data I will be looking for and hence allocating a relevant code. I thought the if MyArr was now Sheets("Sheet2").range("A1:A200) and similarly for MyAssettype using column B. however i just get a Runtime error!
View 5 Replies
View Related
Jan 3, 2014
I'm attempting to list my data... I have a dynamic table that has the following columns:
number of countries
country 1
country 2
country 3
1
Egypt
3
USA
Egypt
Scotland
2
Scotland
USA
As you may have guessed, I want trends on these countries. That would be easier to do if I had a single "Country" columns but I have to work that way.
I would have wanted to work with a pivottable (because they're so "user-friendly" (not always though^^)) but I didn't find a way to do it.
The reason why I want to work with a pivottable is to be able to link my countries results to the rest of the table.. If that's not possible, I'd still want to be able to reference them and say "USA, egypt and scotland pop up 2 times"
I found the following formula (in E2):
Code:
=INDEX(Table1[country 1]:Table1[country 3];MOD(ROWS(E$2:E2)-1;ROWS(Table1[country 1]:Table1[country 3]))+1;INT((ROWS(E$2:E2)-1)/ROWS(Table1[country 1]:Table1[country 3]]))+1)
Which works but I have to manually click-drag that cell down to complete my list. I'd rather have it grow automatically if I have to work that way.
View 1 Replies
View Related
Jun 12, 2013
I have created a table in Excel 2010 (pls see attached table named post.xlsx).
Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).
Question:
I have received income data for another month - the new month is 13 and the corresponding new income is 100.
I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.
Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".
Specific Question:
The newly-typed column in Excel table is not get updated in PowerPoint table.
View 2 Replies
View Related
Feb 1, 2010
I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).
Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest)
The problem is i will get "....A file name already exist...do you want to overwrite.." prompt.
Which defeat the automate process.
Any other solution to enable the automatic refresh on open the excel workbook?
Or Access can overwrite the exist file or save it as another file name with timestamp ?
View 14 Replies
View Related
May 25, 2014
I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 1 Replies
View Related
Sep 26, 2012
I have a worksheet ("VillageReport") that contains several pivot tables. All tables are from the same data source and all pivot tables have "Location" as the first column field. I would like for the tables on this sheet to automatically be filtered when I select a village name from a drop down menu in a cell on this worksheet (C1). So, for example, when I select "Tarzana" in C1, the location field of all pivot tables on this sheet will show only Tarzana (all others are deselected).
a Macro for this? (I have already set up the drop down menu in C1). I have found some examples online but can't seem to adjust the code to fit my workbook. I am not very familiar with VBA.
View 9 Replies
View Related
May 13, 2013
I have a table below that looks like this:
LOC
# of days
DTX
3
RTC
3
PHP
12
IOP
12
The # of days column will be a manual input. Then I have a larger table that will take those values and convert them to look like this: (Obviously the dollar values are pulled from somewhere else)
1
DTX
$ 1,292.00
2
DTX
$ 1,292.00
3
DTX
$ 1,292.00
[code]....
I've been thinking of trying to use a macro but not sure that is the best way. Using a button or something doesn't seem very elegant either.
View 7 Replies
View Related
Jun 26, 2006
I have a cell that mentions the year (G7) and the sheet mentions the pay period beginning (ex: January 1).
I would like to have a different cell (B9) combine the two into a date that other cells would recognize as such.
For instance, if the sheet name is January 1st and cell G7 has "2006" as a value, then I would like B9 to read "1/16" (year not shown). From there another cell (A13) would show that day/date as "Sunday, January 01, 2006".
View 11 Replies
View Related
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
Jan 21, 2010
I'm trying to hone a quoting tool to generate an amount based on a given range and minimum charges for specified subsects of the range. If you reference the attachment I'm trying to calculate a charge in cell K15 based on values in B9 and C10 referencing the table I8:R9. What I cant figure out is how to create a minimum charge of 50 for J8:P9 (points A-G) and a charge of 95 for Q8:R9 (points H-I).
View 2 Replies
View Related
Jul 5, 2014
Attached example.
I trying to populate table on sheet "1" from the training log sheet. Based on person name. Eg on sheet 1 i select Chris Williams and it will populate the table with courses he done from table on training sheet.
I tried and failed using vlookup and index match, but think that's what i need to be using.
example.xlsx
View 2 Replies
View Related
May 26, 2014
i am trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 4 Replies
View Related
Jan 9, 2013
I have a rolling 12 month (each day in column) tab in Excel 2010 that references variances by ID number in column A, and the column headers are each day for 366 days (2011). I would like to create a tab that shows the past 10 day's variances by ID number. I haven't been able to write a formula that will look at the date headers and the row ID number to return the figure for that day. Here is a sample of the 12 month rolling and the 10 days at a glance that I want to populate.
Rolling 12 Months
IDName12/25/201212/26/201212/27/201212/28/201212/29/2012and so on
1234Employee Name - - - - (11.07)
1235Employee Name - - - 0.20 -
1236Employee Name - - - - -
1237Employee Name - - - - (1.00)
[Code] .........
View 4 Replies
View Related
Nov 19, 2009
I have data going in to a small table which has some empty rows as that data is not yet available... My problem is, I need to sort this table in date order but with the date nearest to today's date at the top...
The sort function puts oldest at the top or oldest at the bottom which is no good for what I need...
I use xl 2003.
View 9 Replies
View Related
May 23, 2006
I've written a fantasy football programme, however i'm having trouble getting the table to auto sort. alter one of the values in the wins tab, then goto the scores tab to see that they don't auto sort.
View 5 Replies
View Related
Mar 14, 2014
In the attachment you will see an example of what I am trying to accomplish. What I am trying to do is find VBA code that is either specific to this worksheet or in a macro. When the sheet is opened I enter a date in B2. I then enter data into A7, B7, and C7. What I would like to happen is when the data is entered into A7, B7, and C7 the sheet goes and finds the same date that I entered in B2 and copies that data from A7, B7, and C7 into F7, G7, and H7.
View 2 Replies
View Related
Jun 18, 2014
I'm looking to set up a table in Excel (or possibly Access that is then referenced through Excel). But end goal would be to be able to type in an Item Number and then Excel automatically fills in the Description, weight, price and other information about that item number. That way, I don't have to type in "11108.1" and "Computer Mouse" and "$15.98". I want to only type in "11108.1" and it knows to fill in the other information. Is this possible in Excel? I've searched through many functions and haven't found it so I'm thinking it may require a combination of formulas.
View 1 Replies
View Related
Mar 16, 2014
I have a adjusting table which auto sorts based on the latest weeks results.
If one of the results is an N/A (There is no data for the site that week, the table will auto sort this value to the top. How do i get it to move any errors to the button of the table?
I've attached a sample worksheet : Table example.xlsm
View 1 Replies
View Related
Mar 8, 2012
I have a summary worksheet, which has a drop down cell. on another tab I have a few pivot tables, is there a way I could get one of the pivot tables to auto filter based on the selection in the summary worksheet?
View 1 Replies
View Related
Aug 27, 2009
I have my projection formulas that use the date and age to estimate production on each farm. Basically the age is calculated first and then the production rate is estimated. I have been trying to figure out if, instead of having a formula in each cell, can I use VBA to populate the ages and then the production.
Here is an example:
8/22/2009
FarmAge8/22/09Age8/29/09Age9/5/09Age9/12/09Age9/19/09Age9/26/09Age10/3/09Farm 1
34181351813617937179381793917940177Farm 2
39223402204122042220432204422045218
All the functions I have created work, I just can't have the worksheet auto-calculate due to the total number of cells with functions. Calculating the worksheet takes approx 1 min...
I should be able to adjust any code to the ranges needed, but this example shows the basic layout.
View 17 Replies
View Related
Nov 15, 2006
I have a football pontoon league that works on goals scored, what I am tring to do is to be able to auto-sort the league on the right of the document when I amend the numbers on the table on the left of the document.
View 9 Replies
View Related
Mar 1, 2008
Saw quite a few threads on auto sorting but nothing that solves my problem: Sheet 1 has two columns, column A has names and column B has numbers. These numbers are manually entered, and there are duplicate values.
Sheet 2 has to present the same names and numbers in ascending sort order. I cannot use macros due to security restrictions. I can accomplish it using a combination of functions LARGE and INDEX, if the values are not duplicated. But that technique fails when there are duplicate values.
In other words, Sheet 1 has:
NAME VALUE
Bob 5
Joe 2
John 5
Sue 3
and Sheet 2 should auto sort these values and give us:
NAME VALUE
Joe 2
Sue 3
John 5
Bob 5
View 3 Replies
View Related
Mar 18, 2013
I manage a team that quote for business. They are targetted on number of quotes per month but then also wins per month, however a win may come a number of months after the original quote was generated.
They work off a spreadsheet where they log:
Quote date (And month)
Quote Value
... customer info etc
Win month (against orginal quote information)
Win value (against orginal quote information)
I started writing my pivot using the Quote month as the main reference point, IE Quote Month in the Row, and then put all the data in the central drop data section... However, there are two immediate problems with this:
They are targetted on quotes generated per month, some business won has come from last year, which they are lifting from the old spreadsheet and puttin in my new one... meaning that quotes they generated in Oct 2012 are now showing up as quotes for October this year.
If the wins span a number of months they are showing in January (if quoted in January) and not in March (when actually won)
Is there a better way of writing my pivot table??? There must be... ideally what I would like is half the table referencing the Quote date and half referencing the order date... but I don't know how to do this? I could probably do this with pages but I would like to show all on one sheet...
View 2 Replies
View Related
Jul 30, 2013
I have got the following problem. I have got a table which looks like this:
=Time!A1
=Open!A1
=max!A1
=min!A1
=Close!A1
When I mark the selected cells and scroll down using the autofill option I get this:
=Time!A1
=Open!A1
=max!A1
=min!A1
=Close!A1
=Time!A6
=Open!A6
=max!A6
=min!A6
=Close!A6
Why does it give A6? I want A2 instead of A6 because I want to create a table from A1 to A500 but without these jumps from A1 to A6.
View 5 Replies
View Related
Dec 9, 2009
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.
View 9 Replies
View Related
