Hey All,

I need help with a new formula, I have been using this Data for a while, but I put it in manually and that is getting old.

Im hoping that I explain this correctly so people will understand, I apologize if it's confusing. I will do my best to explain.

The Notes in the attachment should help with understanding my goal for the formula.

Ok, here it is.

I have 3 digit numbers 0-9 that will continue down Column(D) Examples are 432, 287, 165, 526, 580, 613, 631, etc.

These 3 digit numbers I will need to take and extract each individual digit and put in a row, the columns will be E-N and (labeled 0-9 in row 1)

I will need the formula to look at Seven of the 3 digit numbers at a time, it will count each individual digit and put in the row of the columns marked 0-9

Example from numbers above would go in Columns E-N marked 0-9 in row 8.

Note:

Column E is Marked "0"

Column F is Marked "1"

Column G is Marked "2" and so on until Column N which is Marked "9"

Below is how it works:

432, 287, 165, 526, 580, 613, 631

This would be: E8 is 1 because only one "0" is in the seven 3 digit numbers listed above

F8 is 3 because of three "1's" in the seven 3 digit numbers

G8 is 3 because of three "2's" in the seven 3 digit numbers.

and so on until

N9 which is "0" because of no "9's" in the seven 3 digit numbers

If you add the Row 8 Columns E-N together you will get 21 which is the seven 3 digit numbers (7 times 3 = 21)

Once a new 3 digit number arrives after the 631 from numbers above i will need to take away the first number which is (432) from the formula (But leave it in place on my excel sheet)

So the new seven 3 digit numbers will be 287, 165, 526, 580, 613, 631, 355

Row 9 will show just like the above example except leaving the 432 out and adding the 355

This same pattern will continue

I have faith that this forum and the experts will figure this out for me.

I'm learning more and more everyday with formulas in excel. I used excel for years putting in numbers manually not knowing anything about formulas until recently and it has changed my world.

Thanks in advance,

Brian

I need help with a new formula, I have been using this Data for a while, but I put it in manually and that is getting old.

Im hoping that I explain this correctly so people will understand, I apologize if it's confusing. I will do my best to explain.

The Notes in the attachment should help with understanding my goal for the formula.

Ok, here it is.

I have 3 digit numbers 0-9 that will continue down Column(D) Examples are 432, 287, 165, 526, 580, 613, 631, etc.

These 3 digit numbers I will need to take and extract each individual digit and put in a row, the columns will be E-N and (labeled 0-9 in row 1)

I will need the formula to look at Seven of the 3 digit numbers at a time, it will count each individual digit and put in the row of the columns marked 0-9

Example from numbers above would go in Columns E-N marked 0-9 in row 8.

Note:

Column E is Marked "0"

Column F is Marked "1"

Column G is Marked "2" and so on until Column N which is Marked "9"

Below is how it works:

432, 287, 165, 526, 580, 613, 631

This would be: E8 is 1 because only one "0" is in the seven 3 digit numbers listed above

F8 is 3 because of three "1's" in the seven 3 digit numbers

G8 is 3 because of three "2's" in the seven 3 digit numbers.

and so on until

N9 which is "0" because of no "9's" in the seven 3 digit numbers

If you add the Row 8 Columns E-N together you will get 21 which is the seven 3 digit numbers (7 times 3 = 21)

Once a new 3 digit number arrives after the 631 from numbers above i will need to take away the first number which is (432) from the formula (But leave it in place on my excel sheet)

So the new seven 3 digit numbers will be 287, 165, 526, 580, 613, 631, 355

Row 9 will show just like the above example except leaving the 432 out and adding the 355

This same pattern will continue

I have faith that this forum and the experts will figure this out for me.

I'm learning more and more everyday with formulas in excel. I used excel for years putting in numbers manually not knowing anything about formulas until recently and it has changed my world.

Thanks in advance,

Brian

Was not sure how to describe this but this is what I want to do, I am working on a sheet of my bills, I have my total debt in F5, G5 is blank, and from F5 to Z5 I have months, my goal is in each month when I make the payment I will put an X in that box, showing it was paid, did that, no code needed, BUT, what I want is for the debt in G5 to go down by a certain amount, in this case, $25 every time there is an X in all of row 5. This sounds relativley easy but I am new to Excel and am learning. Please help if this is even possible! I know I will have to use IF or COUNTIF, just not sure how to make it, in this example, 809 subtract 26 if X is entered in all of row 5, or even from H5:Z5 hope that helps. :confused::eek::rolleyes::cool:;):)

]]>Hello!

