Creating 2 new Worksheets

  • Thread starter Thread starter Nycki
  • Start date Start date
N

Nycki

This may not be possible but I thought I would ask just in case.
I am trying to input data that will copy data into two different worksheets
as one is all combinded main data and the other is a letter. I know to use =
but I wanted to know if there is a way so I did not have to create 100 plus
worksheets, everytime I had a new entry.
 
Maybe provide some more details:
- where do you enter the data?
- where do they need to end up in?
- does it need to happen always or based on certain condidtions?
 
Ok I'll try to keep this simple, I think I was complicating it more before.
I put multiple entries comprised of the below info on one worksheet, each is
unique, the consignee will be different in A2 than A6, GO# in C1 will be
different than C5 and so on. Some of this information needs to be loaded
into another worksheet that is a letter. I cannot figure out how to do this
without doing and = for each sheet to the other. The letter does not have to
be a worksheet for each entry but the master worksheet has to have all
entries.

CONSIGNEE, ADDRESS G.O. # 2009-XXX-J632-0002 Date of Arrival 01/03/09
abc AWB / BOL 123-22248888 Date of Entry into G.O.02/12/09
123 main street QTY 690 Cartons Disposition (sold,entry#)
city, state, zip Description of Goods Quilts Disposition Date
 
Are the data 'normalized' or can the data be 'normalized', meaning
it's way better if for each record there is only one row, i.e., you
would have columns:
Consignee
Address
G.O.#
Date of Arrival
Date of Entry into G.O.
Disposition (sold,entry#)
Description of Goods
Disposition Date

What i couldn't figure if the data are already like that or there
would be actually multiple rows for as if 'the same entry'.
What you're asking for can easily be done with a VBA or maybe even
with a formula but before I could help you out i need to understand if
the data are 'normalized' or can be 'normalized' without ruining
something else that you do with those.
Post back and will proceed from there.
 
each record one row is fine I can work with that

AB said:
Are the data 'normalized' or can the data be 'normalized', meaning
it's way better if for each record there is only one row, i.e., you
would have columns:
Consignee
Address
G.O.#
Date of Arrival
Date of Entry into G.O.
Disposition (sold,entry#)
Description of Goods
Disposition Date

What i couldn't figure if the data are already like that or there
would be actually multiple rows for as if 'the same entry'.
What you're asking for can easily be done with a VBA or maybe even
with a formula but before I could help you out i need to understand if
the data are 'normalized' or can be 'normalized' without ruining
something else that you do with those.
Post back and will proceed from there.
Click to expand...
 
ok, then step by step we'll get there :)

Which of those columns holds the Unique number for the row? I'd guess
it's 'G.O.#' but i wouldn't know - we need to know in which column
values would never duplicate - would be the 'ID column' for each
record. What kind of values it would topically hold?
You need to make sure that the 'ID column' is the first column in the
workbook - then you can use vlookup formula. If it's not first column,
you can use 'index' formula.

Let's try with Vlookup and see if it works:
I assumed this structure in your '1st sheet' (i dropped most of the
columns to keep it simple):
G.O.# Consignee
Address
 2009-XXX-J632-0002 ABC my
Address

then in your Letter sheet,
- in a cell 'A1' (e.g.) you can just type/copy in the 'G.O.#' that you
need for the specific letter manually
- in a cell that needs to hold 'Consignee' type this formula:
=VLOOKUP(A1,Sheet2!$A$1:$C$4,2,FALSE)
- in a cell that needs to hold 'Address' type this formula:
=VLOOKUP(A1,Sheet2!$A$1:$C$4,3,FALSE)

you'll see that whenever you change value in A1, the other values also
change and are being pulled from the '1st sheet'.
All the references can be changed, the 'manual typing of the 'g.o.#'
can be replaced with a drop-down, another 'non-duplicate' column can
be chosen but before that i need to know if i'm on the right track.
So, is this something towards what you're after?
 
Slight clarification regarding the data structure i used:
the sheet that would hold all the data i called:
Sheet2
In this sheet:
Column A - holds 'G.O.#'
Column B - Consignee
Column C - Address
 
That is kinda what i already had, but my problem is i need to keep adding to
sheet that holds the GO#'s, consignee etc, but each time I add a new row I
need that new row to fill into the other sheet (the letter)
 
Nevermind, i understand what you are saying, i was reading it wrong. I am
trying it now I will let you know how I make out. Thanks for your help
 
I guess I'm still slightly puzzled by the requirements.
I gather you already have all the vlookups on your letter sheet but
they're not dynamic enough - i gather you want the 'letter' sheet
always show the values for the last row in the '1st sheet' (let's call
it MasterSheet fror consistency here).
If that's the case, you can (in the same data structure i had before):
in your Letter sheet A1 enter:
=INDEX(MasterSheet!A:A,COUNTA(MasterSheet!A:A))

Replace the Vlookup formulas in Letter sheet with something like this:
=INDEX(MasterSheet!A:H,MATCH(A1,MasterSheet!A:A,0),2)
this will return the Consignee (because it's column B (i.e., 2) in my
data)

=INDEX(MasterSheet!A:H,MATCH(A1,MasterSheet!A:A,0),3)
this will return Address (because it's column C (i.e., 3) in my data)

Is it better this time?
Am I still getting the need wrong?
 
Thank you that worked!!!! I have just one other question. Is there a way to
not print the A1 (GO#) when I print the letter?
 
Thank you that worked!!! I just have one other question. Is there a way
when I am printing my letter that the A1 (GO#) does not print out as well.?
 
I told you we'll get there!
Glad to help.

Regarding the printing - if you don't need the cell on the letter
(printed), you can:
- change the printing area (Excel 2003 and earlier - View>Page Break
Preview - and then re-size with mouse)
- move the A1 cell out of the printing area. You would need to update
all the formulas to reference the new cell or you can just grab and
drop the A1 with a mouse and move it around - drop it somewhere
outside the printable area - all the formulas would update
automatically.
This way the the reference cell will still be on the worksheet but
just outside the printable area and won't print.
 
If you explain what you need in more detail, somebody may be able to help.
Think about your question from the reader's perspective. You know what you
want. We have absolutely no idea how anything is laid out; where it is
entered or where it needs to go.


Please don't forget to include XL version.
 
Back
Top