excell help (1 Viewer)

[Herc]

It has been a long time since i have messed with excell and my employer wants me to pull data from 7 pages of a spreadsheet, each page is different, and tabulate it on the last page.

I know most of the commands, but I cant seem to recall how to use vlookup to pull the information. Or is there a better way to do this?

Example: column A always has the name of the vehicle/equipment, but it may not be in the same row so I need to find the vehicle/equipment and then in column F is the hours used that day and add them for the week. I realize I need to use =page!+page1!+page2!

This isn't difficult, but for the life of me I cant remember and office help just doesn't work as intended. Please explain this to me like I am shuke

Any help is greatly appreciated.

 

[PatsWillWin]

Not an expert, but wouldn't it be:

=vlookup(A,B,V,FALSE)

A = name of the vehicle

B = where the hours are (you'd just highlight the column)

V = number of column in the area that you want the data pulled from - if you only highlighted one column for B, you'd just write 1

FALSE = the word false

 

[Herc]

I will try that in the morning. I was setting up a table array where you have B ex A40:F60, where you have V I was using 6 for the column and false, but it just wouldn't pull the data. I am convinced it has to be some type of syntax error.

it looked something like this

=page!vlookup(ford f-150,A40:F60,6,false)+page1!vlookup(ford f-150,A40:F60,6,false)

edit:does it matter if columns b thru e are empty??

 

[Brony]

the page! has to be within the VLOOKUP formula, not on the outside. In your example:

=VLOOKUP(ford f-150,'page!'$A$40:$F$60,6,false)+vlookup(ford f-150,page1!$A$40:$F$60,6,false)

when you are entering the VLOOKUP formula, there usually is an option to individually add each component of the formula. When you get to the lookup range (the 2nd component), use your mouse to select the range on the other tabs.

 

[Guest]

Excel. Ditch an l.

 

[PatsWillWin]

You could also use a pivot table with adding up the sum of all of the values.

 

[Herc]

the page! has to be within the VLOOKUP formula, not on the outside. In your example:

=VLOOKUP(ford f-150,'page!'$A$40:$F$60,6,false)+vlookup(ford f-150,page1!$A$40:$F$60,6,false)

when you are entering the VLOOKUP formula, there usually is an option to individually add each component of the formula. When you get to the lookup range (the 2nd component), use your mouse to select the range on the other tabs.

Thanks! I thought it was something simple I was overlooking.

 

[Herc]

Excel. Ditch an l.

Sorry fat heavy fingers

 

[gmbacm]

 

[acarey50]

Is each page of the spreadsheet the same layout - ie the columns are all the same, etc?

If so, you could just combine them all into a single sheet and do a Pivot Table off of that. Much easier than the way you are going about it with the VLookup.

 

[Herc]

Back with another question I just can't seem to figure out.

=if(isna(vlookup("rental #1",'daily report wed'!$A53:$G63,7,false)),0,vlookup("rental #1",'daily report wed'!$A53:G63,7,false))

If rental #1changes i just want to search for whatever is in that cell. i tried to just use the cell $A$73 in place of rental #1. Without quotes it returns a 0, and with quotes it also returns a 0. I realize I am forcing a zero if no value. Any help with syntax to get this to work, please.

 

[Brony]

Back with another question I just can't seem to figure out.

=if(isna(vlookup("rental #1",'daily report wed'!$A53:$G63,7,false)),0,vlookup("rental #1",'daily report wed'!$A53:G63,7,false))

If rental #1changes i just want to search for whatever is in that cell. i tried to just use the cell $A$73 in place of rental #1. Without quotes it returns a 0, and with quotes it also returns a 0. I realize I am forcing a zero if no value. Any help with syntax to get this to work, please.

Do the VLOOKUP formula without the ISNA to figure out what is going on. I'm pretty sure that you are getting an error. When you specify in the VLOOKUP for an exact match as you did with the FALSE in there, sometimes something very minor can throw if off. I'd try:

=vlookup($A$73,'daily report wed'!$A53:$G63,7,false)

and see what you get there. If you are getting #N/A then try changing the last variable in VLOOKUP formula to TRUE. Once this thing is working, I'd use the IFERROR formula to be less confusing:

=IFERROR(vlookup($A$73,'daily report wed'!$A53:$G63,7,false),0)

 

[Spin]

Back with another question I just can't seem to figure out.

=if(isna(vlookup("rental #1",'daily report wed'!$A53:$G63,7,false)),0,vlookup("rental #1",'daily report wed'!$A53:G63,7,false))

If rental #1changes i just want to search for whatever is in that cell. i tried to just use the cell $A$73 in place of rental #1. Without quotes it returns a 0, and with quotes it also returns a 0. I realize I am forcing a zero if no value. Any help with syntax to get this to work, please.

It's not finding "Rental #1" in the array you're specifying, so you're always getting "True" to the "Isna" portion. Your set value for True is 0, so you're always getting 0.

You need to figure out why the vlookup is returning NA, (happens when your lookup value is not found in the first column of your table array.

Either the values are not matching exactly (you used false in your vlookup, so you're looking for exact matches, could be a formatting issue), it's not there at all or it's just not in the very first column of the table array.

What result is returned if you do JUST the vlookup? I'm guessing #N/A.

Also, use try using IFERROR instead of ISNA inside of an If statement. ISERROR("Vlookup statement",0)

 

[Herc]

I can try that, but the formula works fine with rental #1, but your formula is so much easier wish i hadn't done 200 lines my way.:-(

 

[Herc]

Vlookup is pulling info with my format, i was just trying to simply things for the formen in the field. i just wanted to eliminate a step for them. For the record i am an excel hack I am just trying to do this since my boss asked me to.

 

[Herc]

Back with another question I just can't seem to figure out.

=if(isna(vlookup("rental #1",'daily report wed'!$A53:$G63,7,false)),0,vlookup("rental #1",'daily report wed'!$A53:G63,7,false))

If rental #1changes i just want to search for whatever is in that cell. i tried to just use the cell $A$73 in place of rental #1. Without quotes it returns a 0, and with quotes it also returns a 0. I realize I am forcing a zero if no value. Any help with syntax to get this to work, please.

It's not finding "Rental #1" in the array you're specifying, so you're always getting "True" to the "Isna" portion. Your set value for True is 0, so you're always getting 0.

You need to figure out why the vlookup is returning NA, (happens when your lookup value is not found in the first column of your table array.

Either the values are not matching exactly (you used false in your vlookup, so you're looking for exact matches, could be a formatting issue), it's not there at all or it's just not in the very first column of the table array.

What result is returned if you do JUST the vlookup? I'm guessing #N/A.

Also, use try using IFERROR instead of ISNA inside of an If statement. ISERROR("Vlookup statement",0)

I was getting an #n/a when there was no input to find, which is why i went with an isna statement. I am adding 7 days so a #n/a broke the formula.

 

[Herc]

It helps if I had info in the cell I was trying to pull. Thanks for the iferror that should simplify things.

 

[Brony]

It helps if I had info in the cell I was trying to pull. Thanks for the iferror that should simplify things.

nice.

 

[Spin]

It helps if I had info in the cell I was trying to pull. Thanks for the iferror that should simplify things.

Definitely helps!

One more note, in your above formula you had an absolute reference '$' in the first instance of the vlookup and not in the second. This would create different arrays when dragging across cells. Just be careful as you may be pulling unintended information. In your instance it should have only mattered when dragging left, as adding more columns to the end of the table (dragging to the right) won't matter as you're pulling from column 7.

But if you're dragging to the left, it would create a very frustrating issue, as the 2nd instance no longer has a column 7.

 

[Herc]

It helps if I had info in the cell I was trying to pull. Thanks for the iferror that should simplify things.

Definitely helps!

One more note, in your above formula you had an absolute reference '$' in the first instance of the vlookup and not in the second. This would create different arrays when dragging across cells. Just be careful as you may be pulling unintended information. In your instance it should have only mattered when dragging left, as adding more columns to the end of the table (dragging to the right) won't matter as you're pulling from column 7.

But if you're dragging to the left, it would create a very frustrating issue, as the 2nd instance no longer has a column 7.

Thanks that was just a typo. I am in the field and the laptop has no net so i was using my tablet to post. Looks like it currently works. Thanks for the help!

 

[Herc]

Well my target has moved again. I am pulling info from 7 tables and putting it on 1 page.

Example:

ColumnA equipment

ColumnB billing code

ColumnF hours

ColumnG rental hours

This is all tabulated on a single report page

Column A equip

Column B Wed code

column C wed hours

Column D Thur code

etc

rental hours has its own table and are not needed for this data

The billing code might be used multiple times in each row and/or column. Obviously vlookup wont work and the billing codes vary from job to job. I thought about index match, but don't think it wil work. The equipment all has different billing rates and all the payroll person cares about is the weekly cost for each code. I can make the sheet calculate the rate for each piece of equipment, but how can i find each billing code to total it when the code is not unique?

I am not asking for code but how to approach it. Not that I would turn down code

 

[Brony]

Well my target has moved again. I am pulling info from 7 tables and putting it on 1 page.

Example:

ColumnA equipment

ColumnB billing code

ColumnF hours

ColumnG rental hours

This is all tabulated on a single report page

Column A equip

Column B Wed code

column C wed hours

Column D Thur code

etc

rental hours has its own table and are not needed for this data

The billing code might be used multiple times in each row and/or column. Obviously vlookup wont work and the billing codes vary from job to job. I thought about index match, but don't think it wil work. The equipment all has different billing rates and all the payroll person cares about is the weekly cost for each code. I can make the sheet calculate the rate for each piece of equipment, but how can i find each billing code to total it when the code is not unique?

I am not asking for code but how to approach it. Not that I would turn down code

Seems like SUMIF or a pivot table would work for this.

 

[Ned]

Well my target has moved again. I am pulling info from 7 tables and putting it on 1 page.

Example:

ColumnA equipment

ColumnB billing code

ColumnF hours

ColumnG rental hours

This is all tabulated on a single report page

Column A equip

Column B Wed code

column C wed hours

Column D Thur code

etc

rental hours has its own table and are not needed for this data

The billing code might be used multiple times in each row and/or column. Obviously vlookup wont work and the billing codes vary from job to job. I thought about index match, but don't think it wil work. The equipment all has different billing rates and all the payroll person cares about is the weekly cost for each code. I can make the sheet calculate the rate for each piece of equipment, but how can i find each billing code to total it when the code is not unique?

I am not asking for code but how to approach it. Not that I would turn down code

pivot table would work for this.

Excel has already built the perfect tool for the job - don't recreate the wheel.

 

[Ignoramus]

Well my target has moved again. I am pulling info from 7 tables and putting it on 1 page.

Example:

ColumnA equipment

ColumnB billing code

ColumnF hours

ColumnG rental hours

This is all tabulated on a single report page

Column A equip

Column B Wed code

column C wed hours

Column D Thur code

etc

rental hours has its own table and are not needed for this data

The billing code might be used multiple times in each row and/or column. Obviously vlookup wont work and the billing codes vary from job to job. I thought about index match, but don't think it wil work. The equipment all has different billing rates and all the payroll person cares about is the weekly cost for each code. I can make the sheet calculate the rate for each piece of equipment, but how can i find each billing code to total it when the code is not unique?

I am not asking for code but how to approach it. Not that I would turn down code

pivot table would work for this.

Excel has already built the perfect tool for the job - don't recreate the wheel.

This tells you how to create pivot tables from multiple data sources

 

[Ignoramus]

Excel. Ditch an l.

Sorry fat heavy fingers

For the love of god man, fix it!

 

[Herc]

Excel. Ditch an l.

Sorry fat heavy fingers

For the love of god man, fix it!

Easier to search for with it mispelled.

 

[Herc]

Well my target has moved again. I am pulling info from 7 tables and putting it on 1 page.

Example:

ColumnA equipment

ColumnB billing code

ColumnF hours

ColumnG rental hours

This is all tabulated on a single report page

Column A equip

Column B Wed code

column C wed hours

Column D Thur code

etc

rental hours has its own table and are not needed for this data

The billing code might be used multiple times in each row and/or column. Obviously vlookup wont work and the billing codes vary from job to job. I thought about index match, but don't think it wil work. The equipment all has different billing rates and all the payroll person cares about is the weekly cost for each code. I can make the sheet calculate the rate for each piece of equipment, but how can i find each billing code to total it when the code is not unique?

I am not asking for code but how to approach it. Not that I would turn down code

pivot table would work for this.

Excel has already built the perfect tool for the job - don't recreate the wheel.

This tells you how to create pivot tables from multiple data sources

Thanks I'll read up on this

 

[Herc]

I think I in over my head.

I have the pivot table,but when I put the billing codes into columns the table moves the weekly total to that column not the daily total. What am I doing wrong?

It also stacks all the billing codes in 1 column then breaks them into separate columns but doesn't combine the same codes and each column has the weekly total. Hopefully that makes sense. Any tips?

 

[Jayrok]

Excel help request: I have a column with multiple text that I'd like to remove except for one character string.

Example:

G1 cell = OSVGA #5544,MSBV #MS223-023, CARD #2014-A-0034

G2 cell = OSVGA #6354,STOP #MS123-654, RAIN #2011-B-1076, OSDGB #546-098

and so forth in all cells of column G

I'd like to remove all text/characters except for the highlighted above. Maybe the TRIM function, but I don't need to keep any characters except for the 2014-A-0034 (and so on for each cell/row). Sometimes the XXXX-X-XXXX is in the middle of the text string, as in example G2 above, and sometimes it is at the end as in example G1.

The character string will always be "XXXX-X-XXXX" and it will be the only string in this format in any given cell, but this number is different for each row in the spreadsheet/workbook.

Is there a wildcard I can use with TRIM that removes all characters (before and after the target string) except for this particular string of characters in the cell (i.e. XXXX-X-XXXX)?

 

[Ned]

Excel help request: I have a column with multiple text that I'd like to remove except for one character string.

Example:

G1 cell = OSVGA #5544,MSBV #MS223-023, CARD #2014-A-0034

G2 cell = OSVGA #6354,STOP #MS123-654, RAIN #2011-B-1076, OSDGB #546-098

and so forth in all cells of column G

I'd like to remove all text/characters except for the highlighted above. Maybe the TRIM function, but I don't need to keep any characters except for the 2014-A-0034 (and so on for each cell/row). Sometimes the XXXX-X-XXXX is in the middle of the text string, as in example G2 above, and sometimes it is at the end as in example G1.

The character string will always be "XXXX-X-XXXX" and it will be the only string in this format in any given cell, but this number is different for each row in the spreadsheet/workbook.

Is there a wildcard I can use with TRIM that removes all characters (before and after the target string) except for this particular string of characters in the cell (i.e. XXXX-X-XXXX)?

=MID(G1,SEARCH("-?-",G1)-4,11)

That's assuming your data will never have a single char in between two dashes other than the your XXXX-X-XXXX string.

 

[Jayrok]

Excel help request: I have a column with multiple text that I'd like to remove except for one character string.

Example:

G1 cell = OSVGA #5544,MSBV #MS223-023, CARD #2014-A-0034

G2 cell = OSVGA #6354,STOP #MS123-654, RAIN #2011-B-1076, OSDGB #546-098

and so forth in all cells of column G

I'd like to remove all text/characters except for the highlighted above. Maybe the TRIM function, but I don't need to keep any characters except for the 2014-A-0034 (and so on for each cell/row). Sometimes the XXXX-X-XXXX is in the middle of the text string, as in example G2 above, and sometimes it is at the end as in example G1.

The character string will always be "XXXX-X-XXXX" and it will be the only string in this format in any given cell, but this number is different for each row in the spreadsheet/workbook.

Is there a wildcard I can use with TRIM that removes all characters (before and after the target string) except for this particular string of characters in the cell (i.e. XXXX-X-XXXX)?

=MID(G1,SEARCH("-?-",G1)-4,11)

That's assuming your data will never have a single char in between two dashes other than the your XXXX-X-XXXX string.

This works for the row G1, but what about all the rows in G? Could I highlight the column then insert the function?

What I need is to run a function on the column and have the results (from the example above) be:

G1 -- 2014-A-0034

G2 -- 2011-B-1076

and so on for each row.

 

[Jayrok]

That's assuming your data will never have a single char in between two dashes other than the your XXXX-X-XXXX string.

There will always be a single character (either an "A" or a "B") between the two dashes in every row cell. No other character will be there.

 

[Ignoramus]

Excel help request: I have a column with multiple text that I'd like to remove except for one character string.

Example:

G1 cell = OSVGA #5544,MSBV #MS223-023, CARD #2014-A-0034

G2 cell = OSVGA #6354,STOP #MS123-654, RAIN #2011-B-1076, OSDGB #546-098

and so forth in all cells of column G

I'd like to remove all text/characters except for the highlighted above. Maybe the TRIM function, but I don't need to keep any characters except for the 2014-A-0034 (and so on for each cell/row). Sometimes the XXXX-X-XXXX is in the middle of the text string, as in example G2 above, and sometimes it is at the end as in example G1.

The character string will always be "XXXX-X-XXXX" and it will be the only string in this format in any given cell, but this number is different for each row in the spreadsheet/workbook.

Is there a wildcard I can use with TRIM that removes all characters (before and after the target string) except for this particular string of characters in the cell (i.e. XXXX-X-XXXX)?

=MID(G1,SEARCH("-?-",G1)-4,11)That's assuming your data will never have a single char in between two dashes other than the your XXXX-X-XXXX string.

This works for the row G1, but what about all the rows in G? Could I highlight the column then insert the function?

What I need is to run a function on the column and have the results (from the example above) be:

G1 -- 2014-A-0034

G2 -- 2011-B-1076

and so on for each row.

Type the formula in G1. Copy G1 and paste all the way down as far as you need it. Excel is smart enough to update the references.

 

[tri-man 47]

That's assuming your data will never have a single char in between two dashes other than the your XXXX-X-XXXX string.

There will always be a single character (either an "A" or a "B") between the two dashes in every row cell. No other character will be there.

If everything to the left of what you need is consistent, you could probably use the length (LEN) function. Or, given the separations, you could use LEFT and/or MID functions to isolate the piece you need.

 

[Ned]

Excel help request: I have a column with multiple text that I'd like to remove except for one character string.

Example:

G1 cell = OSVGA #5544,MSBV #MS223-023, CARD #2014-A-0034

G2 cell = OSVGA #6354,STOP #MS123-654, RAIN #2011-B-1076, OSDGB #546-098

and so forth in all cells of column G

I'd like to remove all text/characters except for the highlighted above. Maybe the TRIM function, but I don't need to keep any characters except for the 2014-A-0034 (and so on for each cell/row). Sometimes the XXXX-X-XXXX is in the middle of the text string, as in example G2 above, and sometimes it is at the end as in example G1.

The character string will always be "XXXX-X-XXXX" and it will be the only string in this format in any given cell, but this number is different for each row in the spreadsheet/workbook.

Is there a wildcard I can use with TRIM that removes all characters (before and after the target string) except for this particular string of characters in the cell (i.e. XXXX-X-XXXX)?

=MID(G1,SEARCH("-?-",G1)-4,11)That's assuming your data will never have a single char in between two dashes other than the your XXXX-X-XXXX string.

This works for the row G1, but what about all the rows in G? Could I highlight the column then insert the function?

What I need is to run a function on the column and have the results (from the example above) be:

G1 -- 2014-A-0034

G2 -- 2011-B-1076

and so on for each row.

Type the formula in G1. Copy G1 and paste all the way down as far as you need it. Excel is smart enough to update the references.

Sorry, I assumed you knew that excel would do this part for you. Another way is to double click the tiny little box that shows up in the lower right hand corner of cell G1 (when selected). Excel will autofill that formula until it finds a break in the data (consecutive rows).

 

[Jayrok]

When I type the formula =MID(G1,SEARCH("-?-",G1)-4,11) into cell G1 it says it is circular and just puts a 0 (zero) in the cell.

 

[Ned]

When I type the formula =MID(G1,SEARCH("-?-",G1)-4,11) into cell G1 it says it is circular and just puts a 0 (zero) in the cell.

Try this - https://docs.google.com/spreadsheets/d/1CX39meSEa2uwFAmh_rgn4x5ltS-7zdzYncfSjpT1tL8/edit?usp=sharing

 

[Ignoramus]

When I type the formula =MID(G1,SEARCH("-?-",G1)-4,11) into cell G1 it says it is circular and just puts a 0 (zero) in the cell.

That's because you said your data is in row G1. A formula cannot reference itself.Assuming your data is in row G, you would enter the formula into another row, H for example. Then copy it down.

 

[Jayrok]

Ned,

I can't open that link, but I think I see where I misunderstood you in the beginning. I can paste the formula into a separate cell on the sheet and then drag the formula down the page, as you mentioned above, and have it correspond with the cells in the original column. And this works great.

G1-G56 (for instance) has the original data in each cell. Pasting your formula into H1 extracts the text I need from G1 and puts it into H1. When I drag the formula down column H it grabs all the text that I need from each row in column G. I can then go back and delete column G and use column H for my new data.

This works great. Thanks a million!

 

[Jayrok]

When I type the formula =MID(G1,SEARCH("-?-",G1)-4,11) into cell G1 it says it is circular and just puts a 0 (zero) in the cell.

That's because you said your data is in row G1. A formula cannot reference itself.Assuming your data is in row G, you would enter the formula into another row, H for example. Then copy it down.

Yes. This hit me after looking at it for awhile. I was typing to Ned as you posted this but this is the part I was missing when I originally read Ned's posted formula.

Thanks for your help, guys. I really appreciate it!

 

[Ignoramus]

Ned,

I can't open that link, but I think I see where I misunderstood you in the beginning. I can paste the formula into a separate cell on the sheet and then drag the formula down the page, as you mentioned above, and have it correspond with the cells in the original column. And this works great.

G1-G56 (for instance) has the original data in each cell. Pasting your formula into H1 extracts the text I need from G1 and puts it into H1. When I drag the formula down column H it grabs all the text that I need from each row in column G. I can then go back and delete column G and use column H for my new data.

This works great. Thanks a million!

Hopefully you just omitted a step in your post.

 

[Jayrok]

Ned,

I can't open that link, but I think I see where I misunderstood you in the beginning. I can paste the formula into a separate cell on the sheet and then drag the formula down the page, as you mentioned above, and have it correspond with the cells in the original column. And this works great.

G1-G56 (for instance) has the original data in each cell. Pasting your formula into H1 extracts the text I need from G1 and puts it into H1. When I drag the formula down column H it grabs all the text that I need from each row in column G. I can then go back and delete column G and use column H for my new data.

This works great. Thanks a million!

Hopefully you just omitted a step in your post.

yes, of course. I can't delete column G or the formula is hosed. I can hide it instead.

 

[Ignoramus]

Cool.

 

[Jayrok]

Cool.

Thanks..