How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1302
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

I want to reference the same cell over multiple sheets. When

Resolved Question:

I want to reference the same cell over multiple sheets. When I place ='#1'!$V2, it not only holds the column, but also the row # XXXXX the sheet. Why??? Help!
Submitted: 10 months ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 10 months ago.

The-PC-Guy :

Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question

The-PC-Guy :

do you want the row to change when you copy the formula or not?

The-PC-Guy :

are you here?

The-PC-Guy :

do you want the row to change when you copy the formula or not?

The-PC-Guy :

i wasn't sure from your initial question

Customer:

That would be ideal

The-PC-Guy :

so which is it

The-PC-Guy :

'#1'

The-PC-Guy :

is the sheet name

The-PC-Guy :

and you want to refer to cell V2? regardless of where the formula is

The-PC-Guy :

or do you just want the V column?

Customer:

I have data in another sheet organized by column (V2,V3,V4) that I would like to copy into a row on another sheet

Customer:

one minute....let me try to explain

Customer:

I have 97 sheets of data

Customer:

I'm trying to compile the column V from all of those 97 sheets into a summary table

Customer:

Column V can have up to 5 rows (V2,V3,V4,V5)

Customer:

V6

Customer:

And I'm trying to copy them into columns in the summary table

Customer:

So I'm trying to get row 2 in my summary to do the following:

Customer:

Row #1 ='#1'!$V2 (next cell) = '#1'!$V3 (next cell) '#1'!$V4

Customer:

Row #2 ='#2'!$V2 (next cell) = '#2'!$V3 (next cell) '#2'!$V4

Customer:

I've tried writing out each formula across the top for all 5 V cell references with $V$2, $V$3, etc and just filling down but the autocomplete messes it all up

Customer:

I land up with column progression to W in some rows if I fill sideways

Customer:

And the worksheet reference stays constant at '#1' if I fill down

Customer:

It doesn't make sense....wondering if this data set being built on an earlier version of MS office is causing problems because some cells fill properly and others do not

The-PC-Guy :

you may need a macro to do this

Customer:

I've never done one

The-PC-Guy :

i could do one for you

Customer:

please

The-PC-Guy :

will the cells be the same in each sheet

The-PC-Guy :

so it will always be V2

The-PC-Guy :

V3

The-PC-Guy :

ext..

Customer:

'yes

The-PC-Guy :

the easiest way to do this would be if you could send me your worksheet

The-PC-Guy :

do you need instructions for that

Customer:

sure

Customer:

email?

The-PC-Guy :

do you have a paperclip icon at the top of your chat window

The-PC-Guy :

where you type text?

The-PC-Guy :

sys image

The-PC-Guy :

but works on xls files

The-PC-Guy :

if you can get me the file it shouldn't take too long to do as I have written similar code for others

Customer:

did that work?

The-PC-Guy :

looks like it

The-PC-Guy :

so which sheet is the output supposed to go on?

Customer:

Trying to take the values in worksheets #1 through 94 in column V and place them in columns G-K in the Total WSL charts worksheet

Customer:

also want to transfer column W to columns L-P

The-PC-Guy :

ok i got the first part

The-PC-Guy :

colimn W from the # XXXXX

The-PC-Guy :

is blank

The-PC-Guy :

did you see that

The-PC-Guy :

i can do the first part no problem

The-PC-Guy :

but not sure what you mean colum @

The-PC-Guy :

column W

Customer:

ok I may not have sent the most recent version as I just added column W today

Customer:

one se

Customer:

sec

The-PC-Guy :

what version of excel are you using by the way?

Customer:

10

The-PC-Guy :

2010

The-PC-Guy :

?

The-PC-Guy :

wanna make sure macros will work

Customer:

yes

The-PC-Guy :

okay

The-PC-Guy :

if you send me the new file

The-PC-Guy :

i should be able to do what you need

Customer:

column W can be inserted really anywhere in the sheet...for I have different progression data in columns L-P in the summary sheet...column W can be inserted before or after

The-PC-Guy :

so V should go in G-K

The-PC-Guy :

W go in L-P

The-PC-Guy :

does that sound good to you

Customer:

let's put W in Q-U

Customer:

otherwise yes

The-PC-Guy :

ok

The-PC-Guy :

no problemmmmmmmmmm

The-PC-Guy :

give me a few minutes

The-PC-Guy :

to do it

Customer:

sounds good

Customer:

just a quick fyi...some additional sheets (i.e. #68) are now present in the workbook but not added to the Total WSL Charts sheet. I've never worked with Macros but hoping they autopopulate if I insert rows for those sheets after it is written?

The-PC-Guy :

it will autopopulate for however many sheets you have a # XXXXX

The-PC-Guy :

it doesn't care if you have 1 sheet

The-PC-Guy :

or 3 million sheets

Customer:

k

The-PC-Guy :

almost done

The-PC-Guy :

done:

Donwload the file from this link

http://ge.tt/1Easw001/v/0?c

Since this is a macro, you by default will get a security warning with a yellow bar

it will say something like "Macros have been disabled".

when you first start excel

you will need to click enable, or it wont work

you will notice a populate button that I added to the right of all the data.

click that to update the data

let me know if you have any questions, problems or concerns.

PLEASE REMEMBER TO RATE MY SERVICE SO I GET PAID FOR MY TIME

YOU DO SO WITH THE SMILEYS DIRECTLY BENEATH THIS CHAT

thanks

The-PC-Guy :

thanks

Customer:

sweet let me try it quick

Customer:

the link isn't working

The-PC-Guy :

u let me double check it

The-PC-Guy :

http://ge.tt/1Easw001/v/0?c

The-PC-Guy :

how is it not working?

The-PC-Guy :

what happens when you click it

Customer:

ok now it worked...the one above chrome said it could not find

The-PC-Guy :

LOL

The-PC-Guy :

gotto love chrome

Customer:

looks great...only thing is I just realized some of the sheets have a 6th row in column V

Customer:

can I just insert another column and drag/fill in the summary sheet?

The-PC-Guy :

well we can certainly modify for that, we just change the columns that the data goes too

The-PC-Guy :

so it would go from G to L then

The-PC-Guy :

instead of G to K

The-PC-Guy :

and R to V

Customer:

do I need to step in to the macro to do so?

The-PC-Guy :

ill fix it in about 30 seconds

The-PC-Guy :

do we need to add an extra value for W as well

Customer:

yes

Customer:

better question would have been....do you need to step into the macro?

Customer:

lol

Customer:

I actually think I see it now in the code but it's probably safer if I stay out of it

The-PC-Guy :

yes

The-PC-Guy :

hands off

The-PC-Guy :

here is the file with the changes

The-PC-Guy :

when you do rate, I would appreciate a bonus, for the extra work

The-PC-Guy :

thanks

Customer:

you're the man....thank you very much

The-PC-Guy :

i know

The-PC-Guy :

ur welecom

The-PC-Guy :

PLEASE RATE WITH THE SMILEYS BELOW THIS CHAT

Customer:

lmao....u saved a life today. haha

The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1302
Experience: 20 years experience providing remote computer support
The-PC-Guy and 4 other Microsoft Office Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional