Hi,

I have attached an example and I am using the following formula to create a dynamic date list, based a user entered start and end date. There is now a need to exclude certain days of the week, which I thought was easy enough. However, if the requested start date happens to be one of the excluded days it is still recorded and then makes my later calculations incorrect.

A2 and A3 have my start and end dates.

TRUE/FALSE for days of week to be reported (these are driven by a form check box else where) then I create my WORKDAY detail from it. 0's and 1's.

I'm using the start of my list to equal the start date entered.

Subsequent dates are created by checking the first date and filling accordingly.

My problem is if I use the above formula to deduce the first date in the dynmic list and it is on an excluded day nothing gets generted. In the attached I've excluded Fri-Sun, but my first date in 1st Sept 2017, so a Friday and it gets listed.

How can I get round that with a formula?

Thanks all....#RealLifeDramas

I have attached an example and I am using the following formula to create a dynamic date list, based a user entered start and end date. There is now a need to exclude certain days of the week, which I thought was easy enough. However, if the requested start date happens to be one of the excluded days it is still recorded and then makes my later calculations incorrect.

A2 and A3 have my start and end dates.

TRUE/FALSE for days of week to be reported (these are driven by a form check box else where) then I create my WORKDAY detail from it. 0's and 1's.

I'm using the start of my list to equal the start date entered.

Subsequent dates are created by checking the first date and filling accordingly.

Code:

`=IFERROR(IF($B$2>=WORKDAY.INTL(A5,1,$N$2),WORKDAY.INTL(A5,1,$N$2),""),"")`

How can I get round that with a formula?

Thanks all....#RealLifeDramas

Hi

I need formula which will show column title and row title in table for specific data which i will ask for. Note that data which i will look for will be always unique.

forum.png

I tried =INDEX($C$35:$E$35,MATCH($A$36,C36:E36,0)) but its only working in one column or in one row, not in multiple area. Same with HLOOKUP and VLOOKUP.

Thank you in advance for help.

P

I need formula which will show column title and row title in table for specific data which i will ask for. Note that data which i will look for will be always unique.

forum.png

I tried =INDEX($C$35:$E$35,MATCH($A$36,C36:E36,0)) but its only working in one column or in one row, not in multiple area. Same with HLOOKUP and VLOOKUP.

Thank you in advance for help.

P

I have a table of Spending with a Description column where the spending occured

I have a lookup table matching Shops where I spend money to a spending Category

The Shop will appear somewhere in the Description

I want to populate my Spending table with the Category that matches the Description

Description could be "Trans 345 Wallmart Chicago"

The lookup table could be "Wallmart = Groceries"

So in Spending table it would insert Groceries next to the Description above

I have a lookup table matching Shops where I spend money to a spending Category

The Shop will appear somewhere in the Description

I want to populate my Spending table with the Category that matches the Description

Description could be "Trans 345 Wallmart Chicago"

The lookup table could be "Wallmart = Groceries"

So in Spending table it would insert Groceries next to the Description above

PDF "File 1" can be reached by

HYPERLINK("C:\Doc/File%201.pdf","Test")

MSG "File 1" can be reached by

HYPERLINK("C:\Doc/File%201.msg","Test")

How to combine the above 2 formulas into 1 for

"File 2" that may be PDF or MSG.

]]>HYPERLINK("C:\Doc/File%201.pdf","Test")

MSG "File 1" can be reached by

HYPERLINK("C:\Doc/File%201.msg","Test")

How to combine the above 2 formulas into 1 for

"File 2" that may be PDF or MSG.

Hi what is the formula for calculating time and put value..

I work up-to 06:00 hrs means 0.3

i work more than 06:00 hrs and up-to 12:00 hrs means 0.7

I work more than 12 hours in a day means 1.0

]]>I work up-to 06:00 hrs means 0.3

i work more than 06:00 hrs and up-to 12:00 hrs means 0.7

I work more than 12 hours in a day means 1.0

