Hello Everyone,

My version of MS Office is 2007. I think there is no simple solution for my question - If there is a solution without using Macros then perfecto!!! But anyway if Macros can give a definitive answer, please don't hesitate to share it as I desperately need to know this. However, I never used macros, but anyway...........

I have a random numbers array say from A2 to A12 filled with random numbers on Sheet1.

Random numbers.jpg

At the same time I have a large table which contains a lot of data, including my random numbers on Sheet2.

Table array.jpg

Now my question is - On Sheet 3 I want to extract respective rows from the Table array. I need to find all the values of the small array in the large table's column A, and extract respective rows in Sheet3.

Final result.jpg

In other words - I want to find A2 value in the Column A of the Table array and copy the remaining row into another sheet. Then look for A3 in the table, then A4, etc.. But I want the Excel to do it for me, otherwise this whole thing is pointless.

One thing that is for sure - the random values are unique. so there shouldn't be any duplicates. Each random value in both arrays appears only once.

If you read up to this point, then first of all thanks for your time, secondly if you think this is way too easy, then sorry.

Anyway, I appreciate any help.

]]>My version of MS Office is 2007. I think there is no simple solution for my question - If there is a solution without using Macros then perfecto!!! But anyway if Macros can give a definitive answer, please don't hesitate to share it as I desperately need to know this. However, I never used macros, but anyway...........

I have a random numbers array say from A2 to A12 filled with random numbers on Sheet1.

Random numbers.jpg

At the same time I have a large table which contains a lot of data, including my random numbers on Sheet2.

Table array.jpg

Now my question is - On Sheet 3 I want to extract respective rows from the Table array. I need to find all the values of the small array in the large table's column A, and extract respective rows in Sheet3.

Final result.jpg

In other words - I want to find A2 value in the Column A of the Table array and copy the remaining row into another sheet. Then look for A3 in the table, then A4, etc.. But I want the Excel to do it for me, otherwise this whole thing is pointless.

One thing that is for sure - the random values are unique. so there shouldn't be any duplicates. Each random value in both arrays appears only once.

If you read up to this point, then first of all thanks for your time, secondly if you think this is way too easy, then sorry.

Anyway, I appreciate any help.

Hello i need some help with a table, it goes like this:

I need to show me on A, how much money have left when the filtered data from C, the max allowed money is on E.

For example, if i filter "Publicidade" on C, it show me on B all the money i spent. But i want to show also on A how much money i have left based on E

sheet.jpg

I need to show me on A, how much money have left when the filtered data from C, the max allowed money is on E.

For example, if i filter "Publicidade" on C, it show me on B all the money i spent. But i want to show also on A how much money i have left based on E

sheet.jpg

Hi guys, I need to know if it is possible to use the index match function (or sumifs maybe?) to sum values across multiple sheets using the same range i.e

i.e.

where the sheet range is April:March

]]>i.e.

Code:

`=INDEX(AprilCSV!D:D,MATCH(Overview!D6,AprilCSV!C:C,0))`

Hi,

I am familiar with knowing how to use the INDEX MATCH formula and VLOOKUPS.

However, I noticed I am not getting the desired result when working with dates.

For example, say column A contains dates written like 25/11/2013, 01/06/2011, 23/10/2020.

Column B has a header called "ID" it has a range of integer values like 6,34, 54,1,7,23 etc.

Now, I want to find the ID(which is in column B) associated with the date(which is in column A).

So, I was typing =INDEX("A1:C500",MATCH("25/11/2013",A:A,0),MATCH("ID",A1:C1,0))

but I am getting #n/a when I can clearly see from my excel spreadsheet the ID linked to 25/11/2013 is 5.

Any ideas?

Thanks

]]>I am familiar with knowing how to use the INDEX MATCH formula and VLOOKUPS.

However, I noticed I am not getting the desired result when working with dates.

For example, say column A contains dates written like 25/11/2013, 01/06/2011, 23/10/2020.

Column B has a header called "ID" it has a range of integer values like 6,34, 54,1,7,23 etc.

Now, I want to find the ID(which is in column B) associated with the date(which is in column A).

So, I was typing =INDEX("A1:C500",MATCH("25/11/2013",A:A,0),MATCH("ID",A1:C1,0))

