Getting Data From Another Sheet/page 


Getting Data From Another Sheet/page  Excel 
View Answers 
Hi!
I have 3 tables, Table1 is in page 1.
Table2 and Table3 are in page 2.
Table2 gets a value from Table3 and Table1 gets a value from Table2.
However my problem is that no matter which values i input in Table3, it always show the value 0 in Table1.
If it matters, here are the formulas my tables use
Table1[Utgifter] = SUBTOTAL(109; Table2[Utgifter])
Table2[Utgifter] = SUBTOTAL(109; Table3[Utgifter])
Table3[Utgifter] = [a]  [b]
('a' and 'b' are row values in Table3).
So why do i always get the value 0 in Table1 and how can i fix this?
Thanks!
I have 3 tables, Table1 is in page 1.
Table2 and Table3 are in page 2.
Table2 gets a value from Table3 and Table1 gets a value from Table2.
However my problem is that no matter which values i input in Table3, it always show the value 0 in Table1.
If it matters, here are the formulas my tables use
Table1[Utgifter] = SUBTOTAL(109; Table2[Utgifter])
Table2[Utgifter] = SUBTOTAL(109; Table3[Utgifter])
Table3[Utgifter] = [a]  [b]
('a' and 'b' are row values in Table3).
So why do i always get the value 0 in Table1 and how can i fix this?
Thanks!
Similar Excel Video Tutorials
Page Setup Across Multiple Sheets
 Page Set Up Across Multiple Sheets Or To Another Workbook. Headers and Footers.
Highlight all the sheets you want to add Page Setup to and then a ...
Highlight all the sheets you want to add Page Setup to and then a ...
Print Labels On Each Page For Large Spreadsheet
 See how to Print labels at the top of each page or to the left of each page. See how to set print area.
Sheet tab in the Page Setup dialog box al ...
Sheet tab in the Page Setup dialog box al ...
Page Setup
 The Excel Basics Series shows a systematic description of what Excel can do from beginning to end.
#17 Video topics:
1)Fix Column Widths
#17 Video topics:
1)Fix Column Widths
Excel Efficiency: Proper Data Setup, Then Lookup From Different Sheet
 Move football betting data from master sheet to individual sheets when the data is not setup properly. See how to:
1)Setup data correctly
2) ...
1)Setup data correctly
2) ...
Helpful Excel Macros
Basic Web Query in Excel  Import Data from the Web into Excel
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Print Specific Pages in Excel
 This free Excel macro allows you to print a prespecified selection of pages from Excel. This means you can print 2 pag
 This free Excel macro allows you to print a prespecified selection of pages from Excel. This means you can print 2 pag
Hide Formulas in a Worksheet and Prevent Deletion
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
 This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Replace Formulas with Values (For The Entire Workbook)
 This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
 This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Get Values from a Chart
 This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
 This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Similar Topics
