Hi Super Excelers

I am looking for special a Excel formula, to extract a string out of a cell.

This string (alphanumeric code) comes in two forms, which always starts with the letter S.

It is either**10 or 11** characters long.

Here is an example; SA9010001, SA9010001W, SX0512001, SX0512001L

The cells could contain a sentence. ie. [This code SA9010001 is unavailable.]

THANK YOU

]]>I am looking for special a Excel formula, to extract a string out of a cell.

This string (alphanumeric code) comes in two forms, which always starts with the letter S.

It is either

Here is an example; SA9010001, SA9010001W, SX0512001, SX0512001L

The cells could contain a sentence. ie. [This code SA9010001 is unavailable.]

THANK YOU

Hi,

I have the following formula

=IFERROR(INDEX(Backorders!$G$2:$G$1000,MATCH(D10&F10,Backorders!$B$2:$B$1000&Backorders!$H$2:$H$1000,0)),"")

which returns the "Quantity" based on column B (Job No) and column H (Part Number)

This works great if there is only 1 in the list but if there is 2 it only returns the first entry and not the second

If I enter the first job number and part number my formula returns ok, but I then want to return the same part number on the same job (a 2nd entry) my formula only returns the same 1st entry

is there a way to adapt and be able to return both lines (or more)

hope that makes more sense

Thanks

]]>I have the following formula

=IFERROR(INDEX(Backorders!$G$2:$G$1000,MATCH(D10&F10,Backorders!$B$2:$B$1000&Backorders!$H$2:$H$1000,0)),"")

which returns the "Quantity" based on column B (Job No) and column H (Part Number)

This works great if there is only 1 in the list but if there is 2 it only returns the first entry and not the second

If I enter the first job number and part number my formula returns ok, but I then want to return the same part number on the same job (a 2nd entry) my formula only returns the same 1st entry

is there a way to adapt and be able to return both lines (or more)

hope that makes more sense

Thanks

I was wondering if there is anyone out there that can help me convert a copied text number into a numerical value in excel. I have attached the file if you are interested.

I have tried for a awhile and have not accomplished anything on it.

Thanks,

J

a b c

Amount

−16.00 −16.00 #VALUE! NEEDS TO BE CONVERTED TO A NUMBER

−14.06 −14.06

−64.99 −64.99

−23.60 −23.60

I have tried =-1*a1 result would be #value

I aslo tried 1 in a cell then copy paste special after highlighting column A picking multiply and still nothing.

I tried to upload the file but its not working so here is the mock up of it.

]]>I have tried for a awhile and have not accomplished anything on it.

Thanks,

J

a b c

Amount

−16.00 −16.00 #VALUE! NEEDS TO BE CONVERTED TO A NUMBER

−14.06 −14.06

−64.99 −64.99

−23.60 −23.60

I have tried =-1*a1 result would be #value

I aslo tried 1 in a cell then copy paste special after highlighting column A picking multiply and still nothing.

I tried to upload the file but its not working so here is the mock up of it.

Hello,

Why after these lines does VBA apply the filter to all the rows on my spread sheet when all I ask is to apply the filter to only the A6-A10 range?

All help appreciated

Thanks

RN

]]>Why after these lines does VBA apply the filter to all the rows on my spread sheet when all I ask is to apply the filter to only the A6-A10 range?

Code:

`Range("A6:A10").Select`

Selection.AutoFilter

ActiveSheet.Range("A6:A10").AutoFilter Field:=1, Criteria1:="<>"

All help appreciated

Thanks

RN

Hello

I'm having a bit of a problem writing an IF statement. By that I mean I think I need an IF statement, but Im not sure. I really need some guidance.

I have a spreadsheet which is calculating the price of cheese.

I can set an exchange rate which I can change.

What I need to be able to do is if the price per kilo rises by 10p then I need the Average monthly sales figures to drop by 20 and if the exchange rate drops below 0.7 then the sales increase by a similar amount.

I would be super greatful if sombody could give me some pointers on how to solve this!

I'm having a bit of a problem writing an IF statement. By that I mean I think I need an IF statement, but Im not sure. I really need some guidance.

I have a spreadsheet which is calculating the price of cheese.

I can set an exchange rate which I can change.

What I need to be able to do is if the price per kilo rises by 10p then I need the Average monthly sales figures to drop by 20 and if the exchange rate drops below 0.7 then the sales increase by a similar amount.

I would be super greatful if sombody could give me some pointers on how to solve this!

I want to Track Changes, but I don't want other people to turn it off after I've shared the workbook. Can another user (not me) do this?

]]>Hey,

What I want to do is compare values from column A and B, and select only the unique values that are found in column A. To do this, I select both columns, and conditionally format them to highlight unique values. Once done, all all unique values that I am interested in are highlighted red in column A.

From here, I would either sort, or filter by cell color in column A so that all red cells (the ones I want) would be grouped together and from there I can easily copy and paste them. Unfortunately, I can no longer do this as there is too many rows of data (over 500,000) and both the sort and filter feature take 16+ hours to sort/filter based on color (and I am running a deca-core server w/ 64 gb of ram). So of course, this takes far too long, and I can't even use my computer while this sort is running because even a mouse click makes excel crash at this point.

So, my question is, is there another way to select only these red cells without sorting them? I have tried to select cells based on format, however for whatever reason, formats applied by conditional formatting are not recognized when using the find & select feature, so this doesn't work either? Anyone have a solution? Thanks.

]]>What I want to do is compare values from column A and B, and select only the unique values that are found in column A. To do this, I select both columns, and conditionally format them to highlight unique values. Once done, all all unique values that I am interested in are highlighted red in column A.

From here, I would either sort, or filter by cell color in column A so that all red cells (the ones I want) would be grouped together and from there I can easily copy and paste them. Unfortunately, I can no longer do this as there is too many rows of data (over 500,000) and both the sort and filter feature take 16+ hours to sort/filter based on color (and I am running a deca-core server w/ 64 gb of ram). So of course, this takes far too long, and I can't even use my computer while this sort is running because even a mouse click makes excel crash at this point.

So, my question is, is there another way to select only these red cells without sorting them? I have tried to select cells based on format, however for whatever reason, formats applied by conditional formatting are not recognized when using the find & select feature, so this doesn't work either? Anyone have a solution? Thanks.

I'm getting #NUM! error. How do I need to format my data to fix this? I attached a spreadsheet so that you can see.

The formulas worked fine when I manually typed in the data but the data I'm using came from someone else that I'm trying to use these formulas in.

The formulas worked fine when I manually typed in the data but the data I'm using came from someone else that I'm trying to use these formulas in.

Hello all,

I receive a basic CSV from my time card site for my employees login and logout times.

I have attached an example of what I have so far, Data tab has the export, I created the formulas on that tab to combine/split data and the final data is on Sheet 1.

What I am trying to do is create a new Tab where it shows each agents down column A (2 lines per agent) and dates for the last 30 days (Time card is always last 30 days) across Row 1... effectively creating a calendar that will then pull in their Login time for the day and Logout time for the day.

For example:

Agent 2/22 2/23

Test 06:00 06:01 (Start time of the first line for that date)

Test 14:30 14:35 (End time of the second line for that date)

Test 2 09:00 09:00

Test 2 17:00 17:06

The reason I am wanting to do this is so I can easily just dump the time card data into the data tab so the calendar always updates with the data in that tab or the cleaned tab "Sheet 1" since that pulls from the Data tab.

Once I have the calendar I can then set conditional formatting on the cells for when they are late or early etc.

Thanks for any help you can provide :)

I receive a basic CSV from my time card site for my employees login and logout times.

I have attached an example of what I have so far, Data tab has the export, I created the formulas on that tab to combine/split data and the final data is on Sheet 1.

What I am trying to do is create a new Tab where it shows each agents down column A (2 lines per agent) and dates for the last 30 days (Time card is always last 30 days) across Row 1... effectively creating a calendar that will then pull in their Login time for the day and Logout time for the day.

For example:

Agent 2/22 2/23

Test 06:00 06:01 (Start time of the first line for that date)

Test 14:30 14:35 (End time of the second line for that date)

Test 2 09:00 09:00

Test 2 17:00 17:06

The reason I am wanting to do this is so I can easily just dump the time card data into the data tab so the calendar always updates with the data in that tab or the cleaned tab "Sheet 1" since that pulls from the Data tab.

Once I have the calendar I can then set conditional formatting on the cells for when they are late or early etc.

Thanks for any help you can provide :)

Hello,

I am trying to apply the filter to the rows containing an "X" but only for the range of A6 to A9. So in other words I

would to leave the rest of the rows (A10, A11 ... etc... ) unfiltered. The following command filters all the rows

How can I apply the filter only to the rows in the range of A6 to A9 ???

Thanks

RN

]]>I am trying to apply the filter to the rows containing an "X" but only for the range of A6 to A9. So in other words I

would to leave the rest of the rows (A10, A11 ... etc... ) unfiltered. The following command filters all the rows

Code:

`ActiveSheet.Range("A6:A9").AutoFilter Field:=1, Criteria1:="<>"`

How can I apply the filter only to the rows in the range of A6 to A9 ???

Thanks

RN

Hey there,

I need to create a template for less experienced excel users that will help our managers better manage purchasing. As an end product, I need to calculate weeks of supply, which requires a minimum of 12 weeks of inventory/sales data per item. The idea is that they will download a sales report and an inventory report (separately) every week and drop that in sales and inventory report data dump tabs - the output page will update with as much automation as possible. I'm not sure how to build this out, considering we are bringing in new, unique inventory items on a weekly basis and want to display only those items with a minimum of 12 weeks of historical inventory data, or, 12 unique instances of that item being dropped in the data tab.

So I was hoping to get any and all suggestions on the best way to build out an output tab that spits out only those items that have been in inventory for minimum of 12 weeks, based on someone dropping in new inventory data every week, whether that be in one data dump tab (ideal) or a separate tab for each week.

Again, please note this will be for managers that have very limited experience with excel, so the hope is that they can download inventory, copy, paste and output page displays the updated information (well, as close to that as possible).

Thanks!

what are your thoughts on:

1) create a data dump tab with a column for hardcoded date that corresponds with that weekly inventory data

2) on the output tab either extract through formula/vba or drop in unique values (can train them to just remove duplicates)

3) have a column that counts unique instances of an item appearing in data dump tab

4) have that column set to filter greater than 12

5) pull in all the other data needed for formula through sumifs, index/match, etc (need rolling 12 weeks sales data to pull in - can just use reference dates)

]]>I need to create a template for less experienced excel users that will help our managers better manage purchasing. As an end product, I need to calculate weeks of supply, which requires a minimum of 12 weeks of inventory/sales data per item. The idea is that they will download a sales report and an inventory report (separately) every week and drop that in sales and inventory report data dump tabs - the output page will update with as much automation as possible. I'm not sure how to build this out, considering we are bringing in new, unique inventory items on a weekly basis and want to display only those items with a minimum of 12 weeks of historical inventory data, or, 12 unique instances of that item being dropped in the data tab.

So I was hoping to get any and all suggestions on the best way to build out an output tab that spits out only those items that have been in inventory for minimum of 12 weeks, based on someone dropping in new inventory data every week, whether that be in one data dump tab (ideal) or a separate tab for each week.

Again, please note this will be for managers that have very limited experience with excel, so the hope is that they can download inventory, copy, paste and output page displays the updated information (well, as close to that as possible).

Thanks!

what are your thoughts on:

1) create a data dump tab with a column for hardcoded date that corresponds with that weekly inventory data

2) on the output tab either extract through formula/vba or drop in unique values (can train them to just remove duplicates)

3) have a column that counts unique instances of an item appearing in data dump tab

4) have that column set to filter greater than 12

5) pull in all the other data needed for formula through sumifs, index/match, etc (need rolling 12 weeks sales data to pull in - can just use reference dates)

I am trying to determine what is the best method to calculate a balance due on my spreadsheet.

I have a Pledge field which is (-) and amount received which consists of three columns. So I only want to calculate the pledge balance if the Pledge column contains a number greater than zero. See attached.

I have a Pledge field which is (-) and amount received which consists of three columns. So I only want to calculate the pledge balance if the Pledge column contains a number greater than zero. See attached.

Hello,

I am trying to make a selection sheet for my company. I currently have a drop down list to choose different level front doors that can be installed. I need a second cell in yellow to show in a drop down list the options for the level door that was chosen in the previous cell. Attached are a couple pictures showing what I have done. Any help would be greatly appreciated. I tried to do a vlookup and a drop down list and tried to combine them but haven't been able to get them to work. I would attach my current worksheet but the post is not letting me.

Thanks for all the help. I really appreciate it.

Front Page.JPGOptions.JPG

]]>I am trying to make a selection sheet for my company. I currently have a drop down list to choose different level front doors that can be installed. I need a second cell in yellow to show in a drop down list the options for the level door that was chosen in the previous cell. Attached are a couple pictures showing what I have done. Any help would be greatly appreciated. I tried to do a vlookup and a drop down list and tried to combine them but haven't been able to get them to work. I would attach my current worksheet but the post is not letting me.

Thanks for all the help. I really appreciate it.

Front Page.JPGOptions.JPG

Hi, I have the following formula in BG3 to BG32