but I am getting #n/a when I can clearly see from my excel spreadsheet the ID linked to 25/11/2013 is 5.

Any ideas?

Thanks

Hello. Happy to be here on the forum!

I have recently switched from libreoffice to microsoft office 2016 because there are many more features to 2016 that I need. I have run into a problem I can not solve myself (which is odd)

In libreoffice it would work no problem but in office it does not.

Here's what I am doing:

I have 2 sheets in a workbook. sheet 1 is a list of data. Sheet 2 is a interactive form type of page that pulls data from sheet 1 and I print.

On sheet 2 I use data validation in cells to provide a drop down list of the specific cells on sheet 1.

On sheet 2, next to the data validation field, I then use a VLOOKUP to retrieve the data in the fields from sheet 1 based on what is selected in the data validation field.

So in my VLOOKUP formula my first argument is the data validation field.

Here's the problem:

In libreoffice the VLOOKUP formula would complete the formula with the cell that data validation was referencing from sheet 1 giving me all the information in the cells next to whichever I selected from sheet 1.

So whenever I changed the selection in data validation my VLOOKUPS would change with the data validation to whichever cell data validation was referencing from sheet 1.

In MSOffice when I use the data validation cell in the first argument for my VLOOKUP MS Office references the actual cell on sheet 2 and doesnt recognize that it is a data validation field. That makes my VLOOKUP formulas reference sheet 2 and not sheet 1.

My question:

How do I make the VLOOKUP formula access the data through the data validation selection and not the cell the data validation is placed in.

Much appreciated.

Andy

]]>I have recently switched from libreoffice to microsoft office 2016 because there are many more features to 2016 that I need. I have run into a problem I can not solve myself (which is odd)

In libreoffice it would work no problem but in office it does not.

Here's what I am doing:

I have 2 sheets in a workbook. sheet 1 is a list of data. Sheet 2 is a interactive form type of page that pulls data from sheet 1 and I print.

On sheet 2 I use data validation in cells to provide a drop down list of the specific cells on sheet 1.

On sheet 2, next to the data validation field, I then use a VLOOKUP to retrieve the data in the fields from sheet 1 based on what is selected in the data validation field.

So in my VLOOKUP formula my first argument is the data validation field.

Here's the problem:

In libreoffice the VLOOKUP formula would complete the formula with the cell that data validation was referencing from sheet 1 giving me all the information in the cells next to whichever I selected from sheet 1.

So whenever I changed the selection in data validation my VLOOKUPS would change with the data validation to whichever cell data validation was referencing from sheet 1.

In MSOffice when I use the data validation cell in the first argument for my VLOOKUP MS Office references the actual cell on sheet 2 and doesnt recognize that it is a data validation field. That makes my VLOOKUP formulas reference sheet 2 and not sheet 1.

My question:

How do I make the VLOOKUP formula access the data through the data validation selection and not the cell the data validation is placed in.

Much appreciated.

Andy

Please help..

I want to conditional format the dates in column F to highlight when it is 2 days more than column E but exclude weekends

I know it's part of NETWORK days formula but mine isn't working

Thanks

]]>I want to conditional format the dates in column F to highlight when it is 2 days more than column E but exclude weekends

I know it's part of NETWORK days formula but mine isn't working

Thanks

Hello,

I have a list of triplicats values taken from a biological assay. I would like to calculate the average of each triplicats, under the following conditions:

1. Anyway include the median in the average.

2. For the other values: if the value is within the median +/- the standard deviation, include the value in the average

3. If none of the above: include all 3 values in the average.

In other words, there will always be at least 2 values in the average: 2__or__ 3 if they are within the range of the median +/- stdev, or anyway 3 if none of them is.

How do I accomplish this rule?

Many thanks :)

Roy

]]>I have a list of triplicats values taken from a biological assay. I would like to calculate the average of each triplicats, under the following conditions:

1. Anyway include the median in the average.

2. For the other values: if the value is within the median +/- the standard deviation, include the value in the average

3. If none of the above: include all 3 values in the average.

In other words, there will always be at least 2 values in the average: 2

How do I accomplish this rule?

Many thanks :)

Roy

I have a large workbook containing many sheets looking at student progress in a school.

5 sheets are pasted into the workbook, each containing information for a year group - this is the raw data (Yr 7 Aut 1, Yr 8 Aut 1 etc.).

