homeGeek CultureWebstoreeCards!Forums!Joy of Tech!AY2K!webcam

The Geek Culture Forums


Post New Topic  New Poll  Post A Reply
my profile | directory login | | search | faq | forum home
  next oldest topic   next newest topic
» The Geek Culture Forums   » Other Geeky Stuff   » Ask a Geek!   » MS Excel Question - Updating data from one sheet to another

 - UBBFriend: Email this page to someone!    
Author Topic: MS Excel Question - Updating data from one sheet to another
homevestors
Single Celled Newbie
Member # 3029

Rate Member
Icon 1 posted October 24, 2004 15:30      Profile for homevestors     Send New Private Message       Edit/Delete Post   Reply With Quote 
Hello,

I have two spreadsheets saved as seperate files with different names. One is sheet is called parcels.xls. The other is Parcels.Sales.Update.xls.

parcels.xls is a master record of all properties in the county I live in. Parcels.Sales.Update.xls is a record of updated sales data for the properties in parcels.xls.

parcels.xls has a unique PIN (property identifier number) for every property on the sheet. Parcels.Sales.Update.xls lists only PIN's that have recently sold. Both sheets have a column called SALES_YY (last date the proerty sold) and a column called SALE_PRICE.

I want to take all of the PIN's from the Parcels.Sales.Update.xls sheet that match the PIN's in the parcels.xls sheet and update the SALES_YY and SALE_PRICE columns.

Can anyone help me with this? Be easy on me, I am not a speadsheet expert.

Thanks,

Posts: 2 | Registered: Oct 2004  |  IP: Logged
drunkennewfiemidget
BlabberMouth, a Blabber Odyssey
Member # 2814

Member Rated:
4
Icon 1 posted October 25, 2004 07:04      Profile for drunkennewfiemidget     Send New Private Message       Edit/Delete Post   Reply With Quote 
Why does everyone come to this board assuming we're their answer-all for computer problems?
Posts: 4897 | From: Cambridge, ON, Canada | Registered: Jun 2004  |  IP: Logged
Mac D
BlabberMouth, the Next Generation
Member # 2926

Icon 1 posted October 25, 2004 07:08      Profile for Mac D     Send New Private Message       Edit/Delete Post   Reply With Quote 
I usually just point them to the appropriate manual on the internet. "Give a man a fish feed him for a day, Teach a man to fish get the house to yourself every weekend"

--------------------
There's nothing wrong with me, This is how I'm supposed to be.

Posts: 1449 | From: Where I am is very relative to my location at that time. | Registered: Sep 2004  |  IP: Logged
klynn
Mini Geek
Member # 3019

Member Rated:
5
Icon 10 posted October 25, 2004 07:56      Profile for klynn   Author's Homepage     Send New Private Message       Edit/Delete Post   Reply With Quote 
Oh mighty Gods of Geek, lend the mere mortals your favor for but a moment. Bathe them in the light of your brilliant radiance...

Oops, that was kind of blasphemous, wasn't it?

--------------------
Caution: Comments may or may not reflect the actual opinions of the author.

Posts: 62 | From: USA - Saint Albans | Registered: Oct 2004  |  IP: Logged
csk

Member # 1941

Member Rated:
5
Icon 1 posted October 25, 2004 07:59      Profile for csk     Send New Private Message       Edit/Delete Post   Reply With Quote 
quote:
Originally posted by klynn:
Oh mighty Gods of Geek, lend the mere mortals your favor for but a moment. Bathe them in the light of your brilliant radiance...

Oops, that was kind of blasphemous, wasn't it?

I was thinking sarcastic, myself [Wink]

--------------------
6 weeks to go!

Posts: 4455 | From: Sydney, Australia | Registered: Jan 2003  |  IP: Logged
GameMaster
BlabberMouth, a Blabber Odyssey
Member # 1173

Member Rated:
4
Icon 1 posted October 25, 2004 08:04      Profile for GameMaster   Author's Homepage     Send New Private Message       Edit/Delete Post   Reply With Quote 
Perhaps it's the fact that is what the "Ask a Geek!" was intended for:
"Have a question that only a geek could answer? Or are you a knowledgeable geek willing to help poor lost geek-wanna-bes... this is the place!"

I'd suggest RTFM... a quick google got me this:
quote:

Absolute And Relative References In Format Conditions

When you use custom formulas in Conditional Formatting, you need to be aware of the differences between absolute and relative references. If you use CF to apply format conditions to a range of cells, any relative addresses will be translated as Excel adds the format conditions for all the cells. For example, suppose we want to apply format conditions to A1:A10 to display the cell in bold if the value in B1:B10 is greater than 10. We can use the formula =B1>10 to accomplish this. As Excel applies the Conditional Formatting to each cell in A1:A10, it will change the B1 in the formula to the proper cell value. The format condition in A7 will be =B7>10. This is generally what we would want. However, suppose we want to A1:A10 to be bold if the value in B1 was greater than 10. I.e., each cell in A1:A10 is always compared to B1. For this, we would use the formula =$B$1>10, which will not be translated as Conditional Formatting is applied to each cell in A1:A10. The format condition in A7 would remain =$B$1>10.

Array Formulas In Format Conditions

Conditional Formatting evaluates custom formulas as though they were array formula, so you may use array formulas in format conditions. You do not enter them with Ctrl+Shift+Enter in the CF dialog as you normally do in worksheet cells. Excel will always treat a custom formulas in CF as an array formula, even if it is not one.

Using Defined Names In Conditional Formatting

As noted above, custom functions in Conditional Formatting cannot reference cells in other worksheets in the same workbook, and cannot reference cells in other workbooks. However, you can get around this limitation by using defined names. Create a defined name which refers to the list in the other workbook or worksheet, and then use that name in your custom function.

For example, suppose you want to make cell A1 on Sheet1 red if that cell's entry is not found on a list on Sheet2, cells B1:B10. If you tried to use the formula =COUNTIF(Sheet2!$B$1:$B$10,A1)=0 as your formula, you would receive an error message from Conditional Formatting. To get around this error, create a defined name called MyList which refers to the range =Sheet2!$B$1:$B$10 and use the name in your custom formula:

=COUNTIF(MyList,A1)=0

from: http://www.cpearson.com/excel/cformatting.htm
Posts: 3038 | From: State of insanity | Registered: Mar 2002  |  IP: Logged
klynn
Mini Geek
Member # 3019

Member Rated:
5
Icon 14 posted October 25, 2004 08:08      Profile for klynn   Author's Homepage     Send New Private Message       Edit/Delete Post   Reply With Quote 
Try the following link. You may be able to find what you are looking for and then some.

Excel Forum

--------------------
Caution: Comments may or may not reflect the actual opinions of the author.

Posts: 62 | From: USA - Saint Albans | Registered: Oct 2004  |  IP: Logged
drunkennewfiemidget
BlabberMouth, a Blabber Odyssey
Member # 2814

Member Rated:
4
Icon 1 posted October 25, 2004 08:29      Profile for drunkennewfiemidget     Send New Private Message       Edit/Delete Post   Reply With Quote 
quote:
Originally posted by GameMaster:
Perhaps it's the fact that is what the "Ask a Geek!" was intended for:
"Have a question that only a geek could answer? Or are you a knowledgeable geek willing to help poor lost geek-wanna-bes... this is the place!"

I'd suggest RTFM... a quick google got me this:

I can appreciate that, but when you damned well know that that post count isn't going to exceed '1' because they're here to ask their question, get it answered, and leave, I can't help but be moderately annoyed.
Posts: 4897 | From: Cambridge, ON, Canada | Registered: Jun 2004  |  IP: Logged
GameMaster
BlabberMouth, a Blabber Odyssey
Member # 1173

Member Rated:
4
Icon 1 posted October 25, 2004 08:35      Profile for GameMaster   Author's Homepage     Send New Private Message       Edit/Delete Post   Reply With Quote 
quote:
Originally posted by drunkennewfiemidget:
quote:
Originally posted by GameMaster:
Perhaps it's the fact that is what the "Ask a Geek!" was intended for:
"Have a question that only a geek could answer? Or are you a knowledgeable geek willing to help poor lost geek-wanna-bes... this is the place!"

I'd suggest RTFM... a quick google got me this:

I can appreciate that, but when you damned well know that that post count isn't going to exceed '1' because they're here to ask their question, get it answered, and leave, I can't help but be moderately annoyed.
I can think of a few people who started out comming to ask a couple of questions and got hooked. My first post was a personal question in the love forums here... It would be nice if people stayed a while, but if we geeks can help a few laypeople along the way -- all the better.

If this place turns into nothing but a helpdesk; then I may start to get a little annoyed as well... But so far it hasn't been too bad.

--------------------
My Site

Posts: 3038 | From: State of insanity | Registered: Mar 2002  |  IP: Logged
homevestors
Single Celled Newbie
Member # 3029

Rate Member
Icon 1 posted October 25, 2004 08:35      Profile for homevestors     Send New Private Message       Edit/Delete Post   Reply With Quote 
quote:
Originally posted by drunkennewfiemidget:
Why does everyone come to this board assuming we're their answer-all for computer problems?

Please accept my apologies. I am having a very difficult time with this Excel Problem. I read other posts on this site answering simliar questions. I did not intend to offend, nor did I expect to be berated for asking a question.
Posts: 2 | Registered: Oct 2004  |  IP: Logged
ooby
Highlie
Member # 2603

Member Rated:
4
Icon 1 posted October 25, 2004 09:07      Profile for ooby     Send New Private Message       Edit/Delete Post   Reply With Quote 
why don't you use two sheets in the same workbook?

--------------------
"haven't you ever wondered if there's more to life than being really, really, rediculously good looking?"

Posts: 680 | From: South Jersey | Registered: Feb 2004  |  IP: Logged
klynn
Mini Geek
Member # 3019

Member Rated:
5
Icon 1 posted October 25, 2004 09:37      Profile for klynn   Author's Homepage     Send New Private Message       Edit/Delete Post   Reply With Quote 
quote:
Originally posted by ooby:
why don't you use two sheets in the same workbook?

I really don't have much Excel Kung-Fu to speak of, but ooby's suggestion seems logical. It would be easier copy and paste special (?) links between cells in spreadsheets contained within the same Book.

Of course, it's possible this tadpole is off base. [Confused] So, see link to the Excel forum in my previous post. [Wink]

--------------------
Caution: Comments may or may not reflect the actual opinions of the author.

Posts: 62 | From: USA - Saint Albans | Registered: Oct 2004  |  IP: Logged
GameMaster
BlabberMouth, a Blabber Odyssey
Member # 1173

Member Rated:
4
Icon 1 posted October 25, 2004 16:25      Profile for GameMaster   Author's Homepage     Send New Private Message       Edit/Delete Post   Reply With Quote 
Mustn't have read the first post very well, I thought she was trying to link different sheets not different books. If you've got a project that sheets alone won't solve then it is probably time to move to a database... This probably isn't the case -- and you'd be best using different sheets.

Seems I rember reading that MS's BD engine supports using Excel files as databases... If I HAD to refrence cross files, I'd probably write some SQL and figure out how to work with Excel files through the ODBC.

Another google gives me this too:
http://www.excelforum.com/archive/index.php/t-182310.html

Posts: 3038 | From: State of insanity | Registered: Mar 2002  |  IP: Logged


All times are Eastern Time  
Post New Topic  New Poll  Post A Reply Close Topic    Move Topic    Delete Topic next oldest topic   next newest topic
 - Printer-friendly view of this topic
Hop To:

Contact Us | Geek Culture Home Page

2015 Geek Culture

Powered by Infopop Corporation
UBB.classicTM 6.4.0



homeGeek CultureWebstoreeCards!Forums!Joy of Tech!AY2K!webcam