# Need to generate a value on one sheet, using a formula that pulls data from another sheet

1. ## Need to generate a value on one sheet, using a formula that pulls data from another sheet

Hello everyone!

First, apologies for the 'Descriptive title'... I had difficulty summarizing what I was going for...

Basically, I'm trying to generate a single value using results given on a survey, which asked participants to rate five items on a scale of 1-5. The survey also asked for an alias, which I am using as a reference between the 'Summary' page (where I want my single value to appear) and the 'Raw Data' tab, where the survey results are kept. To make things a bit more complicated, I have also decided to give each of the scaled-items (originally a value out of 5), a different weight. I think I have a solid equation figured out for this, but I'm having issues getting the correct data to populate the formula from the 'Raw Data' sheet. The equation, simplified, is as follows:

z = 1/9*(xa+xb) + 1/2*((xc/20)+xd+xe)

Where:
z = generated value using alias's as a reference to pull data into the equation below
and
xa-e = values given on the survey, stored on the 'Raw Data' tab. Feel free to get rid of the x/superscript and instead just use a-e to simplify things.

2. ## Re: Need to generate a value on one sheet, using a formula that pulls data from another sh

The formula you have has nothing wrong with it, but it is more math-based than Excel-based. Is this the issue you are having? If so, calculate X into a cell as well as a-e. Let's say X is in A1, A2-A6 are occupied by a-e. Your formula would look like this:

``Please Login or Register  to view this content.``
If this isn't what you mean, would you mind explaining the problem itself in a little more detail. Perhaps an example sheet?

Tech

3. ## Re: Need to generate a value on one sheet, using a formula that pulls data from another sh

Hey Tech,

Thank you for your response, however I think I may have explained things kind of poorly...

The values for xa-e are actually known (survey results), and are stored on a 'Raw Data' sheet in my workbook. The issue I'm having is creating an Excel formula that will correctly pull the values for xa-e fromthe 'Raw Data' sheet, and input them into my equation so that the generated number appears on the 'Summary' sheet of my workbook. I believe I'll have to use VLookup, Indirect, or a combination of functions to get this to work, and that it'll need to use the 'alias' they provided as the reference point between the pages. I have some experience in this respect, as the survey also asked for weekday availability and I have that data pulling correctly using: =IF(VLOOKUP(INDIRECT("\$C"&ROW()),'RAW DATA'!\$F\$2:\$M\$518,8,FALSE)="Monday","Yes","")

Hope that helps to clarify things, please let me know if I can provide further info.

Evan

4. ## Re: Need to generate a value on one sheet, using a formula that pulls data from another sh

No dice, eh?

There are currently 1 users browsing this thread. (0 members and 1 guests)