Hi, our company always have those Upgrading project. They have set aside a certain amount for each project. Example, $3000 for Chairs. Whenever we received invoices pertaining to this budgeted $3000. We have to put it in excel so that we know how much is left in this $3000. However, most of the time, a single $3000 budget is make up of many invoices. This make presentation very ugly and We need constantly update formula and inserting row etc. See attached file and you know what i roughly mean.

Does anyone have any better idea how to keep track?

Does anyone have any better idea how to keep track?

Hi guys I need help to allocate the table seating accurately.

Lets say I have a name list and number of people will be attending and table allocate to them as follows:

__Name__ __Pax__ __Table No__

Amber 2 2

Bobby 5 1

Chris 1 2

Derrick 3 3

If I wanna look up for table 2, what formula can be used even if I change the number of people and table number? For example, now I know table 2 has 3 pax, if I change Chris from 1 to 2 pax, how would the table 2 can be affected and change to 4 pax?

]]>Lets say I have a name list and number of people will be attending and table allocate to them as follows:

Amber 2 2

Bobby 5 1

Chris 1 2

Derrick 3 3

If I wanna look up for table 2, what formula can be used even if I change the number of people and table number? For example, now I know table 2 has 3 pax, if I change Chris from 1 to 2 pax, how would the table 2 can be affected and change to 4 pax?

Hi - I am looking for a way to change all cell references from relative to absolute. Is there an easy way to do this?

thanks in advance.

]]>thanks in advance.

So desperate right now, any help GREATLY appreciated:

I have worksheets titled 001, 002, 003 and so on (up to 200) on each sheet, I have a cell (C6) that I need to enter information in - which will then need to display on a separate sheet (register)

On the register sheet, I have a column to display the information from C6 on each separate sheet (each sheet will have different info)

The formula I have in the first cell is: =IF('001'!$C$6="","",'001'!$C$6)

I need the '001' parts to automatically increment to 002, 003 and so on....

I have tried all the usual dragging, fill series, flash fill etc and NOTHING is working :(

TIA.

]]>I have worksheets titled 001, 002, 003 and so on (up to 200) on each sheet, I have a cell (C6) that I need to enter information in - which will then need to display on a separate sheet (register)

On the register sheet, I have a column to display the information from C6 on each separate sheet (each sheet will have different info)

The formula I have in the first cell is: =IF('001'!$C$6="","",'001'!$C$6)

I need the '001' parts to automatically increment to 002, 003 and so on....

I have tried all the usual dragging, fill series, flash fill etc and NOTHING is working :(

TIA.

Will Excel do this?

If "1" is entered into A1, I want the cursor to jump to B1.

If "2" is entered into A1, I want the cursor to jump to B2.

Any help will be greatly appreciated!

]]>If "1" is entered into A1, I want the cursor to jump to B1.

If "2" is entered into A1, I want the cursor to jump to B2.

Any help will be greatly appreciated!

Good evening, I have a problem with a file, making the sum with different formulas

in cell D-E-F45 and 46 yellow in color as a result 0

I do not understand why you can help me?

Thank you.

in cell D-E-F45 and 46 yellow in color as a result 0

I do not understand why you can help me?

Thank you.

Hi,

I've been trying to do an index/if/row/column formula and I can't get it to work. I've attached the workbook in question. On the "page" tab, I have a list of items that are sorted by velocity. In cell C2, I need a formula that will lookup the value in cell A2 and return multiple results in cells C2, D2, E2, etc. from the data in the "Data" tab.

Example is shown in cell C3-H3.

Thanks!

Chad

I've been trying to do an index/if/row/column formula and I can't get it to work. I've attached the workbook in question. On the "page" tab, I have a list of items that are sorted by velocity. In cell C2, I need a formula that will lookup the value in cell A2 and return multiple results in cells C2, D2, E2, etc. from the data in the "Data" tab.

Example is shown in cell C3-H3.

Thanks!

Chad

I am trying to create a bill for a local hotel where they have different prices depending on the time of year. For example between 15.4. and 15.8. are the summer prices and 16.8 -14.4 the winter prices

