Re: Need formula for data fetch in column and row simultaneously
In B2 of your first sheet try this.
=SUMPRODUCT((Sheet2!$D$1:$J$1=Sheet1!$A2)*(Sheet2!$A$2:$A$7=Sheet1!B$1)*(Sheet2!$D$2:$J$7="PASSED"))
Copy down and across.
Does this works for you?
Re: Need formula for data fetch in column and row simultaneously
In B2 of your first sheet try this.
=SUMPRODUCT((Sheet2!$D$1:$J$1=Sheet1!$A2)*(Sheet2!$A$2:$A$7=Sheet1!B$1)*(Sheet2!$D$2:$J$7="PASSED"))
Copy down and across.
Does this works for you?
Re: Paste from excel into form.
Quote...Sorry i did no even know these 2 forums was connected in any way
They don't. Just many same posters to both(and more) of these.
Take a look to the links that cytop provided(special to this with title) Message to Cross Posters
Also see the rule#8 of Excel forum. The meaning is that if for ANY REASON, you cross post, then you have to provide a link.
Quote..8. Don't cross-post without a link. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. Expect cross-posts without a link to be closed.
Re: Paste from excel into form.
Just to mention that this is cross posting here
Re: HLOOKUP with Multiple Results
Hi
In A8 put this ARRAY formula. Copy down and across.
=IFERROR(INDEX($A$1:$T$1,SMALL(IF($A2:$T2="x",COLUMN($A$1:$T$1)),COLUMN(A1))),"")
Re: Changing Monthly Data to Daily Data?
And if you don't mind, a suggestion without helper column.
In cell I2 put the first day your data will start in, so 1/1/1982
In cell I3, put the formula =I2+1 and drag down as you need.
In G2 and drag down this ARRAY formula.
=INDEX($B$2:$B$374;SMALL(IF(MONTH($A$2:$A$374)=MONTH(I2);ROW($B$2:$B$374)-1);ROW($B$1)))
Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows
You are welcome.
Quote..So... I'm assuming the -15 was needed to specify the offset from Row 1, where Excel would start its search by default?
YES. Also the last A1 is for ..counting...it's become A2---A3....as you drag down.First, second, third.....n case.....
Re: Select Totals and Display them in Separate Sheet
Could something like this works for you?
Re: Select Totals and Display them in Separate Sheet
Apologize for this but i'll have a delay in my answer as i am at work and have some seriously problems just now. Maybe 1-2 hours later..
Yes. We can do this using formulas.
Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows
In F16 and copy down.
=IFERROR(INDEX($A$16:$A$22;SMALL(IF($E$16:$E$22=1;ROW($A$16:$A$22)-15);ROW(A1)));"")
Pls see the red part of the formula.
Edit: YES. You can use Named ranges.
Re: vlookup
Hi
Pls don't be unpatient(regarding your pm).
In A2 of your second sheet, put this ARRAY formula. Copy down and across. I did an example for Open Status. Same way for the others.
=IF(ISERROR(INDEX(Sheet1!A$2:A$1000;SMALL(IF(Sheet1!$A$2:$A$1000="Open";ROW(Sheet1!A$2:A$1000)-1);ROW(Sheet1!A1))));"";INDEX(Sheet1!A$2:A$1000;SMALL(IF(Sheet1!$A$2:$A$1000="Open";ROW(Sheet1!A$2:A$1000)-1);ROW(Sheet1!A1))))
Does this work for you?
Re: Select Totals and Display them in Separate Sheet
My VBA knowledge is amost nonexistent!
But why do you want to use VBA, when you can do this using formulas?
Of course in any case, a sample workbook will help us, so to be able to help you.
Re: Multi-Level Data Validation Problem
Thanks Ger.:)
As you noticed My English are not so good, so my Explanations(in any issue) may not be understood by many members of the forum.
Re: Select Totals and Display them in Separate Sheet
You are welcome.
Thanks for the feed back.
Re: If a Looked Up MAX repeats, return the multiple values from corresponding rows
Quote....What significance do the curly brackets have?
Arrays formulas create these brackets(No need to type these) if you confirm(Control+Shift+Enter) correctly these. Read this excellent article about Arrays Formulas.
http://www.cpearson.com/excel/ArrayFormulas.aspx
In your case now. Do you use Excel < 2007?
If YES, then IFERROR will not work for you. You need IF(ISERROR function.
=IFERROR(INDEX($B$1:$B$12;SMALL(IF($C$1:$C$12=1;ROW($B$1:$B$12));ROW(B1)));"")
=IF(ISERROR(INDEX($B$1:$B$12;SMALL(IF($C$1:$C$12=1;ROW($B$1:$B$12));ROW(B6))));"";INDEX($B$1:$B$12;SMALL(IF($C$1:$C$12=1;ROW($B$1:$B$12));ROW(B6))))
Take a look to the example sheet. I used both of these functions in my formula. If you use Excel<2007, maybe you will get some errors in some cells that i used IFERROR....
Re: Multi-Level Data Validation Problem
You are welcome BUT is not kind at all to cross posting. In All forums τηε respondents are volunteers who have at their leisure any knowledge to help. Read here why.
http://www.excelguru.ca/conten…ge-to-forum-cross-posters
Cross post here.
http://www.excelforum.com/exce…a-validation-problem.html
.
Re: Multi-Level Data Validation Problem
My first attachment is a sample of how can you fix it as i suggested in my previous post..
As you mentioned that you will need to use more sheets(tabs), my opinion is that will be difficult to use multiply if in the cell that you choose dates using data valitation.
So i add 2 more helper-hidden columns in Data Validation sheet and used a name in a dynamic range. MyRange is the name.
=OFFSET('Data Validation'!$C$2;0;0;COUNT('Data Validation'!$C:$C);1)
Now you can add as many as you want sheets and validation list in Report sheet will work perfectly.
Let us know!:yes:
Re: Select Totals and Display them in Separate Sheet
Thank you!:thanx: