Excel Help needed

Life Technology
1

Any Excel whizzkidz out there?

I am trying to do calculations that I don’t know how to do or how to ask for help.

The problem is this

=IF(‘D22’!$I$4<>0,‘D22’!$I$4,"")

This formula will reprint the contents of Cell D22, if Cell D22 in my spreadsheet has something, otherwise it returns a blank cell.

What I would like to do is replace the Cell number D22 with the following formula, but I want Excel to do that for me.

So what I need is something like this:

=IF(‘Formula’!$I$4<>0,‘Formula’!$I$4,"")

Where Formula is a simple calculation for Excel to reference a particular Cell in a series, say D22, D23, D24

I don’t want to type:
=IF(‘D22’!$I$4<>0,‘D22’!$I$4,"")
=IF(‘D23’!$I$4<>0,‘D23’!$I$4,"")
=IF(‘D24’!$I$4<>0,‘D24’!$I$4,"")
etc.
because I have hundreds to enter. I need a formula that I can copy and paste. To do this, though I need to know how to work D22, etc., etc., into a formula that Excel can calculate, then execute and return the contents of that cell.

I don’t know what commands I need to call to write the contents of Formula…

Anyone see what I need?
Thanks
Kenneth

2 
=IF('D22'!$I$4<>0,'D22'!$I$4,"")

I think there is some confusion. The if() function consists of three fields: the evaluation, the action if true, and the action if false.

“‘D22’!$I$4” literally represents cell I4 of sheet “D22”, where cell I4 is locked (in the sense that it would remain as “I4” even if you copy-pasted the function elsewhere). So if D22 is supposed to be your cell in reference, then you have got the function wrong.

From your question, I am guessing you want something like the following.

=if(D22<>0,D22,"")

Which literally means: “If cell D22 [of current sheet] is not 0, print D22 in current cell; otherwise, don’t print anything in current cell.” Since D22 is not locked with $, if you copy-pasted this formula, it would change to D23, D24, D25 … etc. accordingly.

Hope this helps.

3

I am a bit rusty on Excel, but I used to use VLOOKUP to return the result of a calculation on a different sheet, and then use that in the IF argument to return either the result, or nothing if it was blank or an error. But I’m not altogether sure what you’re trying to do.

4

Now is the time to get a google account and try out on their new spreadsheet function, sharing it with anyone you want …

5

OK, I got it wrong.

I forgot that D22 is a text name. It could be anything. What I’d like to do is have a table of sheet names, and call each one automatically by referencing the table.

Thus…

Table
Sheet1
Sheet2
Sheet3

Formula would include the sheet name where I said ‘Dxx’ before! Does that make sense. Vlookup should do that? I’ll try.

Thanks

6

Ah I see.

I am pretty sure vlookup() could be just as useful as if(), but you would still need to take advantage of indirect(). Consider the following.

=indirect(D22&"!A1")

Where D22 is the cell containing the name of the sheet, and A1 is the cell that you aiming for on the remote sheet. If you copy-pasted this code downwards, D22 should change to D23, D24, D25, etc.

Hope this helps.