I am trying to figure out how to find the demand on SKU level of components that are sold either by themselves or as components in package products. The components can be part of more than one package. What i have is a sales report telling how many of each components and each package that have been sold. I also have a conversion/BoM table stating which components that are included in the packages.

These two should be used to generate a third table stating the demand on sku level (units of sold components by themselves + number of packages where the component is included * number included in the different packages). I was hoping to be able to generate this without having a predefined list of products in the third table, instead i want it to be generated somehow so that whenever i add anything to the sales report and/or the conversion/BoM table, like adding a sku to a package, it will be updated automatically or whenever i refresh it.

In the example file i added some data in the material demand sheet, but best case scenario would be to only have headings that are filled in automatically.

So i guess it is two questions:

How do i get the total demand on SKU level in the most efficient way?

Can it be automated so i dont have to fill in any data in the third sheet? (i understand there must be something, but was thinking of somekind of formula that checks sheet one, adds all articles and then calculate the total demand).

how would you approach this problem?

Edit:

Added the way i solved it for now, works but needs alot of work everytime i update with new products etc, also all the logical operations makes the file super slow (not the small one in the example)

I am trying to figure out how to find the demand on SKU level of components that are sold either by themselves or as components in package products. The components can be part of more than one package. What i have is a sales report telling how many of each components and each package that have been sold. I also have a conversion/BoM table stating which components that are included in the packages.

These two should be used to generate a third table stating the demand on sku level (units of sold components by themselves + number of packages where the component is included * number included in the different packages). I was hoping to be able to generate this without having a predefined list of products in the third table, instead i want it to be generated somehow so that whenever i add anything to the sales report and/or the conversion/BoM table, like adding a sku to a package, it will be updated automatically or whenever i refresh it.

In the example file i added some data in the material demand sheet, but best case scenario would be to only have headings that are filled in automatically.

So i guess it is two questions:

How do i get the total demand on SKU level in the most efficient way?

Can it be automated so i dont have to fill in any data in the third sheet? (i understand there must be something, but was thinking of somekind of formula that checks sheet one, adds all articles and then calculate the total demand).

how would you approach this problem?

Edit:

Added the way i solved it for now, works but needs alot of work everytime i update with new products etc, also all the logical operations makes the file super slow (not the small one in the example)

Whenever I enter an email address in a cell formatted for email hyperlink, the existing font size defaults to a miniscule size and I always have to change it back to the desired size.

example...the cell shows 'ENTER INFO' in bold - font size is 22. After entering the email address, the font reduces in size and is no longer bold. The cell is formatted for email hyperlink and the ENTER INFO is underlined.

How do I set the cell so font size and bold remain?

]]>example...the cell shows 'ENTER INFO' in bold - font size is 22. After entering the email address, the font reduces in size and is no longer bold. The cell is formatted for email hyperlink and the ENTER INFO is underlined.

How do I set the cell so font size and bold remain?

Thanks for help in advance, this forum is the best

I will add an attachment of what I would like to achieve.

Im lost with what to do with this formula.

I have learned a little about Index Function, but not sure which functions to use with my numbers

Can someone help with this?

I have a column of random 3 digit numbers from 000 to 999. Examples: 326, 459, 846, 342, 267, etc.

I want to return Yes or No in 3 different columns beside the column with the 3 digit numbers.

the complicated part is:

The first column I want the formula to look at the last number in column of 3 digit numbers and see if one of its 3 digit matches the cell right above it.

Example would be the Last 3 digit number is 580, the number right before it is 256, so the formula would return a "Yes" because one of the 3 digit numbers (580) match the other 3 digits in cell right above it (256) the matching digit is 5. It doesn't matter if two or all three numbers match, it would still return "Yes"

Also this would continue for future numbers, always looking at number right above the last 3 digit number for a matching digit and return "Yes" or "No"

The second column formula would be same as first except it would look at the 4th cell above it for a matching number.

Example would be last number in column of 3 digit numbers is 580, the 4th number above it is 432, so the formula would return "No" in the 2nd column

The same for the last column which is looking at the 7th number above the last 3 digit numbers column.

See attachment for examples

I will add an attachment of what I would like to achieve.

Im lost with what to do with this formula.

I have learned a little about Index Function, but not sure which functions to use with my numbers