=IF(BF3=1,1,IF(BF3=0,"- ",IF((BF3:BF32)>0,0)))

=IF(BF4=1,1,IF(BF4=0,"- ",IF((BF3:BF32)>0,0)))

=IF(BF5=1,1,IF(BF5=0,"- ",IF((BF3:BF32)>0,0)))

... etc

so with nothing in BF3:BF32 I have "-" in cells BG3:BG32 ... which is good

If I have a "1" in any cell between BF3:BF32 I get a "1" in the coresponding cell in col BG .. which is good

But then when I have a "1" anywhere in the range BF3:BF32 i want all the other cells in BG3:BG32 (apart from the one with the "1" in it) to go to zero, this is not happening.

Am I going about this inthe wrong way ? i've tried many similar conatations but to no avail ..

so to clarify ... in the range BG3:BG32 i want either a "_" or a 1 or a zero which is dependent on there being a "1" somewhere in the range BF3:BF32, so for example if there is a "1" in BF8, I want a "1" in BG8 and for all the other cells in the BG range to go from "-" to zero.

Thanks for any help

Paul

]]>=IF(BF3=1,1,IF(BF3=0,"- ",IF((BF3:BF32)>0,0)))

=IF(BF4=1,1,IF(BF4=0,"- ",IF((BF3:BF32)>0,0)))

=IF(BF5=1,1,IF(BF5=0,"- ",IF((BF3:BF32)>0,0)))

... etc

so with nothing in BF3:BF32 I have "-" in cells BG3:BG32 ... which is good

If I have a "1" in any cell between BF3:BF32 I get a "1" in the coresponding cell in col BG .. which is good

But then when I have a "1" anywhere in the range BF3:BF32 i want all the other cells in BG3:BG32 (apart from the one with the "1" in it) to go to zero, this is not happening.

Am I going about this inthe wrong way ? i've tried many similar conatations but to no avail ..

so to clarify ... in the range BG3:BG32 i want either a "_" or a 1 or a zero which is dependent on there being a "1" somewhere in the range BF3:BF32, so for example if there is a "1" in BF8, I want a "1" in BG8 and for all the other cells in the BG range to go from "-" to zero.

Thanks for any help

Paul

Hi, I need a formula to sum an amount based on row and columns perimeters.

I have the month-year in the columns (B3:Z3) and numbers in the rows (B12:B55), so the amounts are obviously in B12:Z55.

Curve ball: I have a range of cells in the destination tab that contain the list of numbers to be summed for that particular category (T26:AA26).

I need a formula that would sum all dollars for Feb 2018 for the numbers in range T26:AA26. So the reference range (T26:AA26) I would have entered the number 2 in T26 and the number 4 in U26. The formula would return 50.

Jan 18 Feb 18 Mar 18 Apr 18

1 40 5

2 30 20

3 10 5 20

4 20 3 2

This is the formula I have right now. The reference numbers I mentioned earlier are in T26:Z26. So the below formula manually adds those together, one by one. I would like to have one formula that will sum based on the numbers in T26:Z26. Something like "*"&T26:Z26&"*"?

(Must press Cntrl+Shift+Enter)

=SUM(IF('Revenue'!$B$12:$B$53=__$T26__, IF('Revenue'!$AA$7:$AQ$7=F$8, 'Revenue'!$AA$12:$AQ$53)))+

SUM(IF('Revenue'!$B$12:$B$53=__$U26__, IF('Revenue'!$AA$7:$AQ$7=F$8, 'Revenue'!$AA$12:$AQ$53)))

]]>I have the month-year in the columns (B3:Z3) and numbers in the rows (B12:B55), so the amounts are obviously in B12:Z55.

Curve ball: I have a range of cells in the destination tab that contain the list of numbers to be summed for that particular category (T26:AA26).

I need a formula that would sum all dollars for Feb 2018 for the numbers in range T26:AA26. So the reference range (T26:AA26) I would have entered the number 2 in T26 and the number 4 in U26. The formula would return 50.

Jan 18 Feb 18 Mar 18 Apr 18

1 40 5

2 30 20

3 10 5 20

4 20 3 2

This is the formula I have right now. The reference numbers I mentioned earlier are in T26:Z26. So the below formula manually adds those together, one by one. I would like to have one formula that will sum based on the numbers in T26:Z26. Something like "*"&T26:Z26&"*"?

(Must press Cntrl+Shift+Enter)

=SUM(IF('Revenue'!$B$12:$B$53=

SUM(IF('Revenue'!$B$12:$B$53=