Hey Guys I Have I Q
I'd Like To Make To Make Every Thing Variable In "Gray Table" So I Can Choose According To My Need I Work
Ex
1 Ranges Variables So I Can Choose The Range I Need
2 Look Up Table Variable So I Can Choose Between Tables I need
3 Color Every Range With Specific Color Through Drop Down List Color
Sheet1
A B C D E F G H I J K L M N O P Q 1 A1 C 700 Range Tables Color Table1 Table2 Table3 2 A2 C 701 First LookUp A1 A4 Table1 Blue Sky A1 C 700 A1 Z 500 A1 Y 300 3 A3 C 702 Second LookUp A5 A13 Table3 Red A2 C 701 A2 Z 501 A2 Y 301 4 A4 C 703 Third LookUp A14 A18 Table2 Yellow A3 C 702 A3 Z 502 A3 Y 302 5 A5 Y 304 A4 C 703 A4 Z 503 A4 Y 303 6 A6 Y 305 A5 C 704 A5 Z 504 A5 Y 304 7 A7 Y 306 A6 C 705 A6 Z 505 A6 Y 305 8 A8 Y 307 A7 C 706 A7 Z 506 A7 Y 306 9 A9 Y 308 A8 C 707 A8 Z 507 A8 Y 307 10 A10 Y 309 A9 C 708 A9 Z 508 A9 Y 308 11 A11 Y 310 A10 C 709 A10 Z 509 A10 Y 309 12 A12 Y 311 A11 C 710 A11 Z 510 A11 Y 310 13 A13 Y 312 A12 C 711 A12 Z 511 A12 Y 311 14 A14 Z 513 A13 C 712 A13 Z 512 A13 Y 312 15 A15 Z 514 A14 C 713 A14 Z 513 A14 Y 313 16 A16 Z 515 A15 C 714 A15 Z 514 A15 Y 314 17 A17 Z 516 A16 C 715 A16 Z 515 A16 Y 315 18 A18 Z 517 A17 C 716 A17 Z 516 A17 Y 316 19 A18 C 717 A18 Z 517 A18 Y 317 20 A19 C 718 A19 Z 518 A19 Y 318 21 A20 C 719 A20 Z519 A20 Y 319
Spreadsheet Formulas Cell Formula B1 =VLOOKUP(A1,Table1,2,0) B2 =VLOOKUP(A2,Table1,2,0) B3 =VLOOKUP(A3,Table1,2,0) B4 =VLOOKUP(A4,Table1,2,0) B5 =VLOOKUP(A5,Table3,2,0) B6 =VLOOKUP(A6,Table3,2,0) B7 =VLOOKUP(A7,Table3,2,0) B8 =VLOOKUP(A8,Table3,2,0) B9 =VLOOKUP(A9,Table3,2,0) B10 =VLOOKUP(A10,Table3,2,0) B11 =VLOOKUP(A11,Table3,2,0) B12 =VLOOKUP(A12,Table3,2,0) B13 =VLOOKUP(A13,Table3,2,0) B14 =VLOOKUP(A14,Table2,2,0) B15 =VLOOKUP(A15,Table2,2,0) B16 =VLOOKUP(A16,Table2,2,0) B17 =VLOOKUP(A17,Table2,2,0) B18 =VLOOKUP(A18,Table2,2,0)
Excel tables to the web >> Excel Jeanie HTML 4
I'd Like To Make To Make Every Thing Variable In "Gray Table" So I Can Choose According To My Need I Work
Ex
1 Ranges Variables So I Can Choose The Range I Need
2 Look Up Table Variable So I Can Choose Between Tables I need
3 Color Every Range With Specific Color Through Drop Down List Color
Sheet1
A B C D E F G H I J K L M N O P Q 1 A1 C 700 Range Tables Color Table1 Table2 Table3 2 A2 C 701 First LookUp A1 A4 Table1 Blue Sky A1 C 700 A1 Z 500 A1 Y 300 3 A3 C 702 Second LookUp A5 A13 Table3 Red A2 C 701 A2 Z 501 A2 Y 301 4 A4 C 703 Third LookUp A14 A18 Table2 Yellow A3 C 702 A3 Z 502 A3 Y 302 5 A5 Y 304 A4 C 703 A4 Z 503 A4 Y 303 6 A6 Y 305 A5 C 704 A5 Z 504 A5 Y 304 7 A7 Y 306 A6 C 705 A6 Z 505 A6 Y 305 8 A8 Y 307 A7 C 706 A7 Z 506 A7 Y 306 9 A9 Y 308 A8 C 707 A8 Z 507 A8 Y 307 10 A10 Y 309 A9 C 708 A9 Z 508 A9 Y 308 11 A11 Y 310 A10 C 709 A10 Z 509 A10 Y 309 12 A12 Y 311 A11 C 710 A11 Z 510 A11 Y 310 13 A13 Y 312 A12 C 711 A12 Z 511 A12 Y 311 14 A14 Z 513 A13 C 712 A13 Z 512 A13 Y 312 15 A15 Z 514 A14 C 713 A14 Z 513 A14 Y 313 16 A16 Z 515 A15 C 714 A15 Z 514 A15 Y 314 17 A17 Z 516 A16 C 715 A16 Z 515 A16 Y 315 18 A18 Z 517 A17 C 716 A17 Z 516 A17 Y 316 19 A18 C 717 A18 Z 517 A18 Y 317 20 A19 C 718 A19 Z 518 A19 Y 318 21 A20 C 719 A20 Z519 A20 Y 319
Spreadsheet Formulas Cell Formula B1 =VLOOKUP(A1,Table1,2,0) B2 =VLOOKUP(A2,Table1,2,0) B3 =VLOOKUP(A3,Table1,2,0) B4 =VLOOKUP(A4,Table1,2,0) B5 =VLOOKUP(A5,Table3,2,0) B6 =VLOOKUP(A6,Table3,2,0) B7 =VLOOKUP(A7,Table3,2,0) B8 =VLOOKUP(A8,Table3,2,0) B9 =VLOOKUP(A9,Table3,2,0) B10 =VLOOKUP(A10,Table3,2,0) B11 =VLOOKUP(A11,Table3,2,0) B12 =VLOOKUP(A12,Table3,2,0) B13 =VLOOKUP(A13,Table3,2,0) B14 =VLOOKUP(A14,Table2,2,0) B15 =VLOOKUP(A15,Table2,2,0) B16 =VLOOKUP(A16,Table2,2,0) B17 =VLOOKUP(A17,Table2,2,0) B18 =VLOOKUP(A18,Table2,2,0)
Excel tables to the web >> Excel Jeanie HTML 4
I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or viseversa.
I've tried the following but got an error stating it was too long:
= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))
The repeating line needs attention in my mind:
MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))
Thanks
I've tried the following but got an error stating it was too long:
= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))
The repeating line needs attention in my mind:
MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))
Thanks
I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or viseversa.
I've tried the following but got an error stating it was too long:
= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))
The repeating line needs attention in my mind:
MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))
Thanks
I've tried the following but got an error stating it was too long:
= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))
The repeating line needs attention in my mind:
MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))
Thanks
I've got a worksheet with several tables, each of which I've assigned a defined name. At the top of my worksheet I have several cells containing lookup functions, and these are repeated for each defined table on the sheet.
Right now I'm creating all my lookup functions for a single table, copying the cells containing these functions, and editing the references to the defined names in the copies by hand, giving me a final product something like this:
Code:
To get this result, I'd normally do all the formulas in Column A first, then copy these formulas to Columns BD, but doing that gives me the result below:
Code:
As you can see, I still have to go in to Columns BD and manually correct the table reference names. So, my question is this: is it possible to enter the text values Table1, Table2, etc. in Row 1, and somehow have all my vlookup functions extract the name of the table to use from these cells? I know I can't just do something like having the formula in A3 =VLOOKUP(A$2,A$1,2) since that will make the formula think that the range A1 itself is where I want to do the lookup. Any ideas?
Right now I'm creating all my lookup functions for a single table, copying the cells containing these functions, and editing the references to the defined names in the copies by hand, giving me a final product something like this:
Code:
A B C D 1 2 10 10 10 10 3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table2,2) =VLOOKUP(C$2,Table3,2) =VLOOKUP(D$2,Table4,2) 4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table2,3) =VLOOKUP(C$2,Table3,3) =VLOOKUP(D$2,Table4,3) 5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table2,4) =VLOOKUP(C$2,Table3,4) =VLOOKUP(D$2,Table4,4) 6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table2,5) =VLOOKUP(C$2,Table3,5) =VLOOKUP(D$2,Table4,5)
To get this result, I'd normally do all the formulas in Column A first, then copy these formulas to Columns BD, but doing that gives me the result below:
Code:
A B C D A B C D 1 2 10 10 10 10 3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table1,2) =VLOOKUP(C$2,Table1,2) =VLOOKUP(D$2,Table1,2) 4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table1,3) =VLOOKUP(C$2,Table1,3) =VLOOKUP(D$2,Table1,3) 5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table1,4) =VLOOKUP(C$2,Table1,4) =VLOOKUP(D$2,Table1,4) 6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table1,5) =VLOOKUP(C$2,Table1,5) =VLOOKUP(D$2,Table1,5)
As you can see, I still have to go in to Columns BD and manually correct the table reference names. So, my question is this: is it possible to enter the text values Table1, Table2, etc. in Row 1, and somehow have all my vlookup functions extract the name of the table to use from these cells? I know I can't just do something like having the formula in A3 =VLOOKUP(A$2,A$1,2) since that will make the formula think that the range A1 itself is where I want to do the lookup. Any ideas?
Hi, I need a way to select a discontinuous range in VBA with these circumstances:
The data I'm working with is grouped into 5 by 5 regions, each region with a header above it, stacked one above the other with an empty line between each
There are also several "columns" of these tables, although I could put them on separate sheets if that simplifies the code
What I want to do is find the maximum value within a given location in each table. By this, I mean that I will be comparing the value in 1st row in the 1st column of the 1st table with the value in the 1st row in the 1st column of the 2nd table.
I can't seem to upload files here at work, but the tables look something like...
Table1
1 2 3 4 5
6 7 8 9 0
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5
Table2
6 7 0 9 0
1 2 3 4 5
6 7 0 9 0
6 7 0 9 0
6 7 0 9 0
With the end product being
Table2 Table2 Table1 Table2 Table2
Table1 Table1 Table1 Table1 Table1
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2
Thanks in advance, sorry for the mess
The data I'm working with is grouped into 5 by 5 regions, each region with a header above it, stacked one above the other with an empty line between each
There are also several "columns" of these tables, although I could put them on separate sheets if that simplifies the code
What I want to do is find the maximum value within a given location in each table. By this, I mean that I will be comparing the value in 1st row in the 1st column of the 1st table with the value in the 1st row in the 1st column of the 2nd table.
I can't seem to upload files here at work, but the tables look something like...
Table1
1 2 3 4 5
6 7 8 9 0
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5
Table2
6 7 0 9 0
1 2 3 4 5
6 7 0 9 0
6 7 0 9 0
6 7 0 9 0
With the end product being
Table2 Table2 Table1 Table2 Table2
Table1 Table1 Table1 Table1 Table1
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2
Thanks in advance, sorry for the mess
My VBA is so rusty!! need help on the Findfirst syntax
ok I have two tables, table1 and table2. Both have a field called "CKey". Basically I am cycling through each record in Table1, to find the "CKey" in table2 and do something.
I have written:
table2.MoveFirst
Do While Not table2.EOF
Message2 = "Working on record " & counter & " of " & lastrec
DoEvents
strFindKey = table2!CKey
strCriteria = "Ckey='" & strFindKey & "'"
table3.FindFirst strCriteria
If Not table3.NoMatch Then....
I get this message "The Microsoft Jet Engine does not recognise "CKey" as an expression ....."
What's going wrong? Help!!
ok I have two tables, table1 and table2. Both have a field called "CKey". Basically I am cycling through each record in Table1, to find the "CKey" in table2 and do something.
I have written:
table2.MoveFirst
Do While Not table2.EOF
Message2 = "Working on record " & counter & " of " & lastrec
DoEvents
strFindKey = table2!CKey
strCriteria = "Ckey='" & strFindKey & "'"
table3.FindFirst strCriteria
If Not table3.NoMatch Then....
I get this message "The Microsoft Jet Engine does not recognise "CKey" as an expression ....."
What's going wrong? Help!!
Here is my attempt.
=IF(A1="table1",VLOOKUP(B4,Matrix.xls!table1,2,1)*B6),IF(A1="table2",VLOOKUP(B4,Matrix.xls!table2,2,1)*B6),IF(A1="table3",VLOOKUP(B4,Matrix.xls!table3,2,1)*B6),IF(A1="table4",VLOOKUP(B4,Matrix.xls!table4,2,1)*B6),""))))
I have a validation in cell A1 with a dropdown list, table1,table2,table3,table4.
I want to be able to select that and then pull data from the matrix sheet based on the ranges with the same name using a vlookup returning the 2nd column.
Is this possible using this formula? Think I may be barking up the wrong tree here.
Thank you
=IF(A1="table1",VLOOKUP(B4,Matrix.xls!table1,2,1)*B6),IF(A1="table2",VLOOKUP(B4,Matrix.xls!table2,2,1)*B6),IF(A1="table3",VLOOKUP(B4,Matrix.xls!table3,2,1)*B6),IF(A1="table4",VLOOKUP(B4,Matrix.xls!table4,2,1)*B6),""))))
I have a validation in cell A1 with a dropdown list, table1,table2,table3,table4.
I want to be able to select that and then pull data from the matrix sheet based on the ranges with the same name using a vlookup returning the 2nd column.
Is this possible using this formula? Think I may be barking up the wrong tree here.
Thank you
Hi
I have three tables of data (table1, table2, table3) that contain dollar values. Each table has two columns  col 1 is a category (different type of expenses) and col 2 has the associated values of each of those expenses.
I want to use one chart only to graph the data from table1, table2 and table3 independently. For example, if I could set up three buttons (one for each table of data) I could switch from table to table and the chart could update accordingly.
I have seen this done but I'm unsure how to approach it.
Any help would be appreciated.
Thanks
Steve
I have three tables of data (table1, table2, table3) that contain dollar values. Each table has two columns  col 1 is a category (different type of expenses) and col 2 has the associated values of each of those expenses.
I want to use one chart only to graph the data from table1, table2 and table3 independently. For example, if I could set up three buttons (one for each table of data) I could switch from table to table and the chart could update accordingly.
I have seen this done but I'm unsure how to approach it.
Any help would be appreciated.
Thanks
Steve
Hi
Here's the scenario:
I have 2 files. 1 file contains a table which looks something like this:
A B C
1 1 1
2 1
3 1 1
4 1 1 1
The other file contains a table like this:
Table1 Table3
A C
Table2
B
where "Table*" is just the title of the table. In addition, these are simplified versions of the files. The 1st file actually may have 50 columns by 500 rows.
Now, what I want to generate is something like this:
Table1
A 1,3,4
Table2
B 1,2,4
Table3
C 3,4
Is there a way for me to do this?
Thanks in advance
coyotem78
Here's the scenario:
I have 2 files. 1 file contains a table which looks something like this:
A B C
1 1 1
2 1
3 1 1
4 1 1 1
The other file contains a table like this:
Table1 Table3
A C
Table2
B
where "Table*" is just the title of the table. In addition, these are simplified versions of the files. The 1st file actually may have 50 columns by 500 rows.
Now, what I want to generate is something like this:
Table1
A 1,3,4
Table2
B 1,2,4
Table3
C 3,4
Is there a way for me to do this?
Thanks in advance
coyotem78
(see enclosed file....will definitely help explain what i want).
I have 3 tables. The third table is built from table 1 and table 2.
Table1: Has 3 cols. Study, Date and Milestone
Table2: Has lots of columns. The important thing here are the "date headers" which go horizontally across.
Table3: This has the same "date headers" going across as in table2.
The study col is going down (see file). For a given study on the date from table2.......if the same date that is in table2 is also in table1 then put the milestone into the cell. See file, my explanation is not the best i know. Picture is worth a thousand words.
any ideas on how to create the table3 ?
I have 3 tables. The third table is built from table 1 and table 2.
Table1: Has 3 cols. Study, Date and Milestone
Table2: Has lots of columns. The important thing here are the "date headers" which go horizontally across.
Table3: This has the same "date headers" going across as in table2.
The study col is going down (see file). For a given study on the date from table2.......if the same date that is in table2 is also in table1 then put the milestone into the cell. See file, my explanation is not the best i know. Picture is worth a thousand words.
any ideas on how to create the table3 ?
Hello, I'm having a time figuring this problem out.
My Setup:
Sheet1, Table1 is a multiple column table. Column1 is employee name and column 2 is employee location. There are many more columns, but those are all that is important for this problem.
Sheet2, Table2 is also a multiple column table. Column1 uses data validation to force the user to select an employee name from a dropdown. The employee name list is compiled using the data in Column1 of Table1. The rest of the columns contain data from vairous lookups, indexes, and other calculations.
Sheet2, Table3 is also a multiple column table. The rows within this table contain various types of charges, for example facilities.
My problem:
In column2 of Table3, i need to sum all of the data in Column2 of Table2 and then multiply by a factor. However, i only need to sum the data for employees who are in certain locations. For example, if employee A and B (Table2, Column1) are both in location 1 (Table1, Column2), then sum the data in Table2, Column2 and multiply by X.
What i've tried:
Multiple formulas, incuding sumif, sum(if), and index, including combinations. I can get this to work simply if i include an additional column in Sheet2, Table2 that does a vlookup of Sheet1, Table1, Column2 and get's the location for the employee name selected in Sheet2, Table2, Column1. However, i don't want to do it this way. I only want Sheet2, Table2 to contain the selected employee name in Column1 and then the other columns to provide the calculations i already have present and working. I do not want to duplicate the location information from Table1. (Besides, that would be too simple!)
Any help would be appreciated.
My Setup:
Sheet1, Table1 is a multiple column table. Column1 is employee name and column 2 is employee location. There are many more columns, but those are all that is important for this problem.
Sheet2, Table2 is also a multiple column table. Column1 uses data validation to force the user to select an employee name from a dropdown. The employee name list is compiled using the data in Column1 of Table1. The rest of the columns contain data from vairous lookups, indexes, and other calculations.
Sheet2, Table3 is also a multiple column table. The rows within this table contain various types of charges, for example facilities.
My problem:
In column2 of Table3, i need to sum all of the data in Column2 of Table2 and then multiply by a factor. However, i only need to sum the data for employees who are in certain locations. For example, if employee A and B (Table2, Column1) are both in location 1 (Table1, Column2), then sum the data in Table2, Column2 and multiply by X.
What i've tried:
Multiple formulas, incuding sumif, sum(if), and index, including combinations. I can get this to work simply if i include an additional column in Sheet2, Table2 that does a vlookup of Sheet1, Table1, Column2 and get's the location for the employee name selected in Sheet2, Table2, Column1. However, i don't want to do it this way. I only want Sheet2, Table2 to contain the selected employee name in Column1 and then the other columns to provide the calculations i already have present and working. I do not want to duplicate the location information from Table1. (Besides, that would be too simple!)
Any help would be appreciated.
A
P
Table1
area1
Table2
area1
Table3
area1
Table4
area1
Table5
area1
Table6
area1
Table7
area1
Table8
area1
Table1
area2
Table2
area2
Table3
area2
Table4
area2
Table1
area3
Table2
area3
Table3
area3
Table4
area3
Table5
area3
Table6
area3
I have a SQL select statement like this
SELECT [Country], Sum([USD]) AS USDs
FROM Table1
WHERE (
Product in (Select Product from Table2 where (ColorCode=?))
and
(Factory like ? ))
GROUP BY [Country]
HAVING Country in (Select Country from Table3 where ([Shapecode]=?));
When I enter, punch in the value of all three ? parameter, no records come out.
SO I go check step by step and found:
If I encode last ?, ie modify it to
HAVING Country in (Select Country from Table3 where ([Shapecode]="Square"));
It works. so this test is ok
and then do individually like
Select Country from Table3 where ([Shapecode]=?)
and run and key in Square, desired record comes out. so this test is ok
If I delete the first 2 paremeters and make it just
SELECT [Country], Sum([USD]) AS USDs
FROM Table1
GROUP BY [Country]
HAVING Country in (Select Country from Table3 where ([Shapecode]=?));
and run and key in "Sqaure" then it returns some record so this test is ok
Every individual tests are ok when I break it down but when I paste it together and run again as below, no record returned... pls help me plz...
Thanks !!!
SELECT [Country], Sum([USD]) AS USDs
FROM Table1
WHERE (
Product in (Select Product from Table2 where (ColorCode=?))
and
(Factory like ? ))
GROUP BY [Country]
HAVING Country in (Select Country from Table3 where ([Shapecode]=?));
SELECT [Country], Sum([USD]) AS USDs
FROM Table1
WHERE (
Product in (Select Product from Table2 where (ColorCode=?))
and
(Factory like ? ))
GROUP BY [Country]
HAVING Country in (Select Country from Table3 where ([Shapecode]=?));
When I enter, punch in the value of all three ? parameter, no records come out.
SO I go check step by step and found:
If I encode last ?, ie modify it to
HAVING Country in (Select Country from Table3 where ([Shapecode]="Square"));
It works. so this test is ok
and then do individually like
Select Country from Table3 where ([Shapecode]=?)
and run and key in Square, desired record comes out. so this test is ok
If I delete the first 2 paremeters and make it just
SELECT [Country], Sum([USD]) AS USDs
FROM Table1
GROUP BY [Country]
HAVING Country in (Select Country from Table3 where ([Shapecode]=?));
and run and key in "Sqaure" then it returns some record so this test is ok
Every individual tests are ok when I break it down but when I paste it together and run again as below, no record returned... pls help me plz...
Thanks !!!
SELECT [Country], Sum([USD]) AS USDs
FROM Table1
WHERE (
Product in (Select Product from Table2 where (ColorCode=?))
and
(Factory like ? ))
GROUP BY [Country]
HAVING Country in (Select Country from Table3 where ([Shapecode]=?));
i found this post in the forum http://www.mrexcel.com/forum/showpos...34&postcount=5, by mark W, using ado to return number combinations
Quote:
It's a cartesian product where each of the columns is treated as a 1column table using the Excel ODBC driver and the following SQL...
SELECT Table1.F1, Table2.F2, Table3.F3, Table4.F4
FROM Table1, Table2, Table3, Table4
i am trying to return the number of 6 number combinations, from a range of 18 numbers in a set, where the sum of the numbers is within a user selected range, each number can only be used once in each combination
can the sql be modified to do some or all of the above?
Quote:
It's a cartesian product where each of the columns is treated as a 1column table using the Excel ODBC driver and the following SQL...
SELECT Table1.F1, Table2.F2, Table3.F3, Table4.F4
FROM Table1, Table2, Table3, Table4
i am trying to return the number of 6 number combinations, from a range of 18 numbers in a set, where the sum of the numbers is within a user selected range, each number can only be used once in each combination
can the sql be modified to do some or all of the above?
I'm fairly new to access but I do have VBA background and some SQL. I have the following tables that I'm trying to work with:
Table1 (Training Completed)
EmployeeID,Training,revision,Date,Instructor
Table2 (Requirements)
Qualification, Requirement1, Requirement2, Requirement3
Table3 (Training Available)
Training, current_Revision
The Training from table1 and the requirements are all from Training in Table3. Is what I'm trying to do is report as whether an employee passes a qualification meaning that the current_Revision for Requirement1, Requirement2 and Requirement3 are all acounted for in Table1. What would be the easiest way to go about this?
Table1 (Training Completed)
EmployeeID,Training,revision,Date,Instructor
Table2 (Requirements)
Qualification, Requirement1, Requirement2, Requirement3
Table3 (Training Available)
Training, current_Revision
The Training from table1 and the requirements are all from Training in Table3. Is what I'm trying to do is report as whether an employee passes a qualification meaning that the current_Revision for Requirement1, Requirement2 and Requirement3 are all acounted for in Table1. What would be the easiest way to go about this?
SELECT Table1.Field4, [Table2].Field1, [Table2].Field2, [Table2].Field3
FROM Table1 INNER JOIN [Table2] ON Table1.Field4 = [Table2].Field4;
Thanks for any help. I'd like to be able to edit the display and have any
changes (or deletions) reflected in Table2.
FROM Table1 INNER JOIN [Table2] ON Table1.Field4 = [Table2].Field4;
Thanks for any help. I'd like to be able to edit the display and have any
changes (or deletions) reflected in Table2.
Hi,
At the moment I have a very sluggish records system in which I copy and paste table data all day long. I just made a bit of a bad error (forgot to copy some data) and am now in big trouble with my boss .
Anyway, i would dearly like to avoid this happening again, and would also like to automate these processes.
I think it's pretty basic stuff.....all help dearly appreciated. Apologies, I'm an idiot.
Question 1
Ok, I have a workbook with multiple worksheets.
Worksheet 1 is table1 which records inputs.
Worksheet 2 is table2 which records outputs.
Worksheet 3 is table3 which records combined inputs and outputs.
All 3 of the tables are identical in column design (so that I can amalgamate them easily). We only separate them because we need three separate sets of records.
To create table 3, at the moment I simply copy and paste the data from tables 1 and 2 to update table 3 every now and then.
How can I set things up so that table3 is autopopulated? I.e. as soon as I make an entry in table1 or table2, table3 is autoupdated?
At the moment I have a very sluggish records system in which I copy and paste table data all day long. I just made a bit of a bad error (forgot to copy some data) and am now in big trouble with my boss .
Anyway, i would dearly like to avoid this happening again, and would also like to automate these processes.
I think it's pretty basic stuff.....all help dearly appreciated. Apologies, I'm an idiot.
Question 1
Ok, I have a workbook with multiple worksheets.
Worksheet 1 is table1 which records inputs.
Worksheet 2 is table2 which records outputs.
Worksheet 3 is table3 which records combined inputs and outputs.
All 3 of the tables are identical in column design (so that I can amalgamate them easily). We only separate them because we need three separate sets of records.
To create table 3, at the moment I simply copy and paste the data from tables 1 and 2 to update table 3 every now and then.
How can I set things up so that table3 is autopopulated? I.e. as soon as I make an entry in table1 or table2, table3 is autoupdated?
i create two table ,table1 contains following data.
Table1 item no customer invoice qty DM1002 EVG 125 500 SS7002 EVG 126 600 SOMITB1 EVG 127 300 BUK1005 BUK 128 300 BUK1006 BUK 129 500
table2 contains following three headings of data
Table2 customer invoice qty
in table2 i want to get the customer name from first table so i create a simple relation between two tables.
i drag customer from table1 and leave it on table2 and i table2 design view i click customer heading and then in raw sorce i select table1 and in table1 it shows me drop down list of item no not for customer.
please tell me what's going wrong.
thanks.
Table1 item no customer invoice qty DM1002 EVG 125 500 SS7002 EVG 126 600 SOMITB1 EVG 127 300 BUK1005 BUK 128 300 BUK1006 BUK 129 500
table2 contains following three headings of data
Table2 customer invoice qty
in table2 i want to get the customer name from first table so i create a simple relation between two tables.
i drag customer from table1 and leave it on table2 and i table2 design view i click customer heading and then in raw sorce i select table1 and in table1 it shows me drop down list of item no not for customer.
please tell me what's going wrong.
thanks.
Hi,
I am in need of a macro which compares two tables, table1 and table2 and deletes in table2 any duplicates. So in other words, Table1 will not change and Table2 will have rows deleted which duplicate cells in table1.
Example:
Table1 Table2
Apple Orange
Grape Lemon
Banana Pineapple
Peach Banana
Lemon
After macro:
Table1 Table2
Apple Orange
Grape Pineapple
Banana
Peach
Lemon
I was hoping that someone would have an error proof macro.
Thank you!
Nicole
I am in need of a macro which compares two tables, table1 and table2 and deletes in table2 any duplicates. So in other words, Table1 will not change and Table2 will have rows deleted which duplicate cells in table1.
Example:
Table1 Table2
Apple Orange
Grape Lemon
Banana Pineapple
Peach Banana
Lemon
After macro:
Table1 Table2
Apple Orange
Grape Pineapple
Banana
Peach
Lemon
I was hoping that someone would have an error proof macro.
Thank you!
Nicole
Hi
I have a relatively simple problem but not sure how to solve it.
I have two spreadsheets named Table1 and Table2.
I also have a list of loan numbers in Column A of Sheet 3.
I need a formula that will lookup each loan number in Table1, but if it is not there, then lookup Table2, and return the value found in Column B of either Table1 or Table2. I hope that is clear.
Any help would be apprecaited.
Rastus
I have a relatively simple problem but not sure how to solve it.
I have two spreadsheets named Table1 and Table2.
I also have a list of loan numbers in Column A of Sheet 3.
I need a formula that will lookup each loan number in Table1, but if it is not there, then lookup Table2, and return the value found in Column B of either Table1 or Table2. I hope that is clear.
Any help would be apprecaited.
Rastus
Hello!
I'm creating a sheet for a football league, I want that when I insert the results in table1, the table2 automatically fills up.
Is it possible? Should I change something in table1 first?
table1.jpg
table2.jpg
Thanks!
I'm creating a sheet for a football league, I want that when I insert the results in table1, the table2 automatically fills up.
Is it possible? Should I change something in table1 first?
table1.jpg
table2.jpg
Thanks!
Hi guys,
Sorry I've been having trouble with this so thought I'd bring it here.
I have a table (Table1) in worksheet1 which is filled in by the user.
I then have a separate sheet (worksheet2) which is being used as a print out page (exporting to word) which includes a copy of Table1 (Table2). However, just to make it difficult, there is extra data on this page beneath Table2.
Right now Table2 has headings and a direct "=" link to the first line of Table1 (Table1Row1).
I want Table2 to insert a row below Table2Row1 and 'fill' the equation from Table2Row1 if there is data in Table1Row1. I then want this to continue until there is no data in Table1RowX.
I really hope that make sense. Thanks in Advance!
Sorry I've been having trouble with this so thought I'd bring it here.
I have a table (Table1) in worksheet1 which is filled in by the user.
I then have a separate sheet (worksheet2) which is being used as a print out page (exporting to word) which includes a copy of Table1 (Table2). However, just to make it difficult, there is extra data on this page beneath Table2.
Right now Table2 has headings and a direct "=" link to the first line of Table1 (Table1Row1).
I want Table2 to insert a row below Table2Row1 and 'fill' the equation from Table2Row1 if there is data in Table1Row1. I then want this to continue until there is no data in Table1RowX.
I really hope that make sense. Thanks in Advance!
Hi there,
I have 2 tables ("Table1", "Table2"). there are on Sheet1 and Sheet2 respectively. Each table has the same headings in the same Cells.
The headings are very simple: Date; Customer; Rep; Product; Sales.
On Sheet3 I have a Pivottable that is looking at "Table1" how can I add "Table2" to the Source so that both Tables are being used in the PivotTable without changing the location of the tables. The reason why I am not joining the tables together to create a master table ("Table1" + "Table2") is because there will be more tables soon and there is not going to be enough space on one Worksheet.
Is there a way to do this?
I would also like to stay away from using PowerPivots.
I use Excel 2010 on Vista.
Thanks
I have 2 tables ("Table1", "Table2"). there are on Sheet1 and Sheet2 respectively. Each table has the same headings in the same Cells.
The headings are very simple: Date; Customer; Rep; Product; Sales.
On Sheet3 I have a Pivottable that is looking at "Table1" how can I add "Table2" to the Source so that both Tables are being used in the PivotTable without changing the location of the tables. The reason why I am not joining the tables together to create a master table ("Table1" + "Table2") is because there will be more tables soon and there is not going to be enough space on one Worksheet.
Is there a way to do this?
I would also like to stay away from using PowerPivots.
I use Excel 2010 on Vista.
Thanks
I have two tables:
Table1: POS Transactions
Table2: Visa Payment
Table2 will have a consolidated payment line item which will clear 5 to 10 line items of Table1.
I want to clear these items e.i. 6 item of Table1 to one payment line item of Table2. I want to create Query/Report where I can pull all items of Table1 and Table2 but then select only items from Table1 for which I have one payment line item.
Table1: POS Transactions
Table2: Visa Payment
Table2 will have a consolidated payment line item which will clear 5 to 10 line items of Table1.
I want to clear these items e.i. 6 item of Table1 to one payment line item of Table2. I want to create Query/Report where I can pull all items of Table1 and Table2 but then select only items from Table1 for which I have one payment line item.
Hi All,
My workbook has three tables with identical layouts.
Worksheet 1 = table 1.
Worksheet 2 = table 2.
Worksheet 3 = table 3 = all rows in table 1 + all rows in table 2
To create table 3, at the moment I simply copy and paste the data from tables 1 and 2 to update table 3 every now and then.
How can I set things up so that table3 is autopopulated? I.e. as soon as I make an entry in table1 or table2, table3 is autoupdated?I would like to avoid having to copy and paste the whole time.
Macro?
Thanks in advance
Gus
My workbook has three tables with identical layouts.
Worksheet 1 = table 1.
Worksheet 2 = table 2.
Worksheet 3 = table 3 = all rows in table 1 + all rows in table 2
To create table 3, at the moment I simply copy and paste the data from tables 1 and 2 to update table 3 every now and then.
How can I set things up so that table3 is autopopulated? I.e. as soon as I make an entry in table1 or table2, table3 is autoupdated?I would like to avoid having to copy and paste the whole time.
Macro?
Thanks in advance
Gus