Can someone help with this?

I have a column of random 3 digit numbers from 000 to 999. Examples: 326, 459, 846, 342, 267, etc.

I want to return Yes or No in 3 different columns beside the column with the 3 digit numbers.

the complicated part is:

The first column I want the formula to look at the last number in column of 3 digit numbers and see if one of its 3 digit matches the cell right above it.

Example would be the Last 3 digit number is 580, the number right before it is 256, so the formula would return a "Yes" because one of the 3 digit numbers (580) match the other 3 digits in cell right above it (256) the matching digit is 5. It doesn't matter if two or all three numbers match, it would still return "Yes"

Also this would continue for future numbers, always looking at number right above the last 3 digit number for a matching digit and return "Yes" or "No"

The second column formula would be same as first except it would look at the 4th cell above it for a matching number.

Example would be last number in column of 3 digit numbers is 580, the 4th number above it is 432, so the formula would return "No" in the 2nd column

The same for the last column which is looking at the 7th number above the last 3 digit numbers column.

See attachment for examples

I have a question in regards to Excel. I have a file with 6 tabs. 4 of the tabs have a code and description of the code. The four tabs are different things, location, type of service, department and store leader. The other two tabs have like invoice amount was and how much was paid on the invoice. What the two tabs have also is some variation of the codes that were in the first two tabs. I have solved the problem using vlookup, but I want to find another method of doing it, using index match to merge the data sets together and sumif if they meet certain criterias. I have never used index match that much, but I have been told it's more powerful than vlookup and doesnt use as much excel space. I will give you an example.

1st tab has

Column A Column B Column C

Invoice # State Code Store Leader code

2nd tab has

Column A Column B Column C Column D Column E Column F

Invoice# # of invoices with the invoice# Type of Service Code Dept Code Invoiced Amt Paid Amt

(Could have multiple invoices with same invoice#)

3rd Tab has

Column A Column B Column C

Type of Service Code Description of Service Service Taxed or Tax Exempt

4th tab has

Column A Column B

Store Leader Code Store Leader Name

5th tab has

'

Column A Column B

Dept Code Dept Name

6th tab has

Column A Column B

State Code Name of State

I want to merge everything into one data set using Index Match instead of Vlookup, but then I have to do a sumif the number of invoiced amount from certain store leaders in the three most populous state, CA, NY and TX who provide a specific service in these three states.

Then I need to sumif tax exempt services that were rendered by store leaders that are not from those 3 states, where taxes were accidentally charged. I am trying to break those down to states, number of invoices and the total paid.

I did this with vlookup and used a pivot table to determine this, but as the information becomes larger that might cause issue with my storage spaced.

]]>1st tab has

Column A Column B Column C

Invoice # State Code Store Leader code

2nd tab has

Column A Column B Column C Column D Column E Column F

Invoice# # of invoices with the invoice# Type of Service Code Dept Code Invoiced Amt Paid Amt

(Could have multiple invoices with same invoice#)

3rd Tab has

Column A Column B Column C

Type of Service Code Description of Service Service Taxed or Tax Exempt

4th tab has

Column A Column B

Store Leader Code Store Leader Name

5th tab has

'

Column A Column B

Dept Code Dept Name

6th tab has

Column A Column B

State Code Name of State

I want to merge everything into one data set using Index Match instead of Vlookup, but then I have to do a sumif the number of invoiced amount from certain store leaders in the three most populous state, CA, NY and TX who provide a specific service in these three states.

Then I need to sumif tax exempt services that were rendered by store leaders that are not from those 3 states, where taxes were accidentally charged. I am trying to break those down to states, number of invoices and the total paid.

I did this with vlookup and used a pivot table to determine this, but as the information becomes larger that might cause issue with my storage spaced.

For a research project I am trying to find out how often several hundred patients have been admitted to various hospital departments, and additional info like the type of discharges etc. For each department I have a spreadsheet covering all sorts of data from the late 90s to today (resulting in tens of thousands of rows in each spreadsheet, for a total of seven spreadsheets). However, for each patient I am only interested in a 2-year period after they have completed a certain treatment. The problem is that this period is *different* for each patient.

I have cleaned up most of the mess, so the first column contains the unique patient ID (in each row, usually covering dozens to several hundred rows), the second column contains the individual admission dates. An additional spreadsheet contains the patient IDs in the first column, the index date in the second, and the end date of the follow-up period in the third column.

Now I need to remove (or filter out) all rows in the spreadsheets that contain admission dates that fall outside the 2-year period. I know how to filter by date range with advanced filters across the whole spreadsheet, but after quite some time on Google and looking at tutorials I am still at a loss as to how to do this for*different individual* index dates for each patient. I have found out that a pivot table is supposed to help, but I don't know how to link such a table to my spreadsheet (or sheet) containing the data on individual follow-up periods, so excel checks the index date for each patient ID to filter out the rows containing dates that fall outside this period.

If anyone could point me towards the right option in Excel, or any resource/tutorial to achieve this, that would be greatly appreciated :)

]]>I have cleaned up most of the mess, so the first column contains the unique patient ID (in each row, usually covering dozens to several hundred rows), the second column contains the individual admission dates. An additional spreadsheet contains the patient IDs in the first column, the index date in the second, and the end date of the follow-up period in the third column.

