excel.jpg
OK...what we need to do is take data from sheet 1 to another sheet in the same workbook. We want to pull ONLY the code/amounts for the ones that have a balance over $0 (shown as a "-" in the cell). We tried VLOOKUP but that will pull over all codes...I am thinking INDEX/MATCH but not real sure how to pull it off.
Any help is greatly appreciated!
index(range.code,match(1;(range.amount>0)*(counif($H$1:H1,range.code)=0),)) as CSE
$H$1:H1 is here for example
Is "range" in your example a fill in for whatever I call the data on sheet 1?
Also...$H$1:H1 is the column on sheet 2 that the data will transfer to?
I apologize for the dumb questions...I have a basic knowledge of excel and this is over my head a little!
Hi
pls find the attachment..
This works and is more simple.
on sheet 1, change the amount to just 0 (where you has "-")
on sheet 2, use this code in cell A1
=IF(Sheet1!B1=0,,Sheet1!A1)
Then drag the fill handle down...
Hope this helps
How do I modify the formula on sheet 2 to make this work? I am sure that I have to add a sheet or range name somewhere.
This is in reply to Sadath31
Last edited by rr23724; 11-04-2015 at 11:08 AM.
This returns a bunch of "0" values...we need to display ONLY they ones that have a balance.Originally Posted by LukeGilfoyle
Here is an ARRAY formula that doesn't use helper columns. Copy the headings from Sheet1 to Sheet2 (A1 and B1) and enter this formula in A2 and fill across and down:
Formula:
Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Code Amount
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
How do I modify the formula on sheet 2 to make this work? I am sure that I have to add a sheet or range name somewhere.
No Problem. Use this then.
=IF(Sheet1!B1=0,"",Sheet1!A1)
---- And if you don't want any gaps between your codes then place the my code suggestion in sheet 2 cell "a2".
Then press the letter "A" (just above cell A1)..
then go to the 'data' tab and then press 'Filter'
You will see a small button appear in cell a1.
press it.
then press "blanks" to remove the tick.
This will then sort the colum just to show your codes without any gaps.
Hope this helps?
Last edited by LukeGilfoyle; 11-04-2015 at 11:41 AM. Reason: Improvement
This works exactly as I need except when I try to paste across it forces me to go over at least 5 columns and creates cells that I do not need. Is there any way to change that or do I just have to manually delete those extra columns of data.Here is an ARRAY formula that doesn't use helper columns. Copy the headings from Sheet1 to Sheet2 (A1 and B1) and enter this formula in A2 and fill across and down:
Formula:Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Code Amount
Never mind...I figured it out.
I think this is my answer...thank you!
Last edited by rr23724; 11-04-2015 at 11:55 AM.
That works but we are trying to make something that users do not have to do anything manually on sheet 2...people have routinely messed up anything that has filters or sorts.
For the first time using the formula, if the results are always going to be in the same place enter the formula as given then delete the columns not required. You can now copy those formulae and paste into a new worksheet.
Code Amount
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks