#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Eva Eva is offline
external usenet poster
 
Posts: 197
Default Offset function

I have the data in sheet 1 as follows
col A - blank or "new"
col B - name
col c - number

I need to create in sheet2 report that takes only "new" data from column A
col B - name
col c - number

I thought about the offset function - any ideas?

--
Greatly appreciated
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Offset function

In sheet2 A1 try the below formula...Copy/drag the formula to cells to the
right ColB/C and then copy the formula down as required. Please note that
this is an array formula. An array formula can perform multiple calculations
and then return either a single result or multiple results.You create array
formulas in the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula}"

=IF(ROW(A1)<=COUNTIF(Sheet1!$A:$A,"?*"),INDEX(Shee t1!A:A,SMALL(IF(Sheet1!$A$1:$A$100<"",ROW($A$1:$A $100)),ROW(A1))),"")

If this post helps click Yes
---------------
Jacob Skaria


"Eva" wrote:

I have the data in sheet 1 as follows
col A - blank or "new"
col B - name
col c - number

I need to create in sheet2 report that takes only "new" data from column A
col B - name
col c - number

I thought about the offset function - any ideas?

--
Greatly appreciated

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Eva Eva is offline
external usenet poster
 
Posts: 197
Default Offset function

it is so brilliant!
Thank you very much
--
Greatly appreciated


"Jacob Skaria" wrote:

In sheet2 A1 try the below formula...Copy/drag the formula to cells to the
right ColB/C and then copy the formula down as required. Please note that
this is an array formula. An array formula can perform multiple calculations
and then return either a single result or multiple results.You create array
formulas in the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula}"

=IF(ROW(A1)<=COUNTIF(Sheet1!$A:$A,"?*"),INDEX(Shee t1!A:A,SMALL(IF(Sheet1!$A$1:$A$100<"",ROW($A$1:$A $100)),ROW(A1))),"")

If this post helps click Yes
---------------
Jacob Skaria


"Eva" wrote:

I have the data in sheet 1 as follows
col A - blank or "new"
col B - name
col c - number

I need to create in sheet2 report that takes only "new" data from column A
col B - name
col c - number

I thought about the offset function - any ideas?

--
Greatly appreciated

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 506
Default Offset function

Try this...

Using Indirect
=INDIRECT("Sheet1!A1")
=INDIRECT("Sheet1!B1")
=INDIRECT("Sheet1!C1")

Using Offset
=OFFSET(Sheet1!A1,0,0)
=OFFSET(Sheet1!A1,0,1)
=OFFSET(Sheet1!A1,0,2)

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Eva" wrote:

I have the data in sheet 1 as follows
col A - blank or "new"
col B - name
col c - number

I need to create in sheet2 report that takes only "new" data from column A
col B - name
col c - number

I thought about the offset function - any ideas?

--
Greatly appreciated

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Offset function

Try this...

Assume the data on Sheet1 is in the range A2:C20

Enter this array formula** on Sheet2 in cell A2:

=IF(ROWS(A$2:A2)COUNTIF(Sheet1!$A:$A,"new"),"",IN DEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$2:$A$20="new",RO W(Sheet1!A$2:A$20)),ROWS(A$2:A2))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy across to B2 then down until you get blanks meaning all relative data
has been extracted.

--
Biff
Microsoft Excel MVP


"Eva" wrote in message
...
I have the data in sheet 1 as follows
col A - blank or "new"
col B - name
col c - number

I need to create in sheet2 report that takes only "new" data from column A
col B - name
col c - number

I thought about the offset function - any ideas?

--
Greatly appreciated





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Eva Eva is offline
external usenet poster
 
Posts: 197
Default Offset function

One more question. Everything work very well, but I was trying to get rid of
the headings - so I would like to start my formula in sheet2 from A2 Sheet1.
I was trying to change the formula myself but it didn't work. Can you have a
look at this once again? Thank you very much
--
Greatly appreciated


"Eva" wrote:

I have the data in sheet 1 as follows
col A - blank or "new"
col B - name
col c - number

I need to create in sheet2 report that takes only "new" data from column A
col B - name
col c - number

I thought about the offset function - any ideas?

--
Greatly appreciated

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Eva Eva is offline
external usenet poster
 
Posts: 197
Default Offset function

Hi Ms-Exl-Learner - this doesnt' work, but the formula made by Jacob works
perfectly, so thank you for your time
--
Greatly appreciated


"Ms-Exl-Learner" wrote:

Try this...

Using Indirect
=INDIRECT("Sheet1!A1")
=INDIRECT("Sheet1!B1")
=INDIRECT("Sheet1!C1")

Using Offset
=OFFSET(Sheet1!A1,0,0)
=OFFSET(Sheet1!A1,0,1)
=OFFSET(Sheet1!A1,0,2)

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"Eva" wrote:

I have the data in sheet 1 as follows
col A - blank or "new"
col B - name
col c - number

I need to create in sheet2 report that takes only "new" data from column A
col B - name
col c - number

I thought about the offset function - any ideas?

--
Greatly appreciated

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default Offset function

Hi,

Try this

1. Say your data is in range B5:D10 (Sheet1)
2. In B4:D4, type headings - Status, Name and Number
3. In B12, type Status
4. In B13, type New
5. In Sheet2, type Name and number in B4:C4
6. Click on cell B6 of sheet2
7. Go to Data Filter Advanced Filter
8. In Action, select "Copy to another location"
9. In the list box, select B4:D10 of sheet1;
10. In criteria, select b12:B13 of sheet1
11. In the copy to box, select B4:C4 of sheet2
12. Click on Finish

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eva" wrote in message
...
I have the data in sheet 1 as follows
col A - blank or "new"
col B - name
col c - number

I need to create in sheet2 report that takes only "new" data from column A
col B - name
col c - number

I thought about the offset function - any ideas?

--
Greatly appreciated


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Offset function

To get only the entries with "new" try the below.. in sheet2 A2 and copy
across/down as required

=IF(ROW(A1)<=COUNTIF(Sheet1!$A:$A,"new"),INDEX(She et1!A:A,SMALL(IF(Sheet1!$A$1:$A$100="new",ROW($A$1 :$A$100)),ROW(A1))),"")

If this post helps click Yes
---------------
Jacob Skaria


"Eva" wrote:

One more question. Everything work very well, but I was trying to get rid of
the headings - so I would like to start my formula in sheet2 from A2 Sheet1.
I was trying to change the formula myself but it didn't work. Can you have a
look at this once again? Thank you very much
--
Greatly appreciated


"Eva" wrote:

I have the data in sheet 1 as follows
col A - blank or "new"
col B - name
col c - number

I need to create in sheet2 report that takes only "new" data from column A
col B - name
col c - number

I thought about the offset function - any ideas?

--
Greatly appreciated

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Eva Eva is offline
external usenet poster
 
Posts: 197
Default Offset function

Thank you Ashish, but I prefer the formula. I know about this option :)
--
Greatly appreciated


"Ashish Mathur" wrote:

Hi,

Try this

1. Say your data is in range B5:D10 (Sheet1)
2. In B4:D4, type headings - Status, Name and Number
3. In B12, type Status
4. In B13, type New
5. In Sheet2, type Name and number in B4:C4
6. Click on cell B6 of sheet2
7. Go to Data Filter Advanced Filter
8. In Action, select "Copy to another location"
9. In the list box, select B4:D10 of sheet1;
10. In criteria, select b12:B13 of sheet1
11. In the copy to box, select B4:C4 of sheet2
12. Click on Finish

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eva" wrote in message
...
I have the data in sheet 1 as follows
col A - blank or "new"
col B - name
col c - number

I need to create in sheet2 report that takes only "new" data from column A
col B - name
col c - number

I thought about the offset function - any ideas?

--
Greatly appreciated




  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Eva Eva is offline
external usenet poster
 
Posts: 197
Default Offset function

Hi Jacob
I tried already to change the formula with "new", but it doesn't work, but
it realy is not a big problem, so I will leave it as it is.
Thank you for your help
--
Greatly appreciated


"Jacob Skaria" wrote:

To get only the entries with "new" try the below.. in sheet2 A2 and copy
across/down as required

=IF(ROW(A1)<=COUNTIF(Sheet1!$A:$A,"new"),INDEX(She et1!A:A,SMALL(IF(Sheet1!$A$1:$A$100="new",ROW($A$1 :$A$100)),ROW(A1))),"")

If this post helps click Yes
---------------
Jacob Skaria


"Eva" wrote:

One more question. Everything work very well, but I was trying to get rid of
the headings - so I would like to start my formula in sheet2 from A2 Sheet1.
I was trying to change the formula myself but it didn't work. Can you have a
look at this once again? Thank you very much
--
Greatly appreciated


"Eva" wrote:

I have the data in sheet 1 as follows
col A - blank or "new"
col B - name
col c - number

I need to create in sheet2 report that takes only "new" data from column A
col B - name
col c - number

I thought about the offset function - any ideas?

--
Greatly appreciated

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Eva Eva is offline
external usenet poster
 
Posts: 197
Default Offset function

Thanks, it is great!
--
Greatly appreciated
Eva


"T. Valko" wrote:

Try this...

Assume the data on Sheet1 is in the range A2:C20

Enter this array formula** on Sheet2 in cell A2:

=IF(ROWS(A$2:A2)COUNTIF(Sheet1!$A:$A,"new"),"",IN DEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$2:$A$20="new",RO W(Sheet1!A$2:A$20)),ROWS(A$2:A2))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy across to B2 then down until you get blanks meaning all relative data
has been extracted.

--
Biff
Microsoft Excel MVP


"Eva" wrote in message
...
I have the data in sheet 1 as follows
col A - blank or "new"
col B - name
col c - number

I need to create in sheet2 report that takes only "new" data from column A
col B - name
col c - number

I thought about the offset function - any ideas?

--
Greatly appreciated



.

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Offset function

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Eva" wrote in message
...
Thanks, it is great!
--
Greatly appreciated
Eva


"T. Valko" wrote:

Try this...

Assume the data on Sheet1 is in the range A2:C20

Enter this array formula** on Sheet2 in cell A2:

=IF(ROWS(A$2:A2)COUNTIF(Sheet1!$A:$A,"new"),"",IN DEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$2:$A$20="new",RO W(Sheet1!A$2:A$20)),ROWS(A$2:A2))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy across to B2 then down until you get blanks meaning all relative
data
has been extracted.

--
Biff
Microsoft Excel MVP


"Eva" wrote in message
...
I have the data in sheet 1 as follows
col A - blank or "new"
col B - name
col c - number

I need to create in sheet2 report that takes only "new" data from
column A
col B - name
col c - number

I thought about the offset function - any ideas?

--
Greatly appreciated



.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
large function result as reference for offset function Z Excel Discussion (Misc queries) 1 May 5th 09 12:55 AM
Min function with an offset Jase Excel Discussion (Misc queries) 1 November 5th 08 04:22 PM
XL2002 - OFFSET function and LARGE function Trevor Williams Excel Worksheet Functions 3 March 3rd 08 02:40 PM
about Offset function Elysia Links and Linking in Excel 2 September 20th 07 01:51 AM
Offset function with nested match function not finding host ss. MKunert Excel Worksheet Functions 1 March 21st 06 11:46 PM


All times are GMT +1. The time now is 10:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"