Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Copy values from a cell based on values of another cell

Hello,

I have an excel file with multiple sheets, here are the colum headings from
sheet 1,

B C D E F

Date Amt Desc Category Total

What I want to do on sheet 2 is check for value "Y" in the Desc column on
sheet 1, if the value ="Y" then report the value in the Date column on sheet
1. I obtained this by using a simple =IF(Sheet1!D:D="Y", Sheet1!B:B)
function, but I only want to see the values that are = to "Y", when I
populate the if function, I get values for each row. In other words I only
want it to report the date column if the Desc column = Y.

Does this make sense?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Location: Bakersfield, CA
Posts: 45
Send a message via Skype™ to JBeaucaire[_85_]
Default Copy values from a cell based on values of another cell

Try this:

=SUMIF(D:D,"Y",B:B)

--
"Actually, I AM a rocket scientist." -- JB


"Spence10169" wrote:

Hello,

I have an excel file with multiple sheets, here are the colum headings from
sheet 1,

B C D E F

Date Amt Desc Category Total

What I want to do on sheet 2 is check for value "Y" in the Desc column on
sheet 1, if the value ="Y" then report the value in the Date column on sheet
1. I obtained this by using a simple =IF(Sheet1!D:D="Y", Sheet1!B:B)
function, but I only want to see the values that are = to "Y", when I
populate the if function, I get values for each row. In other words I only
want it to report the date column if the Desc column = Y.

Does this make sense?

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Location: Bakersfield, CA
Posts: 45
Send a message via Skype™ to JBeaucaire[_85_]
Default Copy values from a cell based on values of another cell

Or rather:

=SUMIF(Sheet1!D:D,"Y",Sheet1!B:B)
--
"Actually, I AM a rocket scientist." -- JB


"Spence10169" wrote:

Hello,

I have an excel file with multiple sheets, here are the colum headings from
sheet 1,

B C D E F

Date Amt Desc Category Total

What I want to do on sheet 2 is check for value "Y" in the Desc column on
sheet 1, if the value ="Y" then report the value in the Date column on sheet
1. I obtained this by using a simple =IF(Sheet1!D:D="Y", Sheet1!B:B)
function, but I only want to see the values that are = to "Y", when I
populate the if function, I get values for each row. In other words I only
want it to report the date column if the Desc column = Y.

Does this make sense?

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Copy values from a cell based on values of another cell

I put in the formula, but it isn't what I am looking for. That formula gave
me a sum based on conditions, basically I am looking for a list. Basically
what I am trying to achieve can be done using the filter function, however I
want sheet 2 to populate when I enter values on sheet 1, without using
filters.

"JBeaucaire" wrote:

Or rather:

=SUMIF(Sheet1!D:D,"Y",Sheet1!B:B)
--
"Actually, I AM a rocket scientist." -- JB


"Spence10169" wrote:

Hello,

I have an excel file with multiple sheets, here are the colum headings from
sheet 1,

B C D E F

Date Amt Desc Category Total

What I want to do on sheet 2 is check for value "Y" in the Desc column on
sheet 1, if the value ="Y" then report the value in the Date column on sheet
1. I obtained this by using a simple =IF(Sheet1!D:D="Y", Sheet1!B:B)
function, but I only want to see the values that are = to "Y", when I
populate the if function, I get values for each row. In other words I only
want it to report the date column if the Desc column = Y.

Does this make sense?

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Location: Bakersfield, CA
Posts: 45
Send a message via Skype™ to JBeaucaire[_85_]
Default Copy values from a cell based on values of another cell

Okay, that's more difficult. A self-creating list on Sheet2 using the same
idea as a VLOOKUP does for 1 value/cell, you want a list created the same way.

http://home.pacbell.net/beban/

That website offers some user-defined-functions that all inter-relate. The
one you want to use here is VLOOKUPS. It's an array formula. You will need
to copy the code for VLOOKUPS and a few other supporting functions into a
module in your workbook. Copy the code for the functions:

VLookups
ArrayCountIf
ArrayDimensions
MakeArray

Then, on sheet2, enter a standard VLOOKUP type formula:

=VLOOKUPS("Y",Sheet1!$B$2:$D$10000,3)

When you press Enter, a message will appear int he cell telling you how many
cells downward you need to select. Highlight the cell and enough below to
complete the requested number, press F2, then CTRL-SHIFT-ENTER to activate
the array.

Two things, first - you will need to be OK swapping the two columns. VLookup
needs the column to search on the left. My formula above assumes you will
swap the two columns.

Second - since you want this summary to fill itself out as you go, even
though it says "Select at least 100 rows" or whatever, go ahead and go down
much further. The list will expand automatically as you add to the source
chart on Sheet1.

Hope this works as well for you as it does for me, I use this UDF for
several sheets of my own. - Jerry
--
"Actually, I AM a rocket scientist." -- JB


"Spence10169" wrote:

I put in the formula, but it isn't what I am looking for. That formula gave
me a sum based on conditions, basically I am looking for a list. Basically
what I am trying to achieve can be done using the filter function, however I
want sheet 2 to populate when I enter values on sheet 1, without using
filters.

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
How to assign values to a cell based on values in another cell? Joao Lopes Excel Worksheet Functions 1 December 5th 07 10:02 PM
Insert a value in a cell based upon a comparison of cell values in 2 separate worksheets Doctorjones_md Excel Discussion (Misc queries) 7 June 8th 07 09:32 PM
Insert a value in a cell based upon a comparison of cell values in 2 separate worksheets Doctorjones_md Excel Worksheet Functions 7 June 8th 07 09:32 PM
Format cell color based on multiple cell values Zenaida Excel Discussion (Misc queries) 1 May 10th 06 07:31 PM
Format cell color based on multiple cell values Zenaida Excel Discussion (Misc queries) 3 May 9th 06 11:56 PM


All times are GMT +1. The time now is 08:52 AM.

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"