Dynamically creating entries in worksheets

K

kyoshida

Is it possible to dynamically create entries in a worksheet based on th
entry in another worksheet? If so, can you point me in the righ
direction.

Thanks in advance
 
C

CLR

This formula, in a cell in Sheet2, will return the value in cell A1 of
Sheet1....

=Sheet1!A1

HTH
Vaya con Dios,
Chuck, CABGx3
 
K

kyoshida

Great, thanks! What if I need the values of worksheet#2 based on any
value entered in a particular column in worksheet#1? In any other
words, if I have entries in column "A" on workseet#1, in which the
entries positions aren't static, how can I account for that in
worksheet#2.

What I have is 2 worksheets, the first captures project data and the
second associates resources to it. These projects are identified by
project# and depending on how many rows the previous project occupies,
the next project# can start on any given line.

Ken
 
C

CLR

If you put the formula I gave you, ( =Sheet1!A1 ) in cell A1 of Sheet2 and
copy and paste it down column A of Sheet2, it will reproduce column A of
Sheet1 on sheet2.....all data will be in the same order it was on
sheet1..........is that what you're looking for?

Vaya con Dios,
Chuck, CABGx3
 
K

kyoshida

I tried entering: "=CHIP Tasks!A1" into A1 of the 2nd worksheet and I
get this = "=CHIP '[Tasks]CHIP Tasks'!A1" and then an Invalid name
error. Any clues?
 
C

CLR

Try this....to cover the space in the SheetName........

='CHIP Tasks'!A1

Vaya con Dios,
Chuck, CABGx3
 
K

kyoshida

I tried "='CHIP Tasks'!A1", but that only gets me the value of "A1". I
need all the values from column A to appear in worksheet #2. Is there
a way to do that?
 
K

kyoshida

Actually, I got it to work, but how can I force a blank space and not
"0" if the field in empty
 
K

kyoshida

Ok - nevermind on that one to. Sorry! But, here's a good question. I
have the rows from ws#1 column A updating column A on ws#2. Because of
the formula, the values on ws#2 show on the same lines as they do on
ws#1.

On ws#2, I need to remove the empty rows that are created. Is there a
way to do this?

Thanks,
Ken
 
C

CLR

Wrap the formula in an IF statement.........

=IF('CHIP Tasks'!A1="","",'CHIP Tasks'!A1)

Vaya con Dios,
Chuck, CABGx3
 
K

kyoshida

Thanks again for the help. One last question... Can you copy the cell
formatting over as well? So, if the cell is green in ws#1, can you put
that in the formula as well for ws#2?

Thanks
 
C

CLR

No, unfortunately that feature is not supported.......a lot of folks would
like that.

Vaya con Dios,
Chuck, CABGx3
 
C

ccraig61

To capture data from one worksheet to another in the same workbook you
might want to try a code range formula. This works when you can
identify several values that need to be captured, either in total or
individually from one page to another. However the more individual
numbers you have the more codes you need.

Your worksheet identifies individual costs associated with a project.
Project A
Labor hourly $35,000
Labor salary $25,000
Concrete $11,000
Wire $4,000

Next, in the next column,first label it "Code", then give each value a
code. Labor hourly code 1, Labor Salary code 2, Concrete code 3, wire
code 3. These codes can be associated with each cost center. Next
define the range for both Total and Code columns. You do this by going
to Insert->Name->Define. Enter the name you wish to define and click
the little icon in the refer to box. THis will identify the range to
search for your data.

On your next worksheet page you may want a total for each cost center.
In the first column you identify the code associated. Next column your
description. In you total column your formula would be
=ROUND(SUMIF(Code,$A8,1st worksheet total column),0), where $A8 is the
code on your current worksheet in the first column, "1st worksheet" is
whatever your worksheet data name is. You do not have to round. This is
a great way to automate.

C Craig
 
Top