True/False indicator to display in different sheet

**HPD** · 11-02-2007, 05:51 AM

Sorry had to repost with proper title (which refers to problem #2)

hi, me again....

This Excel sheet is doing my head in, my boss keeps adding things

1.
I want to link a DATE from sheet '2007' in format dd/mm/yyyy to sheet 'Progress' but it only comes up with a 5 digit number. I know it has to do with the 1900 or 1904 date setting, I have tried to use Excel help but can't get it solved.

2.
In sheet '2007' there is a column with YES/NO indicators. Now I want to display in sheet 'Progress' when the indicator = NO BUT it needs to display this formula =OFFSET('2007'!$A$2,INT((ROW(B1)-1)/6),0) (from my previous queries)

3.
When performing this formula =OFFSET('2007'!$F$2:$K$86,TRUNC((ROW()-ROW($E$2))/COLUMNS('2007'!$F$2:$K$86)),MOD(ROW()-ROW($E$2),COLUMNS('2007'!$F$2:$K$86)),1,1) there a quite a few zeros displayed.
How do I prevent Excel displaying a '0' when the source cell is empty?

Thanks a whole bunch.

HPD

**VBA Noob** · 11-02-2007, 06:05 AM

1.
I want to link a DATE from sheet '2007' in format dd/mm/yyyy to sheet 'Progress' but it only comes up with a 5 digit number. I know it has to do with the 1900 or 1904 date setting, I have tried to use Excel help but can't get it solved.

Maybe format cell as date ??

2.
In sheet '2007' there is a column with YES/NO indicators. Now I want to display in sheet 'Progress' when the indicator = NO BUT it needs to display this formula =OFFSET('2007'!$A$2,INT((ROW(B1)-1)/6),0) (from my previous queries)

What do you mean display this formula ??

3.
When performing this formula =OFFSET('2007'!$F$2:$K$86,TRUNC((ROW()-ROW($E$2))/COLUMNS('2007'!$F$2:$K$86)),MOD(ROW()-ROW($E$2),COLUMNS('2007'!$F$2:$K$86)),1,1) there a quite a few zeros displayed.

How do I prevent Excel displaying a '0' when the source cell is empty?

See link

http://www.excelforum.com/showthread...highlight=hide

VBA Noob

**HPD** · 11-02-2007, 07:40 AM

1. have formatted cells as DATE in both sheets...something is wrong. We are using Excel 2000 (SP3).

2.

I think it should be something to do with IF (sheet2007 cell N2=no) then display all info in cells A2:M2 taking that formula (=OFFSET('2007'!$A$2,INT((ROW(B1)-1)/6),0) ) into consideration

Hope that makes any sense and sorry to be a pest but your voluntary input is greatly appreciated.

HPD

**Bjornar** · 11-02-2007, 06:55 PM

Is this what you want?

=IF(Sheet2007!N2="No",PUT YOUR FORMULA HERE,"")

I'm not quite sure what you mean by displaying info from multiple cells... in one cell ?? or in a range of cells ??

**HPD** · 11-05-2007, 06:06 AM

Okay solved problems 1 and 3, still baffled about the 2nd one.

Bjornar,

Tried your solution but did not yield the result I wanted.
=IF('2007'!N2="No",OFFSET('2007'!$E$2,INT((ROW(A1)-1)/6),0),"")

This doesn't do anything, it just shows the formula....

I'll try to explain in words

If in sheet '2007' cell N2 = No (or empty) then display the whole row A2:M2.
BUT cells F2:K2 have to be displayed in sheet 'Progress' in a column (this is where the OFFSET formula comes into place).

Basically if sheet '2007' cell N2 = YES nothing needs to be displayed as that RFQ doesn't need progressing

Attached 2 screenshots to make it easier (I hope) :
rfq1.jpg = Sheet '2007'
rfq2.jpg = Sheet 'Progress'

HPD

**Bjornar** · 11-06-2007, 12:03 PM

Tried your solution but did not yield the result I wanted.
=IF('2007'!N2="No",OFFSET('2007'!$E$2,INT((ROW(A1) -1)/6),0),"")

This doesn't do anything, it just shows the formula....

What formula ? The IF or the OFFSET formula ?? Where do you put this formula, and where do "it just shows the formula" ?

...then display the whole row A2:M2.)

Where do you want to display this ?

BUT cells F2:K2 have to be displayed in sheet 'Progress' in a column (this is where the OFFSET formula comes into place).

What cells do you want F2:K2 to display ?

Can you upload a .zip file of your Excel file, or at least a part of your sheets(s)? Sorry I'm a bit confused

**HPD** · 11-07-2007, 06:26 AM

Sorry can't do, company confidential information.

In my previous post I attached 2 pics, were those of any use?

I'll see what I can do.

HPD

True/False indicator to display in different sheet

LinkBack

Thread Tools

Rate This Thread

Display

True/False indicator to display in different sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions