IF ‘YES’ lookup a cell value in another worksheet

Topic

New Reply

Hi All,
This may sound like a really easy (dumb) question, but I am just realising how immense excel is and how little I understand. Anyway, I have have been struggling for 2 days before discovering this forum. Please can someone help?

I am creating a simple report. What I want to achieve is to get a number copied across from a particular cell in sheet 2 onto a particular cell in sheet 1, but only when the word YES is entered into another cell in sheet 1. This situation repeats for several questions in the report where the user can either leave the answer cell blank, or answer YES. Wherever they give a YES answer, a number will appear in the cell to the right of the word YES.

Sounds easy enough in theory. So, the first example would be: To copy the number in cell B2 of Sheet 2 over to cell K36 of Sheet 1, but only IF J36 of Sheet 1 has the word YES entered in it. If J36 is left blank then K36 will show a number 1.

I had managed to do a VLOOKUP formula that made this work, but the report requires me to do further lookup operations in numerous places. So, i listed all the numbers in column B on Sheet 2 but I could only get the operation to work by also putting the word YES in each cell of column A as well. What happened then was every time I entered YES on sheet 1, the LOOKUP formula =IFJ36=”YES”,VLOOKUP(J36,Sheet2!$A1:$B40,2),”1″) simply went to the highest number in column B of sheet 2 and entered that in the destination cell. I want to ba able to use the word YES in a variety of cells on sheet 1 but have only the specific numerical content of a particular sheet 2 column B cell copied across to a specific destination cell on sheet 1.

I need all the raw number data on a separate sheet, so that the number values can be changed easily at any time in the future. This will then automatically change all the returned values, column totals, etc on sheet 1. I will also be duplicating this report hundreds of times with all sheets looking at the raw data on Sheet 2, so you can see why I have to be able to change the raw number data only once on one page rather than having to amend every one of the hundreds of report sheets.

I thank you in anticipation of someone being able to put an end to my struggles. I have no hair left to pull out!

Regards,

Reply | Quote

Replies

Welcome to Woody’s Lounge!

If K36 has to display either the value of Sheet2!B2 or 1, you can use

=IF(J36="YES",Sheet2!B2,1)

If you need to look up the cell from which to return the value, you must specify a search criterion – just looking for “YES” will not yield a unique result.

Reply | Quote

Hans,

That’s fantastic! Two days I’ve been bashing away at this. THANK YOU.
Can I be really greedy and pick your brains some more please?
To make my report more useful, could you help sort this one out?

I would like to total all the returned sheet 1 column K values, BUT if the user has answered YES earlier on in sheet1 (cell N27), this will affect the overall value of the job. So, I would like to add a formula to the column total cell (K60) so it will not only add each of the cells above it, but if there is a YES in N27 it will multiply the value in K46, K48 and K54 by a value in sheet2 cell B42. Is this possible?

Thanks again,

Reply | Quote

Hans,

Just re-reading my last post, maybe the values in K46, K48 and K54 should be individually multiplied by the number in sheet2 B42 if there is a YES in N27 and THEN the column K on sheet1 can simply be totalled. That sounds more logical I think.
Thanks,

Reply | Quote

It is indeed easier to multiply these three values individually. Let’s say that the formula in K46 is now

=IF(J46="YES",Sheet2!B12,1)

You can change this to

=IF(N27="YES",Sheet2!B42,1)*IF(J46="YES",Sheet2!B12,1)

The original result is multiplied by the number in Sheet2!B42 if N27 is “YES”, and by 1 otherwise (leaving the result unchanged). Similar for K48 and K54. The other formulas in column K should remain as they were.

Reply | Quote

Hans,

This is just excellent!

THANKYOU SO MUCH.

Kind regards,

Reply | Quote