Now I need to remove (or filter out) all rows in the spreadsheets that contain admission dates that fall outside the 2-year period. I know how to filter by date range with advanced filters across the whole spreadsheet, but after quite some time on Google and looking at tutorials I am still at a loss as to how to do this for

If anyone could point me towards the right option in Excel, or any resource/tutorial to achieve this, that would be greatly appreciated :)

I need to automatically filter my spreadsheet different ways for their assigned party. Is there any way to filter and save those filtered items so I don't have to filter each person's portfolio everyday? Please point me in the right direction.

]]>i have one cell one date 12/05/2017 in another cell it need to come 12/05/2017 + 30 days = 12/06/2017 how to bring that ?

]]>How to change date format dd/mm/yyyy to mm/dd/yyyy ?

Its not showing the result i have tried in format cells

Its not showing the result i have tried in format cells

I have an experience during submit new thread or preview post and Sucuri Firewall block my request.

Sucuri Websitefirewall-access Denied.JPG

Block reason is : Your request was not authorized due to its content (HTML code not allowed).

Temporary solution from my side:

Attach HTML code as image to avoid Sucuri Firewall block the request.(see attach for example)

FB_input_HTML_code.JPG

My question: Is that any other way to keep the HTML code in text(not in image as my temporary solution) and the NOT blocked by Sucuri Firewall?

Regards,

Benny

]]>Sucuri Websitefirewall-access Denied.JPG

Block reason is : Your request was not authorized due to its content (HTML code not allowed).

Temporary solution from my side:

Attach HTML code as image to avoid Sucuri Firewall block the request.(see attach for example)

FB_input_HTML_code.JPG

My question: Is that any other way to keep the HTML code in text(not in image as my temporary solution) and the NOT blocked by Sucuri Firewall?

Regards,

Benny

sorry see down

]]>I need to calculate sheet 1 c2 + sheet2 c2 + sheet 3 c2 the output come to sheet4 c2 how will I calculate it

Hi All,

Sorry if this may be a simple enquiry but i need some help with Excel please! I have attached the file as an example

I have a table/column for example in the first column with a list of countries/cities on Sheet1.

Then on Sheet2 i have a list with a few of those countries/cities on.

I want to be able to have a formula whereby i can lookup the list on sheet2 and populate the cells in a column on sheet1 with that same text (country name or city name) if it corresondingly matches the table/column on sheet1.

In the file attached on sheet1 columns 2 and 3 are examples of what i am looking for.

I hope someone can help.

Thanks in advance.

Sorry if this may be a simple enquiry but i need some help with Excel please! I have attached the file as an example

I have a table/column for example in the first column with a list of countries/cities on Sheet1.

Then on Sheet2 i have a list with a few of those countries/cities on.

I want to be able to have a formula whereby i can lookup the list on sheet2 and populate the cells in a column on sheet1 with that same text (country name or city name) if it corresondingly matches the table/column on sheet1.

In the file attached on sheet1 columns 2 and 3 are examples of what i am looking for.

I hope someone can help.

Thanks in advance.

Hello, I am quite new in this, so the question may be stupid or not understandable.

I am trying to graph a correlation between the color of wine (1 -white, 2 rose and 3 -red) and its antioxidant properties (expressed in numerical values). How can this be done in Excel 2010?

Thanks lots

]]>I am trying to graph a correlation between the color of wine (1 -white, 2 rose and 3 -red) and its antioxidant properties (expressed in numerical values). How can this be done in Excel 2010?

Thanks lots