Hello everyone,

I require some help again with cross-sheet formulas. I'm just not sure where to start or how to format them properly.

I have attached the sanitized workbook.

Here is the flow I need to occur:

I hope this makes sense.

Thanks for the anticipated help!

I require some help again with cross-sheet formulas. I'm just not sure where to start or how to format them properly.

I have attached the sanitized workbook.

Here is the flow I need to occur:

- Data is entered from a source in either SAFE PRACTICES or INSPECTION FINDINGS tabs on a rolling daily basis.
- Then in each monthly tab, the data needs to be totaled as shown (automatically) if:

- It matches the date for the tab
- SA Referenced equals "Yes"
- Then totals it as shown by the SA Type

I hope this makes sense.

Thanks for the anticipated help!

Hello,

I'm trying to display names in order of rank, based on the value next to the names.

It doesn't work when there's a tie.

Does anyone know how to get it to work, such as with some sort of tie-breaker? Thanks a lot

I'm trying to display names in order of rank, based on the value next to the names.

It doesn't work when there's a tie.

Does anyone know how to get it to work, such as with some sort of tie-breaker? Thanks a lot

I work for a waste water company and we would like to begin using Excel to keep track of inventory as well as monitor any work orders. To do this I have created a spreadsheet with 6 tabs. I would like to be able to type what equipment was used during a work order (For example; one bolt), and have that one bolt deducted automatically from the inventory list on a separate tab. I have included attachments of my example in the spreadsheet I created for the reader to understand completely. You can see on the sheet labeled "inventory", I have the bolt in question with a total quantity of 25. In the spreadsheet labeled "WORK ORDERS", you will see that I have the example in question have a quantity used of 2. I need the formula for the deductions to be made automatically, not just for the cells I have mentioned, but for all future cells in those columns. Obviously this is only going to work if Excel is able to recognize names of parts as well. For instance, if I type "2 bolts" in the equipment used section, it must be able to recognize the "bolts" part, in order to deduct it from the proper column on my inventory list. If this is not possible, I would greatly appreciate any advice on how to correct my spreadsheet. Thank you so much in advance!!!!

grinpas1.pnggrinpas3.png

]]>grinpas1.pnggrinpas3.png

Have what is probably either really simple or completely impossible to do.

I want to be able to dynamically adjust a value which is a product of values summed across column the length of which a based on a date value

Here's the scenario

Row 1 contains a 21 day date range starting on column B

Rows 2-50 have a dollar amount that corresponds to Row 1. Together they show a the cost/day.

Clear over on column AJ I have a date that changes based on a bunch of other formulas and calculations throughout the rest of the sheet.

My hope is that I can have Excel calculate the sum of the dollar amount up to the date that shows up in column AJ

For example:

Row 1 10/12, 10/13, 10/14.....10/31

Row 2 $100, $150, $130, $175.....$145

Cell AO2 has the date 10/25

I would like to sum the values on Row 2 from 10/12 (B2) to 10/25 only (P2).

If the date in AO2 changes to 10/23 for example (due to the rest of the things going on in the spreadsheet), I'd like the values summed to go only to 10/23 (N2) and so on.

I tried using SUMIF(B1:Y1,"<AJ2",B2:Y2) but it returns a 0.

Any help/ideas would be very appreciated. If I can make this work it will make my life 1000x easier!

Thanks in advance

]]>I want to be able to dynamically adjust a value which is a product of values summed across column the length of which a based on a date value

Here's the scenario

Row 1 contains a 21 day date range starting on column B

Rows 2-50 have a dollar amount that corresponds to Row 1. Together they show a the cost/day.

Clear over on column AJ I have a date that changes based on a bunch of other formulas and calculations throughout the rest of the sheet.

My hope is that I can have Excel calculate the sum of the dollar amount up to the date that shows up in column AJ

For example:

Row 1 10/12, 10/13, 10/14.....10/31

Row 2 $100, $150, $130, $175.....$145

Cell AO2 has the date 10/25

I would like to sum the values on Row 2 from 10/12 (B2) to 10/25 only (P2).

If the date in AO2 changes to 10/23 for example (due to the rest of the things going on in the spreadsheet), I'd like the values summed to go only to 10/23 (N2) and so on.

I tried using SUMIF(B1:Y1,"<AJ2",B2:Y2) but it returns a 0.

Any help/ideas would be very appreciated. If I can make this work it will make my life 1000x easier!

Thanks in advance

Hi guys, I am trying to figure out the best formula to use for my scoring excel sheet. For instance, if teams get the same score, the next criteria that will determine who's in the higher placement in the tournament will be where they place in their game.

I am using VLOOKUP to populate a table in Excel. The formula is identical from row to row, except the file in which data is being referred to is changing by one character.

For example, cell B1 says:

=VLOOKUP($A$1,'N:\August\[08-05-2018.xlsx]Assembly Throughput'!$A$3:$O$64,B35,FALSE)

Cell B2 says:

=VLOOKUP($A$1,'N:\August\[08-06-2018.xlsx]Assembly Throughput'!$A$3:$O$64,B35,FALSE)

I would like to autofill the rest of the rows so that cell B3 refers to the file "08-06-2018.xlsx", cell B4 refers to the file "08-07-2018.xlsx", etc, but I don't know how to automatically increment these numbers, considering they are the title of a file. Is this possible? If not, is there another way to populate this table without manually inputting the file names?

]]>For example, cell B1 says:

=VLOOKUP($A$1,'N:\August\[08-05-2018.xlsx]Assembly Throughput'!$A$3:$O$64,B35,FALSE)

Cell B2 says:

=VLOOKUP($A$1,'N:\August\[08-06-2018.xlsx]Assembly Throughput'!$A$3:$O$64,B35,FALSE)

I would like to autofill the rest of the rows so that cell B3 refers to the file "08-06-2018.xlsx", cell B4 refers to the file "08-07-2018.xlsx", etc, but I don't know how to automatically increment these numbers, considering they are the title of a file. Is this possible? If not, is there another way to populate this table without manually inputting the file names?

I need to take cells from column C (one text string) and search into column A (multiple text strings separated by a new line) and if the text string from C exists in column, then plug in the cell adjacent to the search value (column D, the numbers) into Column B if it exists in column A's Cell. The cell needs to be separated by a coma and concatenated. If this is enough to explain, I will be more than happy to explain it further.

I am trying to find a formula that will sum cells based on criteria. For example, if I select an input of 1, I need it to add cells A1 and B6,11,16, & 21. An input of 2 needs to sum cells A1, A5 and B11, 16, & 21. I was wondering if INDEX would work in this case. Thanks in advance!

Hi,

I have a table which has cost values for every product, But in another worksheet, I want excel to only populate list of products that cost more than $30. How do I do that? Can it be done by writing formulas?

Example:

Product (column A) Price (Column B)

A $40

B $60

C $20

D $10

E $82

F $5

G $93

H $35

I want the result as:

Product (column A) Price (Column B)

A $40

B $60

E $82

G $93

H $35

]]>I have a table which has cost values for every product, But in another worksheet, I want excel to only populate list of products that cost more than $30. How do I do that? Can it be done by writing formulas?

Example:

Product (column A) Price (Column B)

A $40

B $60

C $20

D $10

E $82

F $5

G $93

H $35

I want the result as:

Product (column A) Price (Column B)

A $40

B $60

E $82

G $93

H $35

I have an extensive spreadsheet I use to track tax savings for a job. We have formulas in the H and I columns of the attached sheet on the first tab. They have been working for months. All of the sudden, the formulas are no longer working and I can not figure out why. I have highlighted the areas where the problem is. If anyone can help me figure out why they would no longer work, it would be greatly appreciated.

The formula in H column is =IF(E4=0," ",IF(SUM(F4-G4)<=4999.99,SUM(F4-G4)*0.075,(SUM(F4-G4)*0.06)+75)) and in the I column it is =IF(E4=0," ",SUM(F4+H4))

The formula in H column is =IF(E4=0," ",IF(SUM(F4-G4)<=4999.99,SUM(F4-G4)*0.075,(SUM(F4-G4)*0.06)+75)) and in the I column it is =IF(E4=0," ",SUM(F4+H4))

I need help to figure out how to best do this. Tried to search it and didn't see anything that works exactly. Here is what I want to do.

I want to vlookup/or hlookup into a different sheet (Bottom table is what that sheet should look like) where the value is in different column and rows and return the number underneath the lookup value.

The only way I can think of is by using combo of iferror and hlookup formula, but the formula can get very long since the actual data set I am working with is pretty large. Anyway I can do this another way?

not sure if I am explaining this correctly! Thanks for your help in advance

Capture.PNG

]]>I want to vlookup/or hlookup into a different sheet (Bottom table is what that sheet should look like) where the value is in different column and rows and return the number underneath the lookup value.

The only way I can think of is by using combo of iferror and hlookup formula, but the formula can get very long since the actual data set I am working with is pretty large. Anyway I can do this another way?

not sure if I am explaining this correctly! Thanks for your help in advance

Capture.PNG

I have developed an excel spreadsheet to record detail of software testing.

I have applied conditional formatting to sheets 1,2 & 3 which will highlight rows 12-30 based on the input to column G. If "N" is input then the row turns amber and if "Y" is input then the row turns green. This works very well right up until I expand the range by adding more rows in. The "Applies to" field in the "conditional formatting rules manager" behaves in very strange ways and the formatting becomes really unpredictable, meaning I have to constantly go back into the rules manager to correct the range. Is there a way to get the range in the "applies to" field to automatically update, correctly?

The other thing I would like to be able to do would be to get the text from the entire row to mirror into sheet 4 if "N" is in input into column G on sheets 1, 2 or 3. Is there a formula which would allow this to happen?

(I think..) I have attached the spreadsheet I am using in the hope this is helpful.

Thanks in advance!

I have applied conditional formatting to sheets 1,2 & 3 which will highlight rows 12-30 based on the input to column G. If "N" is input then the row turns amber and if "Y" is input then the row turns green. This works very well right up until I expand the range by adding more rows in. The "Applies to" field in the "conditional formatting rules manager" behaves in very strange ways and the formatting becomes really unpredictable, meaning I have to constantly go back into the rules manager to correct the range. Is there a way to get the range in the "applies to" field to automatically update, correctly?

The other thing I would like to be able to do would be to get the text from the entire row to mirror into sheet 4 if "N" is in input into column G on sheets 1, 2 or 3. Is there a formula which would allow this to happen?

(I think..) I have attached the spreadsheet I am using in the hope this is helpful.

Thanks in advance!

I have a database of loans (several thousand records) in this structure:

# date description $

4220275 16/10/2018 repayment 1275145.48

4220275 14/10/2018 Providing a loa 1275000

Trying to build a unified row in this structure:

1 2 3 4 5 6 7

# start date end date amount taking $ repayment amount $ # of days interest

4220275 14/10/2018 16/10/2018 1275000 1275145.48 calculatedif between col 2 &3 calculate dif between col 4 &5

thank you

]]># date description $

4220275 16/10/2018 repayment 1275145.48

4220275 14/10/2018 Providing a loa 1275000

Trying to build a unified row in this structure:

1 2 3 4 5 6 7

# start date end date amount taking $ repayment amount $ # of days interest

4220275 14/10/2018 16/10/2018 1275000 1275145.48 calculatedif between col 2 &3 calculate dif between col 4 &5

thank you

Afternoon,

I have a list of dates that a set of devices were activated, and I need a formula to calculate the number of months since each date. The tricky part is I need to know how many months the device was activated in certain 12 month windows as well.

For example an activation date of 01/07/2017.

In the date period 01/10/2016 - 31/09/2017 it would return 3 months (July, aug, sept)

In the date period 01/10/2017 - 31/09/2018 it would return 12 months

In the date period 01/10/2018 - 31/09/2019 it would currently return 1, but would increase each month we progress

The formula would need to look at the number of months in that time period (returning 0 if the activation was after the time period), with each time period having a column for evidence purposes.

The formula I tried works for the most part, but errors where the current month happens to match the month (regardless of year) the device was activated, and doesn't always return the right number of month.

=(IF($E7>F$5,0,IF(DATEDIF($E7,F$5,"m")>12,12,DATEDIF($E7,F$5,"m"))))

-(IF(DATE(YEAR(F$5),MONTH($E7),DAY($E7))<=$D$2,0,DATEDIF($D$2,F$5,"m")))

Where E7 is the Activation date, F5 is the 1st of the next time period, and D2 is the Current month.

Any help would be much appreciated!

]]>I have a list of dates that a set of devices were activated, and I need a formula to calculate the number of months since each date. The tricky part is I need to know how many months the device was activated in certain 12 month windows as well.

For example an activation date of 01/07/2017.

In the date period 01/10/2016 - 31/09/2017 it would return 3 months (July, aug, sept)

In the date period 01/10/2017 - 31/09/2018 it would return 12 months

In the date period 01/10/2018 - 31/09/2019 it would currently return 1, but would increase each month we progress

The formula would need to look at the number of months in that time period (returning 0 if the activation was after the time period), with each time period having a column for evidence purposes.

The formula I tried works for the most part, but errors where the current month happens to match the month (regardless of year) the device was activated, and doesn't always return the right number of month.

=(IF($E7>F$5,0,IF(DATEDIF($E7,F$5,"m")>12,12,DATEDIF($E7,F$5,"m"))))

-(IF(DATE(YEAR(F$5),MONTH($E7),DAY($E7))<=$D$2,0,DATEDIF($D$2,F$5,"m")))

Where E7 is the Activation date, F5 is the 1st of the next time period, and D2 is the Current month.

Any help would be much appreciated!

Hi,

I have two columns... Column AB with product names and column AD with a prices. I need to have a Top 10 and Bottom 10 results. The information in both column AB and AD will change frequently. Right now I have the following:

Same code for the bottom 10 just with =SMALL in columns AP5 and AQ5

This works great except every so often there is a duplicate price, and when that happens the first product name is listed for all the duplicate prices. I've tried all the examples I can find and nothing seems to work in my situation so I'm not sure what I'm missing.

When there are duplicate prices I need each individual product name to show.

Any help would be greatly appreciated.

]]>I have two columns... Column AB with product names and column AD with a prices. I need to have a Top 10 and Bottom 10 results. The information in both column AB and AD will change frequently. Right now I have the following:

Code:

`Top 10 Results `

Pricing (starting in AM5)

=LARGE($AD$5:$AD$900,1)

=LARGE($AD$5:$AD$900,2)

=LARGE($AD$5:$AD$900,3)

and so on through row AD14

Product Name (starting in AN5)

=INDEX($AB$5:$AB$900,MATCH(AM5,$AM$5:$AM$900,0))

=INDEX($AB$5:$AB$900,MATCH(AM6,$AM$5:$AM$900,0))

and so on through row AN14

This works great except every so often there is a duplicate price, and when that happens the first product name is listed for all the duplicate prices. I've tried all the examples I can find and nothing seems to work in my situation so I'm not sure what I'm missing.

When there are duplicate prices I need each individual product name to show.

Any help would be greatly appreciated.