copy data from a worksheet

[confused teacher]

How do I get data into a worksheet from two seperate sources within the same
workbook. I have two worksheets only one of which will contain data in a
given setting but there is no way of telling which one at the time of setting
up the file. I need a formula that will fill in the sheet in question based
on the data in any one of the two sheets.

The formula that I use to get it form the one sheet is :
='Gradebook - 15%, 65% & 20%'!D7*D6/100

The other sheet has the same name except the pertages are different, the
cell refs are the same. Essentially i need the upload file that I have
created to gather data from either one of these, but i wont know which one
has the data at the time of creating the workbook.

Any assistance would be great

Brian

[Dav]

You really have to provide more information. the data can be matched from the other sheets and filled in on one sheet, but it is not entirely clear what you want. You usually get better answers if you give an example. It does not have to be all your sheets, it can be scaled down.

If you are looking at the same cell in both sheets and it is blank in the other sheet, why not just add both together, as adding blank will not change your formula, or if it produces an error if not completed

If(iserror(sheet1!ref),sheet2 formula,sheet1 formula)

Regards

Dav

[confused teacher]

Thanks Dav

I will try and explain here.

I have a series of worksheets within the workbook where staff can enter a
series of grades. Each worksheet relates to a specific course, but the course
results need to be uploaded to a central sheet at the end of term. What I
need to do is fill in cells D7 and E7 based on the value that cell D7 and E7
contain in a specific worksheet, but I don't know which work sheet the
teacher will use.

As an example if the teacher uses the worksheet titled Gradebook - 15%, 65%
& 20% I need the cells D7 and E7 to be copied from that specific worksheet
such as the one identified above. The formula that I currently have in the
cell D7 is "='Gradebook - 15%, 65% & 20%'!D7*D6/100" so that I can get the
grade out of a score less than 100. My problem emerges when a teacher uses
one of the other worksheets, for instance " Gradebook 21153". The problem is
I can get the results from the sheet that I have set it up to take data from
but it I dont know how to get the sheet to grab data from another sheet if
the one that I have set it up for doesnt have data entered in it.

eg. myCQU file upload worksheet needs to be able to collect data stored in
one of however many possible sheets the teacher has access to, at this stage
just 2 sheets. There would only be data in one sheet at a time in the
workbook so I just need to figure out a way to get it to recognise data in
whichever sheet has the data.

HTH clarify this.

Brian


"Dav" wrote:

>
> You really have to provide more information. the data can be matched
> from the other sheets and filled in on one sheet, but it is not
> entirely clear what you want. You usually get better answers if you
> give an example. It does not have to be all your sheets, it can be
> scaled down.
>
> If you are looking at the same cell in both sheets and it is blank in
> the other sheet, why not just add both together, as adding blank will
> not change your formula, or if it produces an error if not completed
>
> If(iserror(sheet1!ref),sheet2 formula,sheet1 formula)
>
> Regards
>
> Dav
>
>
> --
> Dav
> ------------------------------------------------------------------------
> Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
> View this thread: http://www.excelforum.com/showthread...hreadid=572460
>
>

[Dav]

=IF(AND(MAX(Sheet1:Sheet3!D7)=SUM(Sheet1:Sheet3!D7),MAX(Sheet1:Sheet3!D6)=SUM(Sheet1:Sheet3!D6)),SUM(Sheet1:Sheet3!D6)*SUM(Sheet1:Sheet3!D7)/100,"You have duplicate Values")

if sheet1 is the name of the first sheet in the workbook that could contain the values and sheet3 is the last sheet and all the sheets inbetween also could contain the value the following should work.

I have only put the if statement in to guard against entry on multiple sheets as this could have a detrimental effect on your calculations

Regards

Dav