I have it set up so that when the room is selected it looks up the price from the corresponding list. However it is using the year also and will therefore only work for this years booking.

It is set to look up the corresponding rooms from different lists depending on time of year, the lists are as below.

Summer price 15.4. 15.8.

1hh 79,00 €

2hh 109,00 €

2hh+sauna 119,00 €

Apartment 5hh 230,00 €

Winter price 16.8. 14.4.

1hh 69,00 €

2hh 89,00 €

2hh+sauna 101,00 €

Apartment 5hh 210,00 €

The formula I am using at is,

=IFERROR(IF(AND(B11>=Sheet2!$B$1;B11<=Sheet2!$C$1);VLOOKUP(A11;Sheet2!$A$2:$B$6;2);VLOOKUP(A11;Sheet2!$A$9:$B$13;2));"")

It works unless I go over this year. Is there a way it can be set to ignore the year? or is it a case of changing the parameter year when it changes to 2018? or will it automatically change it, since I did not enter a year on the look up inputs but it automatically entered 2017 when the cell was formatted to a date, even in just day and month style?

]]>I have it set up so that when the room is selected it looks up the price from the corresponding list. However it is using the year also and will therefore only work for this years booking.

It is set to look up the corresponding rooms from different lists depending on time of year, the lists are as below.

Summer price 15.4. 15.8.

1hh 79,00 €

2hh 109,00 €

2hh+sauna 119,00 €

Apartment 5hh 230,00 €

Winter price 16.8. 14.4.

1hh 69,00 €

2hh 89,00 €

2hh+sauna 101,00 €

Apartment 5hh 210,00 €

The formula I am using at is,

=IFERROR(IF(AND(B11>=Sheet2!$B$1;B11<=Sheet2!$C$1);VLOOKUP(A11;Sheet2!$A$2:$B$6;2);VLOOKUP(A11;Sheet2!$A$9:$B$13;2));"")

It works unless I go over this year. Is there a way it can be set to ignore the year? or is it a case of changing the parameter year when it changes to 2018? or will it automatically change it, since I did not enter a year on the look up inputs but it automatically entered 2017 when the cell was formatted to a date, even in just day and month style?

Okay, here's the concept of what I'm dealing with. (Second post today, first one was answered very well so here I am again :) )

The first two columns are inputs, the third column (Unique/Duplicate?) is an output I already have the formula for, and the fourth column (Node) is what I need an equation for. As you can see from the below table, once a node has been established as unique, it must be referenced back to if it duplicates.

Again, to reiterate - I only need the column D formula.

Thanks you, supreme overlords of excel

]]>The first two columns are inputs, the third column (Unique/Duplicate?) is an output I already have the formula for, and the fourth column (Node) is what I need an equation for. As you can see from the below table, once a node has been established as unique, it must be referenced back to if it duplicates.

Again, to reiterate - I only need the column D formula.

X | Y | Unique/Duplicate? | Node |

2 | 3 | Unique | 1 |

3 | 0 | Unique | 2 |

4 | 3 | Unique | 3 |

5 | 3 | Unique | 4 |

6 | 0 | Unique | 5 |

3 | 0 | Duplicate | 2 |

6 | 0 | Duplicate | 5 |

7 | 2 | Unique | 6 |

3 | 0 | Duplicate | 2 |

Thanks you, supreme overlords of excel

Hi,

I'm running a savings scenario where the ending value of my investment ends up being less than the total contributions based on monthly returns during a down market period, but the XIRR is returning a 0.00% instead of a negative number. In other periods, when the ending value is greater than the total contributions, the XIRR formula works properly. Can anybody explain/provide a formula that will return a negative XIRR in the attached scenario? Thanks so much!

I'm running a savings scenario where the ending value of my investment ends up being less than the total contributions based on monthly returns during a down market period, but the XIRR is returning a 0.00% instead of a negative number. In other periods, when the ending value is greater than the total contributions, the XIRR formula works properly. Can anybody explain/provide a formula that will return a negative XIRR in the attached scenario? Thanks so much!