The information for our Senior Leaders is contained in sheets Year Groups and Subjects - every other sheets is used to work towards providing that information.

In an effort to make the workbook more easily used, I've created a DataCells sheet which "declares" which rows and columns data is held - subjects change year on year and I don't want to have to change the formulas at the start of each academic year. I have tried to create lots of validating cross checks to make sure that the data that comes in is all counted in the appropriate places SO......

On the DataCells sheet I have several COUNTA formulas in row 6 columns E,I,M,Q & U which count all data in the ranges that I want to work on. This is totalled in W6 for a total data count and cross checked on the Counts sheet AT68 which in turn is then checked against the Year Groups Sheet C3 which is finally checked against the Subjects sheet A264!!! Yes - the data needs to be correct to be useful. There are other cross checks built in but they are unimportant to my current problem.

The Counts sheet works perfectly - it is a simple COUNTIF for each year group.

The SEN COUNTS, PP COUNTS AND EAL COUNTS sheets are all variations of the original Counts sheet but they are only looking at what we call "vulnerable" students - these are identified on the Raw Data sheets (Yr7 Aut 1 etc.) in:

Column D for SEN students who have the value of K or E

Column B or F for PP Students who have a Y in either column and

Column E for EAL Students who have value of Y

I have to use COUNTIFS (for the EAL & SEN COUNTS) and SUMPRODUCT (for PP COUNTS). I still need to build the Subjects sheets using only these students but that is a little way off yet.

Now for the problem.......

Whilst these additional count sheets all look fine - I want to build the same checking mechanism into the sheets to ensure that I have captured all of the data. So I want to COUNTA the complete range of 'Yr 9 Aut 1'!J2:AT147 for example but only if the EAL column has a Y. And then of course the same for the PP Counts and SEN Counts. I just can't get my head around it - I think an INDEX MATCH with a counta may be the answer but I just can't see how.

Any help gratefully recieved

Many thanks

Sue

5 sheets are pasted into the workbook, each containing information for a year group - this is the raw data (Yr 7 Aut 1, Yr 8 Aut 1 etc.).

The information for our Senior Leaders is contained in sheets Year Groups and Subjects - every other sheets is used to work towards providing that information.

In an effort to make the workbook more easily used, I've created a DataCells sheet which "declares" which rows and columns data is held - subjects change year on year and I don't want to have to change the formulas at the start of each academic year. I have tried to create lots of validating cross checks to make sure that the data that comes in is all counted in the appropriate places SO......

On the DataCells sheet I have several COUNTA formulas in row 6 columns E,I,M,Q & U which count all data in the ranges that I want to work on. This is totalled in W6 for a total data count and cross checked on the Counts sheet AT68 which in turn is then checked against the Year Groups Sheet C3 which is finally checked against the Subjects sheet A264!!! Yes - the data needs to be correct to be useful. There are other cross checks built in but they are unimportant to my current problem.

The Counts sheet works perfectly - it is a simple COUNTIF for each year group.

The SEN COUNTS, PP COUNTS AND EAL COUNTS sheets are all variations of the original Counts sheet but they are only looking at what we call "vulnerable" students - these are identified on the Raw Data sheets (Yr7 Aut 1 etc.) in:

Column D for SEN students who have the value of K or E

Column B or F for PP Students who have a Y in either column and

Column E for EAL Students who have value of Y

I have to use COUNTIFS (for the EAL & SEN COUNTS) and SUMPRODUCT (for PP COUNTS). I still need to build the Subjects sheets using only these students but that is a little way off yet.

Now for the problem.......

Whilst these additional count sheets all look fine - I want to build the same checking mechanism into the sheets to ensure that I have captured all of the data. So I want to COUNTA the complete range of 'Yr 9 Aut 1'!J2:AT147 for example but only if the EAL column has a Y. And then of course the same for the PP Counts and SEN Counts. I just can't get my head around it - I think an INDEX MATCH with a counta may be the answer but I just can't see how.

Any help gratefully recieved

Many thanks

Sue

hi guys,

i have in worksheet Data columns with startdates;employee names and numbers

i want in sheet Column K the startdates ordererd from early to late based on day and employee

see attachment for example

kind regards

i have in worksheet Data columns with startdates;employee names and numbers

i want in sheet Column K the startdates ordererd from early to late based on day and employee

see attachment for example

kind regards

I have quite a large macro enabled workbook use for predicting performance of units. It involves lots of balances and formulas across many sheets. Some calculations are in VBA code, some are formulas in cells.

It has worked perfectly up until recently, now whenever I open it I get the "Excel cannot calculate a formula, circular reference that cannot be listed. Try entering the formula again or pressing undo" (or something similar).

Well as I've just opened the workbook I can't undo anything. The bottom left of the window says where there is a circular reference on each sheet but when I go through them there's nothing wrong with the formulas that allowed them to work in the first place. No reference errors, all named cells are present in the name manager.

It's becoming very frustrating to have to close this error every time I open the workbook and sometimes when I'm working in the workbook.

For confidential reasons I can't share the workbook in this forum, but has anyone else experienced this and found a solution?

]]>It has worked perfectly up until recently, now whenever I open it I get the "Excel cannot calculate a formula, circular reference that cannot be listed. Try entering the formula again or pressing undo" (or something similar).

Well as I've just opened the workbook I can't undo anything. The bottom left of the window says where there is a circular reference on each sheet but when I go through them there's nothing wrong with the formulas that allowed them to work in the first place. No reference errors, all named cells are present in the name manager.

It's becoming very frustrating to have to close this error every time I open the workbook and sometimes when I'm working in the workbook.

For confidential reasons I can't share the workbook in this forum, but has anyone else experienced this and found a solution?

Hello All,

I have a problem to make a hyperlink with dynamic reference (as in the pic followed

For example,

in cell A1, there's an ADDRESS formula in which it dynamically shows address value (e.g. T$9$, B$15$, etc.)

In the meantime, I want to have a hyperlinked text which links to the dynamically changing address in the cell A1. Is it possible? how can I do it?

Thank you in advance for any help

]]>I have a problem to make a hyperlink with dynamic reference (as in the pic followed

For example,

in cell A1, there's an ADDRESS formula in which it dynamically shows address value (e.g. T$9$, B$15$, etc.)

In the meantime, I want to have a hyperlinked text which links to the dynamically changing address in the cell A1. Is it possible? how can I do it?

Thank you in advance for any help

Dear All,

Please see the attachment.

Can anyone tell why the #N/A error in one only while calculating the best score.

Regards

Dastgir

Please see the attachment.

Can anyone tell why the #N/A error in one only while calculating the best score.

Regards

Dastgir

Hi guys,

Can anyone please help me with my VLOOKUP? In the spreadsheet attached, I am trying to to lookup which Interviewers (column B) have done a partial test (I have the details of all the interviewers that did a partial test on sheet 2). I am doing a VLOOKUP in O3 down (sheet 1) but not getting all the data come through??

I would be grateful if someone can take a look and advise.

Many thanks!

Can anyone please help me with my VLOOKUP? In the spreadsheet attached, I am trying to to lookup which Interviewers (column B) have done a partial test (I have the details of all the interviewers that did a partial test on sheet 2). I am doing a VLOOKUP in O3 down (sheet 1) but not getting all the data come through??

I would be grateful if someone can take a look and advise.

Many thanks!

In the workbook attached, how do I calculate the number of boys and the number of girls whose total marks in column N is >=200 and <=249?

My formulas in cells C50 and D50 are not working.

FORUMS.xlsx

]]>My formulas in cells C50 and D50 are not working.

FORUMS.xlsx

Hi Team,

I have 20 columns and 10000 rows I want to find out in a each row find max value and check after that max value is there any row value is less than 50% of the max value

Example

in a first row I have below data from A1 to L1 in that max value is 550, I want to get "Fail" if any value after this 550 is less than 50%.

350 360 370 400 500 550 350 250 360 370 350 220

Please any one can help me to do this.

Thanks in advance

]]>I have 20 columns and 10000 rows I want to find out in a each row find max value and check after that max value is there any row value is less than 50% of the max value

Example

in a first row I have below data from A1 to L1 in that max value is 550, I want to get "Fail" if any value after this 550 is less than 50%.

350 360 370 400 500 550 350 250 360 370 350 220

Please any one can help me to do this.

Thanks in advance