Hi All,

We have data for orders and items. Some orders contain more than 1 item. I need to make a list of unique orders and have item numbers in columns next to it. Any idea how to do it?

We have data for orders and items. Some orders contain more than 1 item. I need to make a list of unique orders and have item numbers in columns next to it. Any idea how to do it?

Hi all,

I am having an issue with Index, Match, Match - it works when one of the references is 7, but when I change it to 8 the formula doesn't work and throws me an error.

What I am trying to achieve is in columns L - Q to show whether a bill has been paid based on the info below:

Looking at the bank statement

comparing it to the months in E1-J1 and L1-Q12

comparing it to the expected spend (E-J)

if the amount in the bank account for that period and spend is the same as the expected amount, show as paid, otherwise show as unpaid

However, I just can't get it to work properly for all months, as you can see from the bank statement, in July the Derbyshire should be saying Paid and the DVLA should be saying Unpaid.

Any help would be appreciated !

Thanks

Karlin

I am having an issue with Index, Match, Match - it works when one of the references is 7, but when I change it to 8 the formula doesn't work and throws me an error.

What I am trying to achieve is in columns L - Q to show whether a bill has been paid based on the info below:

Looking at the bank statement

comparing it to the months in E1-J1 and L1-Q12

comparing it to the expected spend (E-J)

if the amount in the bank account for that period and spend is the same as the expected amount, show as paid, otherwise show as unpaid

However, I just can't get it to work properly for all months, as you can see from the bank statement, in July the Derbyshire should be saying Paid and the DVLA should be saying Unpaid.

Any help would be appreciated !

Thanks

Karlin

I have a list of customer invoices (Sheet2), and I want the Max Date and related Comment to show on the Main Sheet (Sheet1). I used the MAX and IF function for the date, but I'm having a problem with the comment. Anyone know the best way to show the last invoice and the comment? Appreciate the help.

Hi,

I have 2 problems, that would appear to be utter-basic, but I cannot find the answer anywhere, so apologies in advance for if this is too simple for this forum.

__Problem 1:__

What i'd like to do is this: copy a formula with a formula.

Example: Let's say there are these cells:

A1: 2

A2: 1

A3: =A1+A2

A4: .......

What i need is a formula that copies the formula in A3. NOT the value.

The reason for not wanting the value but the formula is that I want to substitute something in the formula, therefore i need a formula that copies the formula, so that I can then do a substitute on it.

The reason I do not want to manually copy-paste the formula, is that my data and formulas will change over time. E.g. if A3 becomes '=A1-A2' instead of '=A1+A2' in the future, I want that change to be reflected in the formula in A.

All I can find is the regular '=A3' formula, which copies the value in A3, and guides online for manual copy-pasting. Any help would be much appreciated.

__problem2: __

Problem 2 is a lot like problem one, except I need a formula that does what a manual copy-paste does, wherein it changes the relative references in the formula. So e.g. when I would copy-paste cell A3 to cell A4, it would change the formula to '=A2+A3', because the references are relative and without the $-sign. I need a formula that performs this copy-paste.

Same reasons as above for not manually doing this: the original formula might change, and when it does, i want this other cell to change with it.

As above, any help would be much appreciated.

]]>I have 2 problems, that would appear to be utter-basic, but I cannot find the answer anywhere, so apologies in advance for if this is too simple for this forum.

What i'd like to do is this: copy a formula with a formula.

Example: Let's say there are these cells:

A1: 2

A2: 1

A3: =A1+A2

A4: .......

What i need is a formula that copies the formula in A3. NOT the value.

The reason for not wanting the value but the formula is that I want to substitute something in the formula, therefore i need a formula that copies the formula, so that I can then do a substitute on it.

The reason I do not want to manually copy-paste the formula, is that my data and formulas will change over time. E.g. if A3 becomes '=A1-A2' instead of '=A1+A2' in the future, I want that change to be reflected in the formula in A.

All I can find is the regular '=A3' formula, which copies the value in A3, and guides online for manual copy-pasting. Any help would be much appreciated.

Problem 2 is a lot like problem one, except I need a formula that does what a manual copy-paste does, wherein it changes the relative references in the formula. So e.g. when I would copy-paste cell A3 to cell A4, it would change the formula to '=A2+A3', because the references are relative and without the $-sign. I need a formula that performs this copy-paste.

Same reasons as above for not manually doing this: the original formula might change, and when it does, i want this other cell to change with it.

As above, any help would be much appreciated.

I have references in Col A and values in Col B on sheet1 on workbook "extract account number if Reference and amount the Same.xlsx"

Where the ref and amount are the same as in Col A and D on sheet1 on workbook "Account Numbers.xlsx" , then the account number on the same row in Col G is to be extracted

I have tried to set up a formula to do this, but get #ref!

I have attached my sample data

It would be appreciated if someone could assist me

Where the ref and amount are the same as in Col A and D on sheet1 on workbook "Account Numbers.xlsx" , then the account number on the same row in Col G is to be extracted

I have tried to set up a formula to do this, but get #ref!

Code:

`=INDEX('[Account Numbers.xlsx]Sheet1'!$G:$G,MATCH(A2,'[Account Numbers.xlsx]Sheet1'!$A:$A,0),MATCH(B2,'[Account Numbers.xlsx]Sheet1'!$D:$D,0))`

I have attached my sample data

It would be appreciated if someone could assist me

Good morning,

I'm hoping one of you might be able to assist me with a formula that I can use to associate a name to an ID number selected from a data validation list originating from a worksheet titled "Template"; see attached sample. The problem I'm running into is that there could be multiple similar ID numbers in column A, but different name associations to that ID number in column C. I was hoping that I could use a Vlookup type formula that would associate column A3 down and C3 down, so that if in the drop down validation box on the form page, so that if the ID you picked was the 2nd similiar ID number it would locate the name associated with that 2nd similar ID and not just the 1st name it found associated with that ID number. My formula only locates the 1st name and I can't for the life of me figure a way around it. Additionally, I need the data validation drop down option to stay the same as the form requires it for other associations, etc. I hope this makes sense. Thanks in advance for your time!

I'm hoping one of you might be able to assist me with a formula that I can use to associate a name to an ID number selected from a data validation list originating from a worksheet titled "Template"; see attached sample. The problem I'm running into is that there could be multiple similar ID numbers in column A, but different name associations to that ID number in column C. I was hoping that I could use a Vlookup type formula that would associate column A3 down and C3 down, so that if in the drop down validation box on the form page, so that if the ID you picked was the 2nd similiar ID number it would locate the name associated with that 2nd similar ID and not just the 1st name it found associated with that ID number. My formula only locates the 1st name and I can't for the life of me figure a way around it. Additionally, I need the data validation drop down option to stay the same as the form requires it for other associations, etc. I hope this makes sense. Thanks in advance for your time!

how can l solve the problem in excell example (2x+3y)2y(2y-x)

]]>Hi Team

I'm pulling my hair out a bit.

I'm trying to dynamically assess a table, and return a value from a dynamic row in another table. (see attached images 1 and 2 for reference) (image 3 is current attempt using named range covering the dates in the orange table)

Table Structure

Column C - Cost categories, Tax, Insurance, Groceries, Phone bill etc.

Column G - How often cost occurs, weekly, f/n, monthly etc

Column I - Start date of cost

Column J to BB - Start Date + Col G. So eg column J = 05/07/2020 + 14 days, then column K would take the result and add 14Attachment 690124Attachment 690125 days, etc etc. Giving me a row with all the dates the cost would occur.

What im trying to do is populate a calendar with all these dates.

The calendar Structure

Row 1 Has the Cost categories spread across it

Column A dates in days from 01/06/2020 to 01/06/2021

I need a formula in the calendar sheet that will assess the Category, then the date and check the Cost table to see if that date exists in the row of the corresponding category, if it does, simply return a value.

I have achieved the above, but with named ranges, I'd prefer the whole process to by dynamic if possible as when categories are added in the future there is no need to manually update named ranges.

My attempt is Image 3

Thanks in advance!

NR

1.png

2.png

My attempt - using named ranges

3.png

]]>I'm pulling my hair out a bit.

I'm trying to dynamically assess a table, and return a value from a dynamic row in another table. (see attached images 1 and 2 for reference) (image 3 is current attempt using named range covering the dates in the orange table)

Table Structure

Column C - Cost categories, Tax, Insurance, Groceries, Phone bill etc.

Column G - How often cost occurs, weekly, f/n, monthly etc

Column I - Start date of cost

Column J to BB - Start Date + Col G. So eg column J = 05/07/2020 + 14 days, then column K would take the result and add 14Attachment 690124Attachment 690125 days, etc etc. Giving me a row with all the dates the cost would occur.

What im trying to do is populate a calendar with all these dates.

The calendar Structure

Row 1 Has the Cost categories spread across it

Column A dates in days from 01/06/2020 to 01/06/2021

I need a formula in the calendar sheet that will assess the Category, then the date and check the Cost table to see if that date exists in the row of the corresponding category, if it does, simply return a value.

I have achieved the above, but with named ranges, I'd prefer the whole process to by dynamic if possible as when categories are added in the future there is no need to manually update named ranges.

My attempt is Image 3

Thanks in advance!

NR

1.png

2.png

My attempt - using named ranges

3.png

This VLookup function is driving me a bit nuts. In the attached example all I'm trying to do is pull the hourly rate for each role from the column starting in H53.

I have it working in Row 1 but can't understand why it does since it references H52.

I also can't understand why it then doesn't work in Rows 2-7.

As a reference, the remaining rows use manual calcs and are correct.

The goal is to remove column K.

Thank you.

I have it working in Row 1 but can't understand why it does since it references H52.

I also can't understand why it then doesn't work in Rows 2-7.

As a reference, the remaining rows use manual calcs and are correct.

The goal is to remove column K.

Thank you.

Hi everyone,

is it possible to use a formula in order to go to a new line (like when you press ALT+ENTER) after a specific character?

For instance, in the file I've attached I would like to insert a new line within cell B3 after every comma or question mark. The final result should look like B10.

Thanks in advance!

is it possible to use a formula in order to go to a new line (like when you press ALT+ENTER) after a specific character?

For instance, in the file I've attached I would like to insert a new line within cell B3 after every comma or question mark. The final result should look like B10.

Thanks in advance!

Hi,

I want to get data from my Logbook sheet in CA39 sheet as per the last 6 months, last 5 years and Aircraft wise for both with respect to today, for which I have created 4 different tables. I tried Index formula but was not able to make it for the specific things I am looking for as mentioned above.

thank you,

Jitendra Nor.

I want to get data from my Logbook sheet in CA39 sheet as per the last 6 months, last 5 years and Aircraft wise for both with respect to today, for which I have created 4 different tables. I tried Index formula but was not able to make it for the specific things I am looking for as mentioned above.

thank you,

Jitendra Nor.

I have 4 columns where the data contains 4 values, but the value could in in any column

Then a column that has a value

I normally use countifs, but as the value is different in the column, that does not work

So I tried countif, but then I can't choose the value

I tried it as a bulk range of A:D and couldn't get it to work

I need to return how many of a particular type are in the different value range

I'm using office 2016 however the file sample is earlier version

Thanks heaps

Then a column that has a value

I normally use countifs, but as the value is different in the column, that does not work

So I tried countif, but then I can't choose the value

I tried it as a bulk range of A:D and couldn't get it to work

I need to return how many of a particular type are in the different value range

I'm using office 2016 however the file sample is earlier version

Thanks heaps

Hello! Is there a way for me to use the sheet name within the formula on Data Validation function? I tried the INDIRECT function but it is not working. Hope you can help me! Thank you!

Hello, I have big accounting Database, which has many accounts and subaccounts, I need to select only last smallest subaccounts, and for that purpose I have some formula, but In some cases It does'n work, Please help solving this problem...

in attached workbook sheet1!D4 is a simple choose formula that references A11:A15. I shift A11:A15 down 1 row. sheet2 is the results after adding a number in A11. I need the formula in sheet1!D4 to still reference A11:A15.

My real project does similar modification via VBA.

unable to prevent excel from changing the reference.

My real project does similar modification via VBA.

unable to prevent excel from changing the reference.