You're welcome, Louise - thanks for feeding back.

There are many others more expert than me who contribute to these
newsgroups !!

There is not a formula that could be used to help you to rename the
worksheets - you would need a macro to do that.

Pete

On Oct 3, 12:31*pm, Louise wrote:
Hey Pete

THANK YOU SOOOOO MUCH, it works. You have saved me alot of time exept i
should have asked earlier i did one spreadsheet manually . .hehehe *. *silly
me

Seeing that you seem to be quite the spreadsheet expert is it possible to
rename worksheets as i do RFP001, RFP002 all at the same time?

Appreciate your help!

Louise



"Pete_UK" wrote:
Well as long as you have them sequentially numbered you can do it -
amend the formula to:

=INDIRECT("RFP"&TEXT(ROW(A1),"000")&"!C5")

Put this in A10 and then copy down as required - it will bring the
values from C5 in sheets RFP001, RFP002, RFP003 etc. on consecutive
rows in your summary sheet.

Hope this helps.

Pete

On Oct 3, 11:37 am, Louise wrote:
Hi Pete,

That's my problem, my sheets are not named Sheet1, Sheet2 i actually name it
RFP001, RFP002 for my own reference. Is it possible to do so without the
sheets being named Sheet1, Sheet2? Please tell me it is its a long process to
do them individually?

I appreciate you help!

Louise

"Pete_UK" wrote:
Try it this way, then:

=INDIRECT("Sheet"&ROW(A1)&"!C5")

ROW(A1) will return 1, so the text in the bracket will evaluate to
Sheet1!C5 and the INDIRECT function will bring the data from that
calculated cell. When the formula is copied down it will change to
ROW(A2) which will return 2, so the text becomes Sheet2!C5, then in
the next row it will be Sheet3!C5, and so on.

This relies on your sheets being named as Sheet1, Sheet2, Sheet3 etc -
the #REF error implies that they are not.

Hope this helps.

Pete

On Oct 3, 10:36 am, Louise wrote:
Hi Pete,

Thanx so much for that but it does not seem to work. It gives me #REF error.
I checked to see the explanation for the INDIRECT formula but honestly do not
understand what they are saying. I should mention that the cell i want from
Sheet 1 will not be in the same cell on the Summary sheet. I was just using
an example but in actual fact it will be A10=Sheet1!C5, maybe this is why
this formula does not work. Can you help?

Louise

"Pete_UK" wrote:
Try this in A1:

=INDIRECT("Sheet"&ROW(A1)&"!A1")

then you can copy this down as required.

Hope this helps.

Pete

On Oct 3, 9:33 am, Louise wrote:
I have created a sheet and duplicated this sheet on multiple sheets as it is
a standard form that needs to be completed for payment requests. I then
created another sheet in the same spreadsheet where i want to summarize all
these individual sheets, meaning i want to extract exactly the same
information from each sheet into the summary sheet. I have set up the first
line by saying for instance A1=Sheet1A1 now how do i copy this formula down
so that the next formula is A2=Sheet2A1 and so forth. This must be possible
its such a simple thing but i cannot seems to find it on the help menu.
Anybody out there that can help.

Thanx
Louise- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -