Hi Michelle
In your Workbook Summary.xls, create a sheet called Summary with
headings in A1:E1 of
Product, Batch, Description, Est Price, Brand
Use the code below, after modifying the line Set Spath = to reflect the
location of the folder containing Prices.xls.
This code needs to be copied into a standard module in Summary.xls,
(details how to copy shown below the code)
Use ToolsMacroRun to invoke the macro whenever needed.
It deletes all entries on Summary sheet and rebuilds all from scratch
each time it is run.
Sub ColateData()
Dim Wbook As Workbook, Spath As String
Dim SourceSheet As Worksheet, DestSheet As Worksheet
Dim addrow As Long, lastrow As Long
Dim source As Range, dest As Range
Application.ScreenUpdating = False
ThisWorkbook.Sheets("Summary").Select
Set DestSheet = ThisWorkbook.Sheets("Summary")
With DestSheet
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
If lastrow = 1 Then lastrow = 2
Rows("2:" & lastrow).Clear
End With
Set Spath = "C:\Excel\Ngroups\" ' <=== Change to suit
Set Wbook = Workbooks.Open(Spath & "Prices.xls")
For Each SourceSheet In Wbook.Worksheets
With DestSheet
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
End With
addrow = SourceSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set source = SourceSheet.Range("A2:D" & addrow)
Set dest = DestSheet.Range("A" & lastrow + 1)
source.Copy dest
DestSheet.Range("E" & lastrow + 1 & ":E" & lastrow + addrow - 1)
= SourceSheet.Name
Next
Wbook.Close Savechanges:=False
ThisWorkbook.Save
Application.ScreenUpdating = True
End Sub
You can copy the code and paste it into your Visual Basic Editor
(VBE) in a Standard Module located in your file.
To do this,
Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert Module
Paste code in Module
--
Regards
Roger Govier
"MichelleS" wrote in message
...
Roger,
Yes, I think I could safely copy the 6 sheets of data onto 1 sheet in
the
Estimate file and call that Sheet 'Summary'.
Also, to correct a constant question here, in my original data there
are 'NO
Repeats or Duplicate codes like the example'. Also, the two files do
not
reside in the same folder.
Can you share with me a specific VBA solution for the example
submitted?
Thank you,
MichelleS.
"Roger Govier" wrote:
Hi Michelle
Would it be acceptable to use a VBA solution, to copy the data from
each
sheet of Prices.xls into one sheet in Estimates.xls called Summary?
During the process, an extra column would be created which would
place
the name of the sheet tab from each sheet of prices, alongside the
each
row of data derived from that sheet. Therefore your final table in
Estimates would have Brand as well all the other data, thereby
getting
round any problems with duplicate Product or Batch codes between
sheets.
If so the code is quite simple and fast, but I need to know if the 2
files reside in the same folder, if not where does each file reside.
--
Regards
Roger Govier
"MichelleS" wrote in message
...
Thanks Pete for replying to my message, but can you do me a favor
and
write
out the "=IF(ISNA" formula using the actual cells in my example
"IF(ISNA(VLOOKUP(A1??)", only because I'm not that experienced with
this
formula? Thank you again.
MichelleS
"Pete_UK" wrote:
Are you saying that you want to match on Product and Batch, and
can
you
guarantee that there are no duplicates of these in your pricing
worksheets? In your examples you have 400 01 in two sheets, so
would
you like to specify the Brand in another column of your Estimates
file,
or can we say that such duplicates will not occur in the actual
Prices
file?
If you want to perform a lookup over a number of lookup tables,
the
standard approach is something like:
=IF(ISNA(vlookup1),IF(ISNA(vlookup2),IF(ISNA(vlook up3),"missing",vlookup3),vlookup2),vlookup1)
This is for 3 tables, but hopefully with the symmetry of the
formula
you can see how it could be extended to up to 6. I think you may
be
pushing it with the limit on the number of characters in a
formula,
so
you may like to have a column which looks at 3 sheets and another
column which looks at the other three sheets (these could be
hidden)
and then a final formula which chooses the appropriate value from
the
two hidden columns (at least one of which will contain "missing"
if
the
entries are unique).
However, you want to match on two columns, so VLOOKUP is not
appropriate if you can't amend your pricing tables to amalgamate
the
lookups - you will have to use an INDEX/MATCH/MATCH combination.
Hope this helps.
Pete
MichelleS wrote:
Romelsb,
Thanks for replying but.....................
In all honesty, the 'Descriptions', I have provided you are
irrelevant, only
cell fillers in place of the true info. The sheet
names.....Brand
1, Brand
2, etc, etc. are also irrelevant, only sheet name fillers in
place
of the
true info., So do not focus on those.......
"My main interest is in finding a way to automatically poplulate
cells from
one workbook with 6 different sheets to another workbook with 1
sheet."
I have tried the VLOOKUP function below with cntrl+shft+enter,
it
works for
only 1 criteria search not both (product# and batch#). Maybe
there
is another
function INDEX MATCH or something that I should be
using.........
=IF(OR('[Prices.xls]sheet1'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet1'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet2'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet2'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet3'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet3'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet4'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet4'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet5'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet5'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet6'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet6'!$A$2:$C$3,3,FALSE),""))))))
Again Anyone with experience in this area please assist.
Thank you much !!
"romelsb" wrote:
excuse me Dave...Michelle,
From your sheet title:
Brand 1 : item 6 : Product 400, Batch 01, Description Small,
Est.Price $96--
Brand 2 : item 4 : Product 400, Batch 01, Description Black,
Est.Price $95--
Your encoding sheet A & B on Estimates.xls may not be enough
if
you dont add
another column LIKE for Brand....
The BRANDS sheet Description is quite strange- some are by
sizes,
others are
by colors....please check first the 6 Brand Sheet Description
and
Product
coding....more power...
"Bright minds are blessed to those who share them.."-rsb.
"MichelleS" wrote:
Thank you Dave, but I tried the VLOOKUP and it does not work
for all of my 6
sheets nor for the 2 criteria search (Product and Batch
Code).
Also, for accounting purposes I cannot move any data,
because
other
departments reference that same Price.xls workbook.
Michelle
"Dave F" wrote:
You would need to use the VLOOKUP function to do what
you're
trying to do.
However, one suggestion would be to have one workbook
rather
than two for
this task. The reason for this is that the syntax for
VLOOKUP formulas is
rather complex; it is made more so by having a link to an
external workbook.
Since you say you're a beginner you may wish to save
yourself
the headache of
auditing VLOOKUPS which reference external workbooks.
In any event, this site explains how VLOOKUP works:
http://www.techonthenet.com/excel/formulas/vlookup.php
Best thing for you to do would go to the site, try the
VLOOKUP, and, if you
run into problems, reply to this post with the VLOOKUP
syntax
you are using
and someone can help you.
Dave
--
Brevity is the soul of wit.
"MichelleS" wrote:
I have two EXCEL 2000 workbooks( Estimates.xls and
Prices.xls).
Workbook 'Estimates.xls' has 1 worksheet with empty
cells
(C&D) that need
populating if columns A&B are filled in, see below.
(snapshot of ESTIMATES.xls)
A B C
D
1 Product Batch Description Est Price
2 400 01 ____ $____
3 400 03 ____ $____
4 600 01 ____ $____
5 200 02 ____ $____
6 900 01 ____ $____
7 900 02 ____ $____
Workbook 'Prices.xls' has 6 worksheets that are lists of
different brands
with prices used for referencing the 'Estimate.xls'
workbook. Its sheets are
titled 'Brand 1, Brand 2 thru Brand 6', see below.
(snapshot of PRICES.xls, specifically Brand 1 worksheet)
A B C
D
1 Product Batch Description Est Price
2 200 01 Red
$45
3 300 03 Blue Small $75
4 600 01 Orange Med $47
5 900 02 Large
$87
6 400 01 Small
$96
7 900 01 Medium $15
(PRICES.xls, Brand 2 worksheet)
A B C
D
1 Product Batch Description Est Price
2 100 01 Green $22
3 800 03 Brown $14
4 400 01 Black $95
5 200 02 XL $67
6 900 03 XXL $98
7 700 02 XXXL $10
(this is a snapshot of PRICES.xls, Brand 3 worksheet)
A B C
D
1 Product Batch Description Price
2 500 01 Cyan $44
3 400 03 Magenta $29
Worksheets 4 thru 6 are very similar.
Any help with this is appreciated.
(ps: I'm a beginner so please use details in response)
Michelle