I've been using excel for 6 years and haven't come across this formula
question until now.

Ok, I have 2 worksheets, one being the pretty page that has the total
numbers for each location and each product type over YTD. The 2nd tab
is my data tab, which is data imported from our mainframe system. The
data returned lists a branch code, the product type and the totals for
that type, but leaves out any null values (so 3 columns). Branch 0
might have 5 products, Branch 2 might have 2, etc.

My problem is that when I go to refresh the YTD data at the beginning
of a new month, the position of the data changes, so I can't do a
simple cell = !dataA1 formula, b/c the number will probably change to
the wrong product type if a once null value becomes ummm... not null?

What i would like to do is some sort of if function, but I might be co
mingling my excel and SQL formulas a bit. I want to say =IF(!dataA1=0
AND !dataB1=SD-FREE THEN return data in !dataC1). I don't see how that
would work though, b/c I'd have to have multiple formulas in a cell,
and I know that isn't easy, if not impossible.

help?


--
darkwood
------------------------------------------------------------------------
darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948
View this thread: http://www.excelforum.com/showthread...hreadid=496451

=IF(AND('data'!A1=0,'data'!B1="SD-FREE"),'data'!C1,"")

the "" means that if the conditions are not TRUE it will return a blank


--

Regards,

Peo Sjoblom

"darkwood" wrote in
message ...

I've been using excel for 6 years and haven't come across this formula
question until now.

Ok, I have 2 worksheets, one being the pretty page that has the total
numbers for each location and each product type over YTD. The 2nd tab
is my data tab, which is data imported from our mainframe system. The
data returned lists a branch code, the product type and the totals for
that type, but leaves out any null values (so 3 columns). Branch 0
might have 5 products, Branch 2 might have 2, etc.

My problem is that when I go to refresh the YTD data at the beginning
of a new month, the position of the data changes, so I can't do a
simple cell = !dataA1 formula, b/c the number will probably change to
the wrong product type if a once null value becomes ummm... not null?

What i would like to do is some sort of if function, but I might be co
mingling my excel and SQL formulas a bit. I want to say =IF(!dataA1=0
AND !dataB1=SD-FREE THEN return data in !dataC1). I don't see how that
would work though, b/c I'd have to have multiple formulas in a cell,
and I know that isn't easy, if not impossible.

help?


--
darkwood
------------------------------------------------------------------------
darkwood's Profile:
http://www.excelforum.com/member.php...o&userid=29948
View this thread: http://www.excelforum.com/showthread...hreadid=496451

Actually,

Now that I look at that formula, it doesn't appear to work either. I
have the "pretty" form looking like this:


Prod Type HQ Branch 3 Branch 5 etc....

SD 345 250 200
SD-FREE 1000 700 555
SD-REW 750 500 322
SD-STU 334 225 150

Problem here is that HQ= branches 0, 2, 7, and 2 other alpha branch
codes.

The data sheet looks like this:

Br cd type Aggregate
0 SD-FREE 250
0 SD 100
0 SD-REW 550
0 SD-STU 100
3 SD-FREE 700
3 SD 250
3 SD-REW 500
ETC...

Would it be possible to say, if somewhere in column A = 0 AND somewhere
in column B = SD-FREE, return the cell just to the right of where those
2 values are true?


--
darkwood
------------------------------------------------------------------------
darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948
View this thread: http://www.excelforum.com/showthread...hreadid=496451

bump, I am getting desparate!

TIA :)


--
darkwood
------------------------------------------------------------------------
darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948
View this thread: http://www.excelforum.com/showthread...hreadid=496451

If you copy it down adapted to the real cell references it will return that
particular value, you could also use

=AND('data'!A1=0,'data'!B1="SD-FREE")

copy down will return TRUE or FALSE then you could apply autofilter and
filter on TRUE

another way would be to use

=INDEX(C2:C200,MATCH(1,(A2:A200=0)*(B2:B200="SD-FREE"),0))

entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

(No private emails please)


"darkwood" wrote in
message ...

bump, I am getting desparate!

TIA :)


--
darkwood
------------------------------------------------------------------------
darkwood's Profile:
http://www.excelforum.com/member.php...o&userid=29948
View this thread: http://www.excelforum.com/showthread...hreadid=496451

All I get with the index formula is #N/A and followed the formula
exactly.

The AND function doesn't really do me any good if I just autofilter
afterwards. I can autofilter right now, but it doesn't accomplish what
I need. I need to put this formula on the front page, so that it's
pulling the aggregate totals from column C on the data page, over to
the appropriate cell on the totals page. The formula as I entered is
as follows:

=INDEX(Data!C2:C122,MATCH(1,(Data!A2:A122=3)*(Data !B2:B122="SD-FREE"),0))

and used Ctrl+Shift+Enter I used A2:A122=3 b/c I wanted to look up
branch 3 and SD-FREE, corresponding total in Column C. The data exists
currently in cells A68 thru D68 that meets the criteria I am looking
for, but the formula didn't pull over the number from Column C.


--
darkwood
------------------------------------------------------------------------
darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948
View this thread: http://www.excelforum.com/showthread...